【Analytic】分析函数之ROW_NUMBER函数

我们通过这个文章来看一下ROW_NUMBER函数的用法。这是一个很重要的分析函数，在得到分组排序上有着自己的重要位置。

【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

1.万变不离其宗，先看ROW_NUMBER函数的语法描述。

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#SQLRF06100

ROW_NUMBER( )

   OVER ([ query_partition_clause ] order_by_clause)

2.通过实验看一下ROW_NUMBER函数的使用方法

1）创建测试表T，并初始化几条数据。

[email protected]> create table t (group_id number(10), name varchar2(10), salary int);

[email protected]> insert into t values (1,'Tom',1200);

[email protected]> insert into t values (2,'Kary',2400);

[email protected]> insert into t values (2,'Joe',800);

[email protected]> insert into t values (3,'Erick',3600);

[email protected]> insert into t values (3,'Andy',600);

[email protected]> insert into t values (3,'Mary',300);

[email protected]> commit;

2）T表全貌

[email protected]a10g> select * from t;

  GROUP_ID NAME                               SALARY

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

         1 Tom                                  1200

         2 Kary                                 2400

         2 Joe                                   800

         3 Erick                                3600

         3 Andy                                  600

         3 Mary                                  300

6 rows selected.

共三组数据，group_id分别是1、2和3。第1组有一个人，第2组有两个人，第3组有三个人。最后一列是每个人的薪水值。

3）ROW_NUMBER函数应用之一：按照每组人的薪酬的高低进行排序。

[email protected]> select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary) as rn from t;

  GROUP_ID NAME                               SALARY         RN

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

         1 Tom                                  1200          1

         2 Joe                                   800          1

         2 Kary                                 2400          2

         3 Mary                                  300          1

         3 Andy                                  600          2

         3 Erick                                3600          3

6 rows selected.

可见，在数据的最后一列我们添加了RN列，这一列可以得到每一组中人员薪酬从低到高的序号。

4）ROW_NUMBER函数应用之二：通过ROW_NUMBER计算得到的结果便可进一步得到每组薪酬最高的两位员工（注意这里没有考虑到薪酬相同的情况）。

（1）错误用法

[email protected]> select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t where rn<=2;

select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t where rn<=2

                                                                                                                 *

ERROR at line 1:

ORA-00904: "RN": invalid identifier

注意RN是不能直接在where子句中使用的。

（2）正确用法

[email protected]> select * from (select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t ) where rn <= 2;

  GROUP_ID NAME                               SALARY         RN

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

         1 Tom                                  1200          1

         2 Kary                                 2400          1

         2 Joe                                   800          2

         3 Erick                                3600          1

         3 Andy                                  600          2

[email protected]> select group_id,name from (select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t ) where rn <= 2;

  GROUP_ID NAME

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

         1 Tom

         2 Kary

         2 Joe

         3 Erick

         3 Andy

使用嵌套SQL之后，一切恢复到了自然和谐的状态。

3.小结

ROW_NUMBER函数在分组排序统计上有着自己的应用场合。细节之处需要注意，善用之。

有关分析函数的扩展可以参考Oracle的官方文档中的“Analytic Functions”描述：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174

Good luck.

secooler

10.01.11

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-624886/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-624886/