Oracle 中 RANK() 和 DENSE_RANK() 函数有什么区别？

Question

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

如果表格数据中存在nulls，那么如果我想找出第n高的薪水会发生什么？

- user1357722

Answer 1

RANK()函数可以在有序分区内给出排名。如果有并列项，则这些项将被分配相同的排名，下一个排名将跳过相应数量的项。例如，如果有3个项目的排名为2，则下一个排名将是5。

DENSE_RANK()函数同样可以在有序分区内给出排名，但是排名是连续的。如果有具有多个项目的排名，则不会跳过排名。

对于空值，它取决于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.

这里有一个链接，提供了良好的解释和一些示例。

- DCookie

---

使用SELECT UNION ALL FROM DUAL来生成示例数据是一个非常好的想法，而不需要创建任何表格。 - Jean-Christophe Blanchard

@Wildcard 在PG中可以，但在Oracle中不行。至少在11版本中是这样的。我还没有在生产环境中遇到12版本。 - jpmc26

伊凡，RANK告诉我相对于前面的人我处于什么位置，而DENSE_RANK告诉我绝对排名。我可能有第二高的工资，但前面可能有100个人。哪一个更好取决于我回答的问题。 - DCookie

但是 RANK() 的必要性在哪里？为什么你想跳过一个数字呢？ - Kaushik

正如我之前所说，任何时候你关心的是根据你在集合中的实际位置而不是相对位置来排名你的集合。以我的薪水为例，问题是关于我的实际薪水还是有多少人在我前面？ - DCookie

Answer 2

我在这篇文章中更详细地解释了这个问题。基本上，您可以这样看待：

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;

以上将产生以下结果：

+---+------------+------+------------+
| 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 |
+---+------------+------+------------+

简述

• ROW_NUMBER() 为每行分配唯一值
• RANK() 为相同的值分配相同的行号，留下“空洞”
• DENSE_RANK() 为相同的值分配相同的行号，不留下“空洞”

- Lukas Eder

---

错误：SQL 错误：ORA-00923：未找到期望的 FROM 关键字 - zloctb

Answer 3

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

dense_rank()：DENSE_RANK函数类似于RANK函数，但它分配连续的排名。

查询 -

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

输出 -

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

查询 -

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

输出 -

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

- Sindhu

Answer 4

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

- Anant_00

Answer 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() -> 用于生成序列号。

Dense_rank() 可以连续排名，但在排名冲突的情况下，Rank() 将跳过排名。

- Rajeev Kumar Mehta

Answer 6

RANK()和DENSE_RANK()函数的唯一区别在于存在“并列”的情况；即，在集合中有多个值具有相同的排名时。在这种情况下，RANK()将为集合中的值分配非连续的“排名”（当存在并列时，导致整数排名值之间存在间隙），而DENSE_RANK()将为集合中的值分配连续的排名（因此，在存在并列的情况下，整数排名值之间将没有间隙）。

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

- Salman Lone

Answer 7

Rank() SQL函数生成有序数据集内数据的排名，但是前一个排名之后的下一个排名是该特定行的row_number。另一方面，Dense_Rank() SQL函数生成下一个数字而不是生成row_number。以下是SQL示例，可以澄清这个概念：

Select ROW_NUMBER() over (order by Salary) as RowNum, Salary, 
RANK() over (order by Salary) as Rnk, 
DENSE_RANK() over (order by Salary) as DenseRnk from (
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 2000 as Salary union all
Select 3000 as Salary union all
Select 3000 as Salary union all
Select 8000 as Salary union all
Select 9000 as Salary) A

它将生成以下输出：

----------------------------
RowNum  Salary  Rnk DenseRnk
----------------------------
1       1000    1   1
2       1000    1   1
3       1000    1   1
4       2000    4   2
5       3000    5   3
6       3000    5   3
7       8000    7   4
8       9000    8   5

- Brijesh Kumar Tripathi

Answer 8

Rank(), Dense_rank(), row_number()

这三个函数都是窗口函数，意味着它们首先作为某个有序输入集合上的窗口。这些窗口根据需求具有不同的功能。以下是这三个函数：

row_number()

从 row_number() 开始，因为它构成了这些相关窗口函数的基础。如其名称所示，row_number() 为其应用的行集提供唯一编号，类似于为每一行分配序列号。

Rank()

rank() 可以说是 row_number() 的一个子版本。Rank() 用于为那些重复的有序集行提供相同的序列号，但仍然保持计数与 row_number() 相似，即对于重复项之后的所有内容，rank() 的含义与下面的数据2相同，row_number() = rank()，意思是二者只是在重复形式上有所不同。

Data row_number() rank() dense_rank() 
    1         1                    1       1
    1         2                    1       1
    1         3                    1       1
    2         4                    4       2

最后，Dense_rank()是rank()的扩展版本，因为其名称暗示着它是密集的，正如您可以从上面的示例中看到的那样，对于所有数据1，rank() = dense_rank()，但只是对于数据2，它在形式上有所不同，因为它维护了先前rank()的排序而不是实际数据

- Himanshu

Answer 9

Rank和Dense rank给出了分区数据集中的排名。

Rank()：它不会给你连续的整数。

Dense_rank()：它会给你连续的整数。

在上面的图片中，使用 dense_rank() 函数时，10008 邮政编码的排名为 2，而使用 rank() 函数时，排名为 24，因为它考虑了 row_number。

- Tutu Kumari

Answer 10

RANK()和DENSE_RANK()函数之间唯一的区别在于存在“并列”的情况，即集合中多个值具有相同的排名。在这种情况下，RANK()将为集合中的值分配非连续的“排名”（当存在并列时，整数排名值之间会产生间隙），而DENSE_RANK()将为集合中的值分配连续的排名（因此，在并列情况下，整数排名值之间不会有间隙）。

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

- rjose