oracle聚合函数rank()的用法和一些体会

先建了张测试表

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,也就是最高分。这样就完成了需求。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值