熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle的數據分析語法

2013-11-13 16:16:08  來源: Oracle 

   Create table

  create table TEST_RANK_OVER

  (

  CLASS NUMBER()

  NAME  VARCHAR()

  SCORE NUMBER() default

  );

  delete from test_rank_over;

  insert into test_rank_over (class namescore)values( a );

  insert into test_rank_over (class namescore)values( b );

  insert into test_rank_over (class namescore)values( c );

  insert into test_rank_over (class namescore)values( d );

  insert into test_rank_over (class namescore)values( a );

  insert into test_rank_over (class namescore)values( b );

  insert into test_rank_over (class namescore)values( c );

  insert into test_rank_over (class namescore)values( d );

  insert into test_rank_over (class namescore)values( a );

  insert into test_rank_over (class namescore)values( b );

  insert into test_rank_over (class namescore)values( c );

  insert into test_rank_over (class namescore)values( d );

  insert into test_rank_over (class namescore)values( e );

  跳躍排名語法

  select class  name score rank() over(partition by class order by score desc) rank

  from test_rank_over;

  查詢結果

  class     name    score     rank

  

          d           

          a              (與下一個排名相同都是第二名)

          b              (與上一個排名相同都是第二名)

          c              (跳過第三名)

          d          

          a           

          c           

          b           

          d          

          e          

          a           

          c           

          b           

  說明rank() over(partition by class order by score desc)先對班級分組然後根據成績進行排名如果分數相同則排名相同接下來的排名會跳躍N個(N為排名相同的人數)

  非跳躍排名語法

  select class  name score dense_rank() over(partition by class order by score desc)rank  from test_rank_over;

  查詢結果

  class     name    score     rank

  

         d            

         a            

         b            

         c            

         d           

         a            

         c            

         b            

         d           

         e           

         a            

         c            

         b            


From:http://tw.wingwit.com/Article/program/Oracle/201311/18060.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.