sql - 为什么我们不能使用 rank() 分析函数来删除表中的重复项？

Question

我创建了一个包含以下记录的 emp 表。

    create table emp(
    EMPNO  integer,
    EMPNAME varchar2(20),
    SALARY  number);

select * from emp;

empno empname  salary
10    bill     2000
11    bill     2000
12    mark     3000
12    mark     3000
12    mark     3000
12    philip   3000
12    john     3000
13    tom      4000
14    tom      4000
14    jerry    5000
14    matt     5000
15    susan    5000

为了删除重复项，我一直在使用 rownum() 函数以及 partition by 和 order by 子句，查询如下:

delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
--6 rows deleted

查询删除所有具有重复 empno 的员工记录，结果看起来像这样:

empno empname  salary
10    bill     2000
11    bill     2000
12    mark     3000
13    tom      4000
14    tom      4000
15    susan    5000

当我使用内部查询获取表中所有结果的行号时，它会给我以下结果:

select rowid as rid,empno,empname,
row_number() over(partition by empno order by empno) rn
from emp;

rowid                         rownumber
AACDJUAAPAAGLlTAAA  10  bill    1
AACDJUAAPAAGLlTAAB  11  bill    1
AACDJUAAPAAGLlTAAE  12  mark    1
AACDJUAAPAAGLlTAAD  12  mark    2
AACDJUAAPAAGLlTAAC  12  mark    3
AACDJUAAPAAGLlTAAF  12  philip  4
AACDJUAAPAAGLlTAAG  12  john    5
AACDJUAAPAAGLlTAAH  13  tom     1
AACDJUAAPAAGLlTAAI  14  tom     1
AACDJUAAPAAGLlTAAJ  14  jerry   2
AACDJUAAPAAGLlTAAK  14  matt    3
AACDJUAAPAAGLlTAAL  15  susan   1

但是当我使用 rank() 代替 rownumber() 函数时，它会给出以下结果:

select rowid as rid,empno,empname,
rank() over(partition by empno order by empno) rn
from emp;

rowid                          rank
AACDJUAAPAAGLlTAAA  10  bill    1
AACDJUAAPAAGLlTAAB  11  bill    1
AACDJUAAPAAGLlTAAE  12  mark    1
AACDJUAAPAAGLlTAAD  12  mark    1
AACDJUAAPAAGLlTAAC  12  mark    1
AACDJUAAPAAGLlTAAF  12  philip  1
AACDJUAAPAAGLlTAAG  12  john    1
AACDJUAAPAAGLlTAAH  13  tom     1
AACDJUAAPAAGLlTAAI  14  tom     1
AACDJUAAPAAGLlTAAJ  14  jerry   1
AACDJUAAPAAGLlTAAK  14  matt    1
AACDJUAAPAAGLlTAAL  15  susan   1

所以我的问题是为什么即使有重复的 empid，rank() 也会为表中的所有记录赋予相同的值？

Answer 1

这就是 RANK() 的工作方式。为分区内排名相同的行获得不同的 RANK 值会很令人惊讶。事实上，ORDER BY 子句是分区内 RANK 的重要驱动因素，但由于您对分区使用与排序相同的列，因此它是明确每一行在各自分区中排名第一(因为它们是分区中的唯一值)

See an explanation in this blog post ，其中此 SQL(PostgreSQL 语法)

SELECT
  v,
  ROW_NUMBER() OVER (window) row_number,
  RANK()       OVER (window) rank,
  DENSE_RANK() OVER (window) dense_rank
FROM t
WINDOW window AS (ORDER BY v)
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 |
+---+------------+------+------------+