【Analytic】分析函数之RANK函数

2020-01-02 11:56:37

我们通过这个文章来看一下ROW_NUMBER函数的用法。这是一个很重要的分析函数，在得到分组排序前N条记录和后N条记录上有着自己的重要位置。
【Analytic】分析函数之MIN函数：http://space.itpub.net/519536/viewspace-624736
【Analytic】分析函数之MAX函数：http://space.itpub.net/519536/viewspace-624749
【Analytic】分析函数之AVG函数：http://space.itpub.net/519536/viewspace-624799
【Analytic】分析函数之ROW_NUMBER函数：http://space.itpub.net/519536/viewspace-624886

1.万变不离其宗，先看RANK函数的语法描述。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions123.htm#i1269223
RANK( )
   OVER ([ query_partition_clause ] order_by_clause)

2.通过实验看一下RANK函数的使用方法
1）创建测试表T，并初始化9条数据。
sec@ora10g> create table t (group_id number(10), name varchar2(10), salary int);
sec@ora10g> insert into t values (1,'Tom',1200);
sec@ora10g> insert into t values (2,'Kary',2400);
sec@ora10g> insert into t values (2,'Joe',800);
sec@ora10g> insert into t values (3,'Erick',3600);
sec@ora10g> insert into t values (3,'Andy',600);
sec@ora10g> insert into t values (3,'Secooler',600);
sec@ora10g> insert into t values (3,'Hou',600);
sec@ora10g> insert into t values (3,'Mary',300);
sec@ora10g> insert into t values (3,'Ellen',200);
sec@ora10g> commit;

2）T表全貌
sec@ora10g> select * from t;

GROUP_ID NAME                               SALARY
---------- ------------------------------ ----------
         1 Tom                                  1200
         2 Kary                                 2400
         2 Joe                                   800
         3 Erick                                3600
         3 Andy                                  600
         3 Secooler                              600
         3 Hou                                   600
         3 Mary                                  300
         3 Ellen                                 200

9 rows selected.

共三组数据，group_id分别是1、2和3。第1组有一个人，第2组有两个人，第3组有六个人。后一列是每个人的薪水值，注意第三组中的Andy、Secooler和Hou的薪水都是相同的。

3）分析函数RANK的基本使用方法
sec@ora10g> select group_id, name, salary, rank() over (PARTITION BY group_id ORDER BY salary) as rank from t;

GROUP_ID NAME                               SALARY       RANK
---------- ------------------------------ ---------- ----------
         1 Tom                                  1200          1
         2 Joe                                   800          1
         2 Kary                                 2400          2
         3 Ellen                                 200          1
         3 Mary                                  300          2
         3 Hou                                   600          3
         3 Secooler                              600          3
         3 Andy                                  600          3
         3 Erick                                3600          6

9 rows selected.

可见，在数据的后一列我们添加了RANK列，这一列可以得到每一分组人员薪酬从低到高的序号。不过细节不容错过，重点请关注同组内薪酬相同的人，如Andy、Secooler和Hou，他们在第三组中的RANK都是3，紧跟其后的Erick的RANK突然变成了6，这是序号跳跃现象（以后我会给大家介绍分析函数DENSE_RANK，它就不会出现这种跳跃现象）。

3）分析函数RANK的一般使用场合
类似于分析函数ROW_NUMBER的作用，可以得到每组内的前N条或后N条记录，便于数据的查询与分析。
与ROW_NUMBER不同的是，分析函数RANK在遇到同组内值相同的记录时，将会使用相同的数字进行标识，以示数值的相等。

3.小结
不同的统计场景将会选用不同的分析函数，一切随需而变。这个世界上永远不变的就是变化。
有关分析函数的扩展可以参考Oracle的官方文档中的“Analytic Functions”描述：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174

Good luck.

secooler
10.01.12

-- The End --