在oracle中，RANK()和DENSE_RANK()函数的区别是什么?

What's the difference between RANK() and DENSE_RANK() functions? How to find out nth salary in the following emptbl table?

RANK()和DENSE_RANK()函数的区别是什么?如何在下面的emptbl表中找出第n个工资?

DEPTNO  EMPNAME    SAL
------------------------------
10       rrr    10000.00
11       nnn    20000.00
11       mmm    5000.00
12       kkk    30000.00
10       fff    40000.00
10       ddd    40000.00
10       bbb    50000.00
10       ccc    50000.00

If in the table data having nulls, what will happen if I want to find out nth salary?

如果在表数据中有nulls，如果我想知道第n个工资会发生什么?

6 个解决方案

#1

RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

RANK给出了有序分区内的排序。领带被分配相同的等级，下一个等级被跳过。所以，如果你有3项在第2级，下一个列出来的是第5级。

DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

DENSE_RANK再次为您提供有序分区中的排名，但是排名是连续的。如果有多个项目的级别，则不跳过级别。

As for nulls, it depends on the ORDER BY clause. Here is a simple test script you can play with to see what happens:

至于nulls，它取决于ORDER BY子句。这里有一个简单的测试脚本，您可以使用它来查看会发生什么:

with q as (
select 10 deptno, 'rrr' empname, 10000.00 sal from dual union all
select 11, 'nnn', 20000.00 from dual union all
select 11, 'mmm', 5000.00 from dual union all
select 12, 'kkk', 30000 from dual union all
select 10, 'fff', 40000 from dual union all
select 10, 'ddd', 40000 from dual union all
select 10, 'bbb', 50000 from dual union all
select 10, 'xxx', null from dual union all
select 10, 'ccc', 50000 from dual)
select empname, deptno, sal
     , rank() over (partition by deptno order by sal nulls first) r
     , dense_rank() over (partition by deptno order by sal nulls first) dr1
     , dense_rank() over (partition by deptno order by sal nulls last) dr2
 from q; 

EMP     DEPTNO        SAL          R        DR1        DR2
--- ---------- ---------- ---------- ---------- ----------
xxx         10                     1          1          4
rrr         10      10000          2          2          1
fff         10      40000          3          3          2
ddd         10      40000          3          3          2
ccc         10      50000          5          4          3
bbb         10      50000          5          4          3
mmm         11       5000          1          1          1
nnn         11      20000          2          2          2
kkk         12      30000          1          1          1

9 rows selected.

Here's a link to a good explanation and some examples.

这里有一个很好的解释和一些例子的链接。

#2

This article here nicely explains it. Essentially, you can look at it as such:

本文对此进行了很好的解释。本质上，你可以这样看:

CREATE TABLE t AS
SELECT 'a' v FROM dual UNION ALL
SELECT 'a'   FROM dual UNION ALL
SELECT 'a'   FROM dual UNION ALL
SELECT 'b'   FROM dual UNION ALL
SELECT 'c'   FROM dual UNION ALL
SELECT 'c'   FROM dual UNION ALL
SELECT 'd'   FROM dual UNION ALL
SELECT 'e'   FROM dual;

SELECT
  v,
  ROW_NUMBER() OVER (ORDER BY v) row_number,
  RANK()       OVER (ORDER BY v) rank,
  DENSE_RANK() OVER (ORDER BY v) dense_rank
FROM t
ORDER BY v;

The above will yield:

以上将收益率:

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+

In words

用文字

• ROW_NUMBER() attributes a unique value to each row
• ROW_NUMBER()为每一行属性一个惟一值
• RANK() attributes the same row number to the same value, leaving "holes"
• RANK()将相同的行号属性为相同的值，留下“空穴”
• DENSE_RANK() attributes the same row number to the same value, leaving no "holes"
• DENSE_RANK()将相同的行号属性为相同的值，不留下“空穴”

#3

SELECT empno,
       deptno,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          4
      7499         30       1600          5
      7698         30       2850          6


SELECT empno,
       deptno,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          3
      7499         30       1600          4
      7698         30       2850          5

#4

rank() : It is used to rank a record within a group of rows.

rank():用于对一组行中的记录进行排序。

dense_rank() : The DENSE_RANK function acts like the RANK function except that it assigns consecutive ranks.

dense_rank(): dense_rank函数的作用类似于RANK函数，只不过它分配了连续的秩。

Query -

查询-

select 
    ENAME,SAL,RANK() over (order by SAL) RANK
from 
    EMP;

Output -

输出-

+--------+------+------+
| ENAME  | SAL  | RANK |
+--------+------+------+
| SMITH  |  800 |    1 |
| JAMES  |  950 |    2 |
| ADAMS  | 1100 |    3 |
| MARTIN | 1250 |    4 |
| WARD   | 1250 |    4 |
| TURNER | 1500 |    6 |
+--------+------+------+

Query -

查询-

select 
    ENAME,SAL,dense_rank() over (order by SAL) DEN_RANK
from 
    EMP;

Output -

输出-

+--------+------+-----------+
| ENAME  | SAL  |  DEN_RANK |
+--------+------+-----------+
| SMITH  |  800 |         1 |
| JAMES  |  950 |         2 |
| ADAMS  | 1100 |         3 |
| MARTIN | 1250 |         4 |
| WARD   | 1250 |         4 |
| TURNER | 1500 |         5 |
+--------+------+-----------+

#5

select empno
       ,salary
       ,row_number() over(order by salary desc) as Serial
       ,Rank() over(order by salary desc) as rank
       ,dense_rank() over(order by salary desc) as denseRank
from emp ;

Row_number() -> Used for generating serial number

Row_number() -用于生成序列号的>

Dense_rank() will give continuous rank but rank will skip rank in case of * of rank.

Dense_rank()会给出连续的秩，但是当秩发生冲突时，秩会跳过秩。

#6

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

RANK()和DENSE_RANK()函数之间的惟一区别是在有“tie”的情况下;即。，当集合中的多个值具有相同的排序时。在这种情况下,排名()将分配连任的“排名”中的值设置(导致差距整数排序值时领带),而DENSE_RANK()将分配连续排名中的值设置(这样不会有整数排名差距值在领带的情况下)。

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

例如，考虑set{25, 25, 50, 75, 75, 100}。对于这样的集合，RANK()将返回{1、1、3、4、4、6}(注意，值2和5被跳过)，而DENSE_RANK()将返回{1、1、2、3、3、4}。