Oracle analytic function-row_number/rank/dense_rank

Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。例如:row_number() over (partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。
row_number与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号。row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开始排序)。

Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

Dense_rank函数返回一个唯一的值,当碰到相同数据时,此时所有相同数据的排名都是一样的,在最后一条相同记录和下一条不同记录的排名之间不空出排名,排名为连续。

First和last函数返回在排序状态下的最大值和最小值,与dense_rank联合使用。

原始数据

SQL> col EMPLOYEE_NAME for a35
SQL>
SQL> select department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary
  5    from employees
  6   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY
------------- ----------------------------------- ------------------- ----------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400
           20 Michael Hartstein                   2004-02-17 00:00:00      13000
           20 Pat Fay                             2005-08-17 00:00:00       6000
           30 Den Raphaely                        2002-12-07 00:00:00      11000
           30 Alexander Khoo                      2003-05-18 00:00:00       2600
           30 Shelli Baida                        2005-12-24 00:00:00       2900
           30 Sigal Tobias                        2005-07-24 00:00:00       2600
           30 Guy Himuro                          2006-11-15 00:00:00       2600
           30 Karen Colmenares                    2007-08-10 00:00:00       2500
           40 Susan Mavris                        2002-06-07 00:00:00       6500
已选择10行。

rank, dense_rank和row_nunmber查询结果对比如下:

SQL> SELECT department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         rank() OVER(PARTITION BY department_id order by salary) AS rank,
  6         dense_rank() OVER(PARTITION BY department_id order by salary) AS dense_rank,
  7         row_number() OVER(PARTITION BY department_id order by salary) AS row_number
  8    FROM employees
  9   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY       RANK DENSE_RANK ROW_NUMBER
------------- ----------------------------------- ------------------- ---------- ---------- ---------- ----------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400          1          1          1
           20 Pat Fay                             2005-08-17 00:00:00       6000          1          1          1
           20 Michael Hartstein                   2004-02-17 00:00:00      13000          2          2          2
           30 Karen Colmenares                    2007-08-10 00:00:00       2500          1          1          1
           30 Alexander Khoo                      2003-05-18 00:00:00       2600          2          2          2
           30 Guy Himuro                          2006-11-15 00:00:00       2600          2          2          3
           30 Sigal Tobias                        2005-07-24 00:00:00       2600          2          2          4
           30 Shelli Baida                        2005-12-24 00:00:00       2900          5          3          5
           30 Den Raphaely                        2002-12-07 00:00:00      11000          6          4          6
           40 Susan Mavris                        2002-06-07 00:00:00       6500          1          1          1
已选择10行。

First、Last、keep与dense_rank一起取最大值和最小值。

SQL> SELECT department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         min(salary) keep (dense_rank first order by salary) over (partition BY department_id) "Lowest",
  6         max(salary) keep (dense_rank last order by salary) over (partition BY department_id) "Highest"
  7    FROM employees
  8   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY     Lowest    Highest
------------- ----------------------------------- ------------------- ---------- ---------- ----------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400       4400       4400
           20 Michael Hartstein                   2004-02-17 00:00:00      13000       6000      13000
           20 Pat Fay                             2005-08-17 00:00:00       6000       6000      13000
           30 Den Raphaely                        2002-12-07 00:00:00      11000       2500      11000
           30 Alexander Khoo                      2003-05-18 00:00:00       2600       2500      11000
           30 Shelli Baida                        2005-12-24 00:00:00       2900       2500      11000
           30 Sigal Tobias                        2005-07-24 00:00:00       2600       2500      11000
           30 Guy Himuro                          2006-11-15 00:00:00       2600       2500      11000
           30 Karen Colmenares                    2007-08-10 00:00:00       2500       2500      11000
           40 Susan Mavris                        2002-06-07 00:00:00       6500       6500       6500
已选择10行。
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s