如何用窗口函数找出每个分组中第 N 名的记录？(ROW_NUMBER 子查询模式)

考察要点

这道题面试官想看你是否懂 窗口函数（Window Functions）的核心用法、SQL 的逻辑执行顺序 以及 如何通过子查询或 CTE（Common Table Expression）来突破 SQL 的执行限制。这不仅是考察 ROW_NUMBER() 的语法，更是检验你是否具备将复杂业务需求拆解为结构化 SQL 查询的能力。

核心概念

要解决这个问题，首先需要理解以下几个核心概念：

1. 窗口函数 (Window Function): 与聚合函数（如 SUM, COUNT）不同，聚合函数会将多行聚合成一行，而窗口函数会为结果集的每一行计算一个值。它在与当前行相关的“窗口”（一组行）上执行计算。其基本语法是 FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ...)。

2. ROW_NUMBER(): 一个窗口函数，它为窗口分区中的每一行分配一个从 1 开始的唯一连续整数。

   • PARTITION BY department: 这部分是可选的，它将数据行分成不同的“分区”或“组”（例如，按部门分组）。窗口函数会独立地应用于每个分区。
   • ORDER BY salary DESC: 这部分定义了在每个分区内如何对行进行排序，以便分配行号。对于排名问题，这是必需的。

3. SQL 逻辑查询处理顺序: 这是理解为什么需要子查询或 CTE 的关键。一个简化的处理顺序如下：

   1. FROM / JOIN
   2. WHERE
   3. GROUP BY
   4. HAVING
   5. SELECT (包括窗口函数的计算)
   6. DISTINCT
   7. ORDER BY
   8. LIMIT / OFFSET

   因为窗口函数在 SELECT 阶段（第 5 步）才被计算，而 WHERE 子句在第 2 步就执行了，所以在 WHERE 子句中无法直接引用 SELECT 列表中由窗口函数生成的别名。

4. 子查询 / CTE 模式: 为了过滤窗口函数的结果，我们必须将带有窗口函数的查询作为派生表（子查询）或 CTE，然后在外部查询的 WHERE 子句中对其结果进行过滤。

数据示例 假设我们有一张 employees 表：

目标: 找出每个部门薪资第二高的员工。

中间结果 (应用窗口函数后): SELECT *, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as rn FROM employees;

最终结果 (外部查询 WHERE rn = 2):

多种解法

解法 1：使用 CTE (Common Table Expression)

这是最推荐、可读性最好的现代 SQL 解法。

• 适用版本: ✅ MySQL 8.0+, PostgreSQL, SQL Server, Oracle 等现代数据库。
• SQL 代码:
  sql

  ⧉⛶

   1WITH RankedEmployees AS (
   2    SELECT
   3        id,
   4        name,
   5        department,
   6        salary,
   7        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
   8    FROM
   9        employees
  10)
  11SELECT
  12    id,
  13    name,
  14    department,
  15    salary
  16FROM
  17    RankedEmployees
  18WHERE
  19    rn = 2;

• 时间/空间复杂度: 时间复杂度主要由排序决定，为 $O(N \log N)$，其中 N 是总行数。如果存在合适的索引，可以优化到接近 $O(N)$。空间复杂度为 $O(N)$，用于存储 CTE 的中间结果。
• 索引依赖: 在 (department, salary DESC) 上建立复合索引可以极大地提升性能。数据库可以利用该索引来避免对每个分区进行显式排序，而是直接按顺序读取索引。

解法 2：使用子查询（派生表）

功能上与 CTE 完全等价，只是语法结构不同，对于简单查询来说也很常用。

• 适用版本: ✅ MySQL 8.0+, PostgreSQL, SQL Server, Oracle 等。
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    id,
   3    name,
   4    department,
   5    salary
   6FROM (
   7    SELECT
   8        id,
   9        name,
  10        department,
  11        salary,
  12        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  13    FROM
  14        employees
  15) AS RankedEmployees
  16WHERE
  17    rn = 2;

• 时间/空间复杂度: 与 CTE 解法相同，均为 $O(N \log N)$ 时间和 $O(N)$ 空间。
• 索引依赖: 与 CTE 解法相同， (department, salary DESC) 上的复合索引是性能关键。

解法 3：使用相关子查询（兼容老版本）

在不支持窗口函数的旧版本数据库（如 MySQL 5.7）中，可以使用此方法，但性能通常较差。

• 适用版本: ✅ 几乎所有 SQL 数据库，包括 MySQL 5.7。
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    e1.id,
   3    e1.name,
   4    e1.department,
   5    e1.salary
   6FROM
   7    employees e1
   8WHERE (
   9    SELECT COUNT(*)
  10    FROM employees e2
  11    WHERE e2.department = e1.department AND e2.salary > e1.salary
  12) = 1; -- 找出比当前员工薪水高的正好有1个人，即排名第2

  注意: 这种写法在处理薪资相等（并列）的情况时，行为与 RANK() 类似，而不是 ROW_NUMBER()。例如，如果有两个并列第一，它将不会返回任何排名第二的记录。
• 时间/空间复杂度: 这是一个相关子查询，对于外层查询的每一行，都会执行一次内层查询。其时间复杂度近似为 $O(N \cdot K)$，其中 K 是平均分区大小，最坏情况下是 $O(N^2)$。性能远低于窗口函数。
• 索引依赖: (department, salary) 上的索引对于内层查询至关重要，否则会导致全表扫描，性能急剧下降。

常见低分回答

