1.rank()分析函式

語法:
RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])

功能: 先將記錄按PARTITION分組,組內再以ORDER BY排序,算出當前記錄在組內所處的級別(名次). PRATITION條件省略時,表明不分組,或將全錄記錄作為一組

例子: 有表如下:

SQL> select * from test;

NAME       SUBJECT         SCORE
---------- ---------- ----------
aa         chinese            90
aa         art                   88
aa         english            80
bb         chinese            80
bb         art                     92
bb         english            96
cc         chinese            73
cc         art                    78
cc         english            60

已選擇9行。

執行語句如下:

SQL> break on name skip 1;
SQL> select * from ( select name ,subject,score ,rank() over ( partition by name order by score desc)
score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
aa         chinese            90          1
                 art                88          2
           english            80          3

bb         english            96          1
                art                92          2
           chinese            80          3

cc          art                78          1
           chinese            73          2
           english            60          3

已選擇9行。

SQL> select * from (select name ,subject,score ,rank() over ( partition by subject order by score desc) score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
bb         art                92          1
aa                            88          2
cc                            78          3

aa         chinese     90          1
bb                            80          2
cc                            73          3

bb         english     96          1
aa                            80          2
cc                            60          3

已選擇9行。

2.dense_rank函式

DENSE_RANK分析函式

語法:DENSE_RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])

SQL> insert into test values('dd','chinese',80);

已建立 1 行。

SQL> insert into test values('dd','art',78);

已建立 1 行。

SQL> insert into test values('dd','english',96);

已建立 1 行。

SQL> select * from (select name ,subject,score,dense_rank() over ( partition by subject order by score desc) score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
dd         art                96          1
bb                            92          2
aa                            88          3
aa         chinese            90          1
bb                            80          2
cc                            73          3
bb         english            96          1
aa                            80          2
cc                            60          3

已選擇9行。

SQL> select * from (select name ,subject,score ,dense_rank() over ( partition by name order by score desc) score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
aa         chinese            90          1
aa         art                    88          2
aa         english            80          3
bb                                 96          1
bb         art                    92          2
bb         chinese            80          3
cc         art                    78          1
cc         chinese            73          2
cc         english            60          3
dd         art                   96          1
dd                              80          2
dd                               78          3

已選擇12行。

SQL> select * from (select name ,subject,score,rank() over ( partition by name order by score desc)
score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
aa         chinese            90          1
aa         art                   88          2
aa         english            80          3
bb                                 96          1
bb         art                      92          2
bb         chinese            80          3
cc         art                     78          1
cc         chinese            73          2
cc         english            60          3
dd         art                96          1
dd                            80          2
dd                            78          3

已選擇12行。

SQL> select * from (select name ,subject,score,rank() over ( partition by subject order by score desc) score_rank from test) where score_rank<=3;

NAME       SUBJECT         SCORE SCORE_RANK
---------- ---------- ---------- ----------
dd         art                96          1
bb                            92          2
aa                            88          3
aa         chinese      90          1
bb                            80          2
cc                            73          3
bb         english       96          1
aa                            80          2
cc                            60          3

已選擇9行。

rank() 與dense_rank()分析

相關文章