SQL 窗口函数：ROW_NUMBER vs RANK vs DENSE_RANK

一句话结论（30s）

三个排名函数的差别只在如何处理并列：ROW_NUMBER 并列也硬编号（1,2,3）、RANK 并列同号且跳号（1,1,3）、DENSE_RANK 并列同号不跳号（1,1,2）——因为排名语义不同，取 TopN 用 ROW_NUMBER 最稳，因为每个名次唯一、不会因并列漏掉人。

核心原理（2min）

• 窗口函数 = 分区 + 排序 + 每行算一个聚合值：OVER (PARTITION BY 分组列 ORDER BY 排序列) 决定窗口范围，函数在每行的窗口内计算。
• 三大排名函数：ROW_NUMBER 唯一行号；RANK 同分同号、跳过后续名次；DENSE_RANK 同分同号、连续不跳号。
• TopN Per Group：子查询里用 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 编号，外层 WHERE rk <= N 过滤——经典分组取前 N 模式。
• 与 GROUP BY 的区别：GROUP BY 压缩行数（只剩分组聚合结果），窗口函数不压缩行数，每行保留自身且能看到”全局/窗口”聚合值。

底层深入（5-10min）

一个例子看清区别

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM students;

• ROW_NUMBER：严格编号，相同分数也分 1/2 → 唯一行号
• RANK：相同分数同排名，跳号（1→1→3→4，缺 2）
• DENSE_RANK：相同分数同排名，不跳号（1→1→2→3）

💭 思考：为什么 ROW_NUMBER 遇到并列分数时，谁拿 1、谁拿 2 其实是「不确定」的？——因为 ROW_NUMBER 的职责是「硬编一个唯一序号」，它不承诺「并列时按什么规则排序」。ORDER BY score DESC 只规定了「按分数降序」，两个 100 分谁在前没有标准，取决于执行计划，今天张三 1 李四 2、明天可能反过来。所以需要「结果可复现」时，要给 ORDER BY 加一个 tie-breaker（比如 ORDER BY score DESC, id ASC），把排序键变唯一。这也是一层取舍：想要稳定的名次，就得把排序规则写死。

💡 思考穿插：为什么 RANK 并列后会跳号，DENSE_RANK 不跳号？ 因为 RANK 的语义是「名次 = 比当前成绩高的人数 + 1」——并列的两个 100 分都排第 1，第三个人前面有 2 个比他高的，所以是第 3 名，中间的「2」被占用了；DENSE_RANK 数的是「不同成绩的个数」，100、95、90 依次是第 1、2、3 名，中间不留空。跳不跳号，取决于「名次」是数人数还是数等级。

PARTITION BY：分组内窗口

-- 每个班级内分数排名
SELECT class, name, score,
  ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class
FROM students;

PARTITION BY class 将数据按班级分组，每个班级内独立排序和编号。相当于”GROUP BY + 窗口”。

💡 思考穿插：为什么窗口函数要单独设一个 PARTITION BY，而不是直接复用 GROUP BY？ 因为二者的目标相反：GROUP BY 要「压缩行数」得到每个分组的汇总，PARTITION BY 要「保留每行」、只在分区内独立算窗口。比如「每个班内部排名」，既要保留每个学生的行，又要让排名只在同班内竞争——这是 GROUP BY 做不到的，它会把学生聚合没了。所以窗口函数必须自备「分区」能力，而不是借用「聚合」能力。

TopN Per Group（面试高频）

-- 每个班级的前 3 名
SELECT class, name, score, rank_in_class
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class
  FROM students
) t
WHERE rank_in_class <= 3;

用子查询 + 窗口函数 → 外层 WHERE 过滤 → 每个分组取 Top N。经典模式，支持任意复杂的分组逻辑。

💭 思考：为什么「每组取前 3 名」不能写成 WHERE ROW_NUMBER() ... <= 3 一步到位，非得套一层子查询？——因为 SQL 的执行顺序是「先 WHERE 过滤行，再 SELECT 计算列」，而窗口函数是在 SELECT 阶段、甚至 ORDER BY 阶段才被计算的，WHERE 执行时窗口函数还没算出来，所以 WHERE 里既不能用窗口函数、也认不出它的别名。要「先算名次、再按名次过滤」，就必须用子查询先把名次变成一张普通表的一列，外层 WHERE 才能筛。记住「窗口函数是 SELECT 阶段的事」，很多「为什么不能直接写」的疑问都能从执行顺序里找到答案。

💡 思考穿插：为什么 TopN 用 ROW_NUMBER 最稳，而不用 RANK？ 因为 ROW_NUMBER 保证每个名次唯一、绝无并列——取 <= 3 一定恰好 3 人；若用 RANK 取前 3，遇到并列（两个并列第 1）会漏掉分数相同但名次被跳到第 4 的人。业务上「取 3 个」要求人数确定，唯一行号才不踩并列的坑。RANK/DENSE_RANK 更适合「排名展示」，ROW_NUMBER 才适合「精确取数量」。

累积和、移动平均

-- 按日期累计销售额
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS cumulative
FROM sales;

-- 每行前后各 2 行的移动平均
SELECT date, amount,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_avg
FROM sales;

💭 思考：为什么 SUM(amount) OVER (ORDER BY date) 得到的是「累积和」，而不是全局总和？——因为窗口函数里的 ORDER BY 除了「排序」，还偷偷定义了「窗口范围」：默认是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即「从第一行到当前行」。于是每一行的 SUM 只加「截至本行为止」的行，自然就是累积值；想让它变成「整个分区的总和」，就省略 ORDER BY、或显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。理解了「ORDER BY 同时决定窗口边界」，移动平均里的 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING 也就通了——那只是把默认窗口换成一个「前后各 2 行」的滑动窗。

窗口函数 vs GROUP BY

窗口函数不压缩行数——它在保持每行独立的基础上，为每行计算”窗口范围内的聚合值”。这就是它和 GROUP BY 最根本的区别。

章末提问

追问 1：ROW_NUMBER、RANK、DENSE_RANK 三者的区别是什么？各用于什么场景？

结论先行：差别只在「如何处理并列」——ROW_NUMBER 并列也硬编号、RANK 并列同号跳号、DENSE_RANK 并列同号不跳号。因为 语义不同：ROW_NUMBER 要「唯一行号」适合 TopN，RANK 要「真实名次」适合比赛排名（有并列就有空档），DENSE_RANK 要「连续名次」适合分等级。选哪个，取决于你想要「序号」还是「名次」。

追问 2：窗口函数和 GROUP BY 的本质区别是什么？

结论先行：GROUP BY 压缩行数、只留聚合结果；窗口函数不压缩行数、每行保留自身并附加窗口聚合值。因为 GROUP BY 是「汇总」，窗口函数是「在不改变行的前提下，给每行算一个带窗口范围的聚合」，所以窗口函数能实现排名、累计、行间差值这类 GROUP BY 做不到的「行级」运算。

追问 3：取每个班级前 3 名，为什么子查询里用 ROW_NUMBER 而不是直接 GROUP BY 或 LIMIT？

结论先行：因为要「每个分组各自取前 N」，LIMIT 只能全局取前 N，GROUP BY 会丢失学生明细。因为 ROW_NUMBER + PARTITION BY 把「分组」和「保留行」两件事同时完成，外层 WHERE 过滤编号就能精确到每个班的前 3 名——这是聚合和全局截断都做不到的。