Oracle聚合函数RANK和dense_rank的使用

聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
　　在9i版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于order_by_clause子句中的value_exprs指定字段的。
　　其语法为：
　　RANK ( ) OVER ( [query_partition_clause] order_by_clause )
　　在9i版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式，且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
　　其语法为：
　　RANK ( expr [, expr]... ) WITHIN GROUP
　　( ORDER BY
　　expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
　　[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
　　)

实例解析如下：

先建了张测试表

SQL> select * from test_a;

 

ID                   PLAYNAME                  SCORE

-------------------- -------------------- ----------

01                   aa                          100

02                   aa                          101

02                   bb                           99

03                   bb                           98

04                   aa                          101

02                   aa                          101

 

需求是，将score降序排序，打印所有字段，并且如果是同一个playname的score只取出最高分，如果这个playname获得过多个相同的最高分，则只取出其中一个（比如：aa获得过3次101，则只取其中一个），最终要的结果就是：

 

        RK ID                   PALYNAME                  SCORE

---------- -------------------- -------------------- ----------

         1 02                   aa                          101

         1 02                   bb                           99

 

本来我想用max函数，结果直接就出来了：

 

SQL> select max(score),palyname from test_a group by palyname;

 

MAX(SCORE) PALYNAME

---------- --------------------

       101 aa

        99 bb

 

但是要打印所有字段…OTL

 

即使用了嵌套，还是无法解决重复重现最高分的现象：

 

SQL> select distinct * from test_a t where  score  in  (select  max(score)  from  test_a  group  by  palyname) order by score desc;

 

ID                   PALYNAME                  SCORE

-------------------- -------------------- ----------

02                   aa                          101

04                   aa                          101

02                   bb                           99

 

 

由于相同的playname对应的id不同，所以用distinct也无法过滤掉相同playname的并列最高分。

 

 

于是只好用rank（）了

 

Rank的基本语法为：

 

RANK ( ) OVER ( [query_partition_clause] order_by_clause )

 

例子1：

　　

　　TABLE：A （科目，分数）

　　

　　数学，80

　　语文，70

　　数学，90

　　数学，60

　　数学，100

　　语文，88

　　语文，65

　　语文，77

　　

　　现在我想要的结果是：（即想要每门科目的前3名的分数）

　　

　　数学，100

　　数学，90

　　数学，80

　　语文，88

　　语文，77

　　语文，70

　　

　　那么语句就这么写：

　　

　　select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t

where t.rk<=3;

 

以科目来分组，然后以分数来排序，给排序的结果分配rank，取前三名的rank

 

 

例子2：

　　

　　有表Table内容如下

　　

　　COL1　COL2

　　　 1　1

　　　 2　1

　　　 3　2

　　　 3　1

　　　 4　1

　　　 4　2

　　　 5　2

　　　 5　2

　　　 6　2

　　

　　分析功能：列出Col2分组后根据Col1排序,并生成数字列。比较实用于在成绩表中查出各科前几名的信息。

　　

　　SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;

　　

　　结果如下：

　　

　　COL1　COL2　Rank

　　　 1　1　　    1

　　　 2　1　　    2

　　　 3　1　　    3

　　　 4　1　　    4

　　　 3　2　　    1

　　　 4　2　　    2

　　　 5　2　　    3

　　　 5　2　　    3

　　　 6　2　　    5

 

这个例子更直观一点，根据col2分组，根据clo1排序，我们可以发现：

 

5　2　　    3

5　2　　    3

6　2　　    5

 

即，如果两行记录完全相同，他们会被给予相同的rank，而排在它们之后的那行记录，由于前面的并列第3，使得之后的那条记录变成了第5，而如果我们在这里用的是dense_rank，那么之后的那条会变成第4

 

例子3：

　　

　　合计功能：计算出数值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置

　　

　　SELECT RANK(4,1) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;

　　

　　结果如下：

　　Rank

　　4

 

通过以上方法，得出col1为4，col2为1的那行数据的rank排名为多少

 

Dense_rank的例子：

 

dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过

　　

　　例如：表

　　

　　A　　　　　　B　　　　　　C

　　a　　　　　liu　　　　　wang

　　a　　　　　jin　　　　　shu

　　a　　　　　cai　　　　　kai

　　b　　　　　yang　　　　 du

　　b　　　　　lin　　　　　ying

　　b　　　　　yao　　　　　cai

　　b　　　　　yang　　　　 99

　　

　　例如：当rank时为：

　　

　　select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m

　　

　　 A　　　　　B　　　　　　 C　　　　　LIU

　　 a　　　　　cai　　　　　 kai　　　　　1

　　 a　　　　　jin　　　　　 shu　　　　　2

　　 a　　　　　liu　　　　　 wang　　　　 3

　　 b　　　　　lin　　　　　 ying　　　　 1

　　 b　　　　　yang　　　　　du　　　　　 2

　　 b　　　　　yang　　　　　99　　　　　 2

　　 b　　　　　yao　　　　　 cai　　　　　4

　　

　　而如果用dense_rank时为：

　　

　　select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m

　　

　　 A　　　　　B　　　　　　 C　　　　　LIU

　　 a　　　　　cai　　　　　kai　　　　　   1

　　 a　　　　　jin　　　　　shu　　　　　   2

　　 a　　　　　liu　　　　　wang　　　　       3

　　 b　　　　　lin　　　　　ying　　　　         1

　　 b　　　　　yang　　　　 du　　　　　      2

　　 b　　　　　yang　　　　 99　　　　　      2

　　 b　　　　　yao　　　　　cai　　　　　   3

 

 

那么再回到之前的那个需求，

 

SQL> select distinct * from (select rank() over(partition by playname order by score desc,id) rk,t.* from test_a t) where rk=1;

 

        RK ID                   PLAYNAME                  SCORE

---------- -------------------- -------------------- ----------

         1 02                   aa                          101

         1 02                   bb                           99

 

这里order by score desc,id  以score降序和id这两个字段排序，也就是说，正因为相同的playname对应的id不同，这样相同的playname，相同的score，但是不同的id，这样的2行数据就获得了不同的rank，而rk=1，即是只取rank=1，也就是最高分。这样就完成了需求。