答案

窗口函数允许在不将行分组为独立行的情况下对行的“窗口”进行计算，这对于报告和分析很方便。

ROW_NUMBER() — 在每个窗口分区内为行分配唯一的顺序编号，按指定的标准对其排序。在ORDER BY中相同值时，可能会出现编号跳过。
RANK() — 为具有相同ORDER BY值的行分配相同的等级，但跳过后续元素的编号（会有间断）。
DENSE_RANK() — 也为具有相同值的行分配相同的等级，但编号是连续的，没有跳过。

示例：

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

表格：

name	salary
瓦西亚	10000
佩佳	10000
玛莎	9000

结果：

name	salary	num	rank	dense_rank
瓦西亚	10000	1	1	1
佩佳	10000	2	1	1
玛莎	9000	3	3	2

潜在问题：

选择不当的ORDER BY可能会导致错误排序。
(row_number) 如果在ORDER BY中未选择唯一字段，缺乏稳定顺序的保证。
在需要分区的情况下不使用PARTITION BY会导致整个行集合的编号不正确。

陷阱问题

如果在窗口函数中不指定PARTITION BY，ROW_NUMBER()如何进行行编号？

答案： 整个数据集将被视为一个分区。即编号将对所有行连续计算，而不考虑任何分组。

示例：

SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
-- 所有员工将获得从1开始的唯一编号，不按部门分组

故事#1

在BI报告中忘记在部门中指定PARTITION BY。公司的所有员工获得了连续的编号，但任务是识别每个部门中的最佳员工。结果导致了部门内不正确的TOP-N员工。

故事#2

开发人员选择RANK()而不是ROW_NUMBER()来确定“最佳”群体，但由于指标相同而分配了相同的编号，导致分析中的领导者出现隐性重复。

故事#3

使用DENSE_RANK()时未考虑它禁止等级中的跳过，这扭曲了销售分析中“唯一”显著职位数量的报告。商业逻辑的控制发现了名次分配的错误。

窗口函数ROW_NUMBER()、RANK()、DENSE_RANK()在SQL报告编程中的工作原理和区别是什么？使用时有哪些潜在问题？

答案

陷阱问题