1. 直接在 WHERE 子句中过滤窗口函数：这是最典型的错误，暴露了对 SQL 逻辑执行顺序的不理解。

   sql

   ⧉⛶

    1-- 错误！SQL会报错，因为'rn'在WHERE阶段还不存在
    2SELECT
    3    name,
    4    department,
    5    salary,
    6    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    7FROM
    8    employees
    9WHERE
   10    rn = 2;

2. 混淆 ROW_NUMBER(), RANK(), DENSE_RANK()：当面试官问“第 N 名”时，如果存在并列情况，这三个函数结果完全不同。不主动澄清或解释差异，会被认为考虑不周。

   • ROW_NUMBER(): 1, 2, 3, 4 (即使值相同，排名也唯一)
   • RANK(): 1, 2, 2, 4 (并列后跳过后续排名)
   • DENSE_RANK(): 1, 2, 2, 3 (并列后不跳过后续排名) 对于“薪资第二高”的需求，DENSE_RANK() 通常是更符合业务直觉的选择。

3. 忘记 PARTITION BY：如果题目要求“每个分组”的第 N 名，却漏写了 PARTITION BY，查询会变成在整个表里找第 N 名，完全曲解了题意。

   sql

   ⧉⛶

    1-- 错误！这会在全公司范围找薪资第二高的，而不是每个部门
    2ROW_NUMBER() OVER (ORDER BY salary DESC)

4. 在 GROUP BY 中使用窗口函数：错误地认为 PARTITION BY 可以被 GROUP BY 替代，将聚合查询和窗口查询混为一谈。

工程实践与优化

• 索引是性能的关键: 对于 OVER (PARTITION BY A ORDER BY B)， (A, B) 上的复合索引是性能优化的首选。EXPLAIN 结果中，如果没有这个索引，你很可能会看到 Using temporary; Using filesort，这是性能瓶颈的信号。
• 数据倾斜问题: 在生产环境中，某个分区的数据可能远大于其他分区（例如，某个“核心部门”的员工数占了公司总人数的 80%）。这会导致为该分区排序时消耗大量内存和时间。优化策略可能包括：对超大分区进行特殊处理，或与业务方确认是否可以调整统计口径。
• 使用 EXPLAIN ANALYZE (MySQL 8.0.18+): 这个命令不仅展示执行计划，还会实际执行并显示各阶段的耗时和行数。对于窗口函数，你可以清晰地看到窗口聚合（Window Aggregation）节点的实际开销，从而精准定位性能问题。
• 选择正确的排名函数: 明确业务需求。是要唯一的行号 (ROW_NUMBER)，还是要处理并列的排名 (RANK/DENSE_RANK)？例如，在发奖金的场景，并列第二的员工应该都拿到奖金，此时用 RANK 或 DENSE_RANK 更合适。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到上亿级别，这条 SQL 性能怎样？怎么优化？

   • 回答要点 1 (瓶颈分析): 性能瓶颈主要在于排序。上亿数据在内存中排序是不现实的，会导致大量的磁盘 I/O（外部排序）。即使有 (department, salary DESC) 索引，一次性读取和处理如此多的数据也会给数据库带来巨大压力。
   • 回答要点 2 (优化方案):
     • 确保索引最优: 必须有 (department, salary DESC) 复合索引，最好是覆盖索引，避免回表。
     • 分批处理: 如果可以接受，不要一次性计算所有部门。可以按部门 ID 分批次执行该查询。例如，用脚本 FOR d IN (SELECT DISTINCT department FROM employees) DO ...。
     • 离线计算: 对于超大规模数据，这类排名统计通常会放在数据仓库（如 Hive, Spark SQL）中进行离线 T+1 计算，将结果存入结果表，线上业务直接查询结果表，避免实时计算。

2. 追问：你刚才提到了 RANK 和 DENSE_RANK，它们和 ROW_NUMBER 的具体区别是什么？请举例说明。

   • 回答要点: 准备一个清晰的表格来解释。 假设数据为 (salary: 120, 100, 100, 90):

     salary	ROW_NUMBER()	RANK()	DENSE_RANK()
     120	1	1	1
     100	2	2	2
     100	3	2	2
     90	4	4	3

     • ROW_NUMBER: 无论值是否相同，都分配唯一连续的排名。适用于需要唯一行号的场景，如分页。
     • RANK: 值相同时排名相同，但后续排名会跳跃。适用于“奥林匹克奖牌”式排名。
     • DENSE_RANK: 值相同时排名相同，后续排名连续不跳跃。适用于“排行榜”场景，如找出“第二高薪水值”对应的所有员工。

3. 追问：如何在不支持窗口函数的 MySQL 5.7 中实现 DENSE_RANK 的效果？

   • 回答要点: 可以使用相关子查询，但需要对薪资去重计数。
     sql

     ⧉⛶

      1SELECT
      2    e1.id,
      3    e1.name,
      4    e1.department,
      5    e1.salary
      6FROM
      7    employees e1
      8WHERE (
      9    SELECT COUNT(DISTINCT e2.salary)
     10    FROM employees e2
     11    WHERE e2.department = e1.department AND e2.salary >= e1.salary
     12) = 2; -- 找出薪水值大于等于当前员工薪水值的不同薪水值的数量为2

   • 补充方案 (用户变量): 也可以提及 MySQL 特有的用户变量法，但要强调其非标准、在 8.0 中已不推荐，且在某些复杂查询中执行顺序无法保证。这能展示你的知识广度。