【Analytic】分析函数之DENSE_RANK函数

我们通过这个文章来看一下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
【Analytic】分析函数之RANK函数：http://space.itpub.net/519536/viewspace-624985

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

2.通过实验看一下DENSE_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）分析函数DENSE_RANK的基本使用方法
sec@ora10g> select group_id, name, salary, dense_rank() over (PARTITION BY group_id ORDER BY salary) as dense_rank from t;

  GROUP_ID NAME                               SALARY DENSE_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          4

9 rows selected.

DENSE_RANK函数与RANK函数相同点是，当同组的薪水值相同时DENSE_RANK的值相同；不同点是，DENSE_RANK不会出现RANK函数的跳跃现象。

4）我们同时使用row_number、rank和dense_rank函数，比较一下他们的区别。
sec@ora10g> col rn for 99
sec@ora10g> col rank for 99
sec@ora10g> col dense_rank for 99
sec@ora10g> select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary) as rn, rank() over (PARTITION BY group_id ORDER BY salary) as rank, dense_rank() over (PARTITION BY group_id ORDER BY salary) as dense_rank from t;

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

9 rows selected.

上面的结果清晰的表明了ROW_NUMBER、RANK和DENSE_RANK的区别。在获取前N条记录和后N条记录的需求里各有使用场合。

5）使用ROW_NUMBER取每组的前四
sec@ora10g> select *
  2    from (select GROUP_ID,
  3                 name,
  4                 salary,
  5                 ROW_NUMBER () over (partition by GROUP_ID order by salary)
  6                    as rn,
  7                 RANK () over (partition by GROUP_ID order by salary) as RANK,
  8                 DENSE_RANK () over (partition by GROUP_ID order by salary)
  9                    as DENSE_RANK
 10            from t)
 11   where rn <= 4;


  GROUP_ID NAME                               SALARY  RN RANK DENSE_RANK
---------- ------------------------------ ---------- --- ---- ----------
         1 Tom                                  1200   1    1          1
         2 Joe                                   800   1    1          1
         2 Kary                                 2400   2    2          2
         3 Ellen                                 200   1    1          1
         3 Mary                                  300   2    2          2
         3 Andy                                  600   3    3          3
         3 Hou                                   600   4    3          3

7 rows selected.

6）使用RANK取每组的前四
sec@ora10g> select *
  2    from (select GROUP_ID,
  3                 name,
  4                 salary,
  5                 ROW_NUMBER () over (partition by GROUP_ID order by salary)
  6                    as rn,
  7                 RANK () over (partition by GROUP_ID order by salary) as RANK,
  8                 DENSE_RANK () over (partition by GROUP_ID order by salary)
  9                    as DENSE_RANK
 10            from t)
 11   where RANK <= 4;

  GROUP_ID NAME                               SALARY  RN RANK DENSE_RANK
---------- ------------------------------ ---------- --- ---- ----------
         1 Tom                                  1200   1    1          1
         2 Joe                                   800   1    1          1
         2 Kary                                 2400   2    2          2
         3 Ellen                                 200   1    1          1
         3 Mary                                  300   2    2          2
         3 Andy                                  600   3    3          3
         3 Hou                                   600   4    3          3
         3 Secooler                              600   5    3          3

8 rows selected.

7）使用DENSE_RANK取每组的前四
sec@ora10g> select *
  2    from (select GROUP_ID,
  3                 name,
  4                 salary,
  5                 ROW_NUMBER () over (partition by GROUP_ID order by salary)
  6                    as rn,
  7                 RANK () over (partition by GROUP_ID order by salary) as RANK,
  8                 DENSE_RANK () over (partition by GROUP_ID order by salary)
  9                    as DENSE_RANK
 10            from t)
 11   where DENSE_RANK <= 4;

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

9 rows selected.

3.小结
ROW_NUMBER、RANK和DENSE_RANK都是很贴心的分析函数，也是用得比较普遍的，领会后必将裨益无限。
有关分析函数的扩展可以参考Oracle的官方文档中的“Analytic Functions”描述：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174

Good luck.

secooler
10.01.13

-- The End --

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

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