ROW_NUMBER() / RANK() / DENSE_RANK() 三者有什么区别？分别在什么场景使用？

考察要点

这道题面试官想看你是否扎实掌握了 SQL 窗口函数（Window Functions）的核心用法。具体来说，考察的是：

1. 精确区分能力：能否清晰描述 ROW_NUMBER(), RANK(), DENSE_RANK() 在处理排序“并列”（ties）时的不同行为。
2. 场景应用能力：能否将理论知识与业务场景结合，说明在何种需求下应该选择哪个函数。
3. SQL 功底：是否理解窗口函数的语法结构，特别是 OVER (PARTITION BY ... ORDER BY ...) 子句的作用。

核心概念

窗口函数是一种特殊的函数，它对与当前行相关的“窗口”（一组查询行）进行计算，而不需要像 GROUP BY 那样将多行折叠为一行。这三个函数都是排序型窗口函数，其核心区别在于处理 ORDER BY 子句中列值相同的数据行的方式。

假设我们有以下学生成绩表示例 scores：

我们想对每个学科内的学生按成绩从高到低排名。

 1SELECT
 2    student,
 3    subject,
 4    score,
 5    ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS row_num,
 6    RANK()       OVER (PARTITION BY subject ORDER BY score DESC) AS rnk,
 7    DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_rnk
 8FROM scores
 9ORDER BY subject, score DESC;

执行结果如下：

三者区别详解：

1. ROW_NUMBER()

   • 定义：为窗口分区中的每一行分配一个从 1 开始的唯一连续整数。
   • 特点：无视并列，排名无重复。即使 ORDER BY 的列值相同（如 Bob 和 Carol），它也会为他们分配不同的行号（2 和 3）。分配顺序在并列情况下是不确定的，除非 ORDER BY 子句能唯一确定一行。
   • 适用场景：
     • 取 Top N：需要严格取 N 条记录，即使存在并列情况。例如，“随机抽取每个部门工资最高的 1 名员工作为代表”。
     • 分页查询：生成唯一的行号用于高效分页。
     • 数据去重：PARTITION BY 重复的键，ORDER BY任意列，然后只取 row_num = 1 的记录。

2. RANK()

   • 定义：计算行在其窗口分区中的排名。
   • 特点：并列同名次，排名会跳跃。如果两行并列第 2（如 Bob 和 Carol），它们都获得排名 2，但下一行的排名将是 4（跳过了 3）。可以理解为“排在前面的总人数 + 1”。
   • 适用场景：
     • 竞赛排名：如体育比赛，两人并列亚军，则不设季军。
     • 查找前 N 名：需要找出所有并列在 N 名及以内的记录。例如，“找出每个部门工资排名前 3 的所有员工”。

3. DENSE_RANK()

   • 定义：计算行在其窗口分区中的排名，类似于 RANK()。
   • 特点：并列同名次，排名是连续的。如果两行并列第 2（如 Bob 和 Carol），它们都获得排名 2，下一行的排名将是 3（不跳跃）。可以理解为“去重排序后的名次”。
   • 适用场景：
     • 分组排名：需要获得连续的排名信息。例如，“按销量对商品进行排名，形成 1、2、3 级商品梯队”。
     • 查找前 N 个等级：例如，“找出每个部门工资排名前 3 个薪资等级的所有员工”。

版本兼容性： 窗口函数是 SQL:2003 标准的一部分。主流数据库如 PostgreSQL, Oracle, SQL Server 很早就支持。MySQL 在 8.0 版本才开始支持，在 MySQL 5.7 及之前版本无法使用。

常见低分回答

1. 混淆 RANK() 和 DENSE_RANK()：最常见的错误，无法清晰说出 RANK() 会跳跃排名而 DENSE_RANK() 不会。
2. 在 WHERE 子句中直接使用窗口函数：这是一个经典错误，因为窗口函数在逻辑上是在 WHERE 子句之后执行的。
   • 错误示例：SELECT ... FROM t WHERE RANK() OVER (...) <= 3;
   • 正确做法：必须使用子查询或 CTE (Common Table Expression)。
     sql

     ⧉⛶

      1-- 使用 CTE
      2WITH RankedEmployees AS (
      3    SELECT
      4        emp_name,
      5        department,
      6        salary,
      7        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
      8    FROM employees
      9)
     10SELECT * FROM RankedEmployees WHERE rnk <= 3;

3. 忘记 OVER() 子句中的 ORDER BY：对于排序型窗口函数，ORDER BY 是必须的，否则排名无从谈起，数据库会报错或产生无意义的结果。
4. 不理解 PARTITION BY 的作用：当被问到“如何计算每个部门内员工的排名”时，只写 OVER (ORDER BY salary DESC)，导致对全表进行排名，而不是在部门内部分别排名。
5. 对 ROW_NUMBER() 在并列情况下的不确定性没有概念：认为 ROW_NUMBER() 在并列时会按某种“默认”规则（如主键）排序。实际上，除非 ORDER BY 子句能唯一确定顺序，否则数据库可以自由决定并列行的顺序，结果可能在不同次查询中变化。

工程实践与优化

• 性能与索引：窗口函数的性能开销主要在排序上。OVER 子句中的 PARTITION BY 和 ORDER BY 列是优化的关键。为 (partition_col, order_col) 创建复合索引，可以极大地提升性能，因为数据库可以直接利用索引的有序性，避免昂贵的内存排序或文件排序（filesort）。
• 执行计划分析：使用 EXPLAIN (或 EXPLAIN ANALYZE) 查看执行计划。关注 Extra 列是否出现 Using temporary; Using filesort。理想情况下，如果索引设计得当，可以看到 WindowAgg 节点直接从索引扫描中获取有序数据，而没有额外的 Sort 节点。
• 大数据量下的内存问题：如果 PARTITION BY 产生的单个分区非常大（例如，一个部门有数百万员工），排序和计算可能会消耗大量内存，甚至溢出到磁盘，导致查询性能急剧下降。此时需要考虑：
  1. 业务上是否能缩小分区范围。
  2. 增加 work_mem (PostgreSQL) 或 sort_buffer_size (MySQL) 等内存配置参数。
  3. 如果只是取 Top N，考虑是否可以用其他更高效的方式近似实现。
• 避免在 OLTP 核心业务中使用：复杂的窗口函数查询通常是分析型（OLAP）负载。在面向高并发、低延迟的在线交易处理（OLTP）场景中，应谨慎使用，因为它可能持有锁或消耗过多 CPU/内存，影响整体系统性能。

常见追问与延伸题目

1. 追问：如果我用的 MySQL 5.7，不支持窗口函数，怎么实现 DENSE_RANK() 的功能？

   • 回答要点：可以使用自连接或相关子查询来模拟。以相关子查询为例，其思路是：对于每一行记录，去计算在同一分区内，有多少个不重复的值比当前行的值更大（或等于）。
   • 示例SQL：
     sql

     ⧉⛶

      1SELECT
      2    s1.student,
      3    s1.subject,
      4    s1.score,
      5    (
      6        SELECT COUNT(DISTINCT s2.score)
      7        FROM scores s2
      8        WHERE s2.subject = s1.subject AND s2.score >= s1.score
      9    ) AS dense_rnk
     10FROM scores s1
     11ORDER BY s1.subject, s1.score DESC;

   • 性能对比：需要强调，这种模拟方式的性能通常远低于原生窗口函数。相关子查询对每一行都可能执行一次内部查询，复杂度接近 $O(N^2)$，而原生窗口函数基于排序，复杂度通常为 $O(N \log N)$。在数据量大时，性能差异巨大。

2. 追问：除了这三个，你还知道哪些常用的窗口函数？比如我想把用户按消费额分成 4 个等级，该用什么？

   • 回答要点：这引出了另一类窗口函数——分桶函数（Bucketing/Tiling Functions）。
   • NTILE(n)：这是标准答案。NTILE(n) 函数将分区中的有序行分成 n 个尽可能大小相等的桶（bucket），并为每行分配一个从 1 到 n 的桶编号。
   • 示例SQL：
     sql

     ⧉⛶

      1SELECT
      2    user_id,
      3    total_spend,
      4    NTILE(4) OVER (ORDER BY total_spend DESC) AS spending_quartile
      5FROM user_spends;

   • 其他函数：可以顺便提及聚合窗口函数（如 SUM(...) OVER (...), AVG(...) OVER (...)）和值窗口函数（如 LAG(), LEAD(), FIRST_VALUE()），展示知识广度。

3. 追问：SUM(col) OVER (PARTITION BY ... ORDER BY ...) 这个语句里的 ORDER BY 有什么作用？它和不加 ORDER BY 有什么区别？

   • 回答要点：这是一个非常好的问题，考察对窗口“框架”（Frame）的理解。
   • 不加 ORDER BY：SUM(col) OVER (PARTITION BY dept)。此时，窗口框架是整个分区。计算的是该行所在分区的总和，分区内每一行的结果都相同。
   • 加上 ORDER BY：SUM(col) OVER (PARTITION BY dept ORDER BY hire_date)。此时，默认的窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。计算的是从分区开始到当前行的累计总和（Running Total）。
   • 示例：对于一个部门的员工按入职日期排序，这个 SQL 可以计算出截至每个员工入职时，部门的累计薪资总额。这是做累积分析（如 YTD, MTD）的利器。