窗口函数与 CTE

这一课你将能做到

1. 用 ROW_NUMBER/RANK/DENSE_RANK 排名，并用「套一层过滤 rn=1」的固定套路取每人 Top-N
2. 用 LAG/LEAD 做行间比较、用 SUM(...) OVER 算累计值，并解释 ROWS 与 RANGE 帧的差异
3. 用 CTE 把多层逻辑写成命名步骤，用 WITH RECURSIVE 生成序列

为什么要学

「每人最快的一次 AC」「按 AC 数排名」「相邻两次提交间隔了多久」——这些需求 GROUP BY 做不到（它会把明细行折叠掉），子查询写得又臭又长。窗口函数是 MySQL 8 最重磅的新能力：保留每一行明细，同时在旁边挂上组内计算结果。它也是现在面试的分水岭题：「找出每个部门工资前三名」没有窗口函数基本写不出优雅解。

人话：GROUP BY 是「把 12 行压成 4 行」，窗口函数是「12 行还在，每行多挂一个数」。想看明细+排行，只能靠它。

先建立图像

GROUP BY：      12 行提交 ──折叠──► 4 行（每人一行）      明细没了
窗口函数：      12 行提交 ──保留──► 12 行，每行多一列      明细还在

         user_id │ runtime_ms │ rn(组内按时间编号)
         ────────┼────────────┼─────
         lin     │ 12         │ 1
         lin     │ 30         │ 2   ← 行没合并，只是"多了个编号"
         lin     │ 25         │ 3
         zhao    │ 48         │ 1
         zhao    │ 2000       │ 2
         ...

CTE 则是把长 SQL 从「嵌套洋葱」改成「带名字的流水线」：

WITH step1 AS (...)   ← 先算每人 AC 数
   , step2 AS (...)   ← 再筛 AC 率
SELECT ... FROM step2 ← 最后出结果

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT，恢复初始数据

步骤 1：ROW_NUMBER——每人按提交时间编号

SELECT
  user_id,
  runtime_ms,
  submitted_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY submitted_at) AS rn
FROM cf_submission;

预期：每人组内 1、2、3 顺次编号（lin 3 行、zhao 3 行、wang 2 行、zhou 3 行、sun 1 行）。PARTITION BY 是「分组但不合并行」，ORDER BY 决定组内序号怎么排。

步骤 2：RANK vs DENSE_RANK——按 AC 数排名

WITH ac AS (
  SELECT user_id, SUM(status = 'accepted') AS ac_cnt
  FROM cf_submission GROUP BY user_id
)
SELECT
  u.username,
  a.ac_cnt,
  ROW_NUMBER() OVER (ORDER BY a.ac_cnt DESC, u.id) AS rn,
  RANK()       OVER (ORDER BY a.ac_cnt DESC)       AS rk,
  DENSE_RANK() OVER (ORDER BY a.ac_cnt DESC)       AS drk
FROM ac a JOIN cf_user u ON u.id = a.user_id
ORDER BY rk, u.id;

预期结果（lin/zhao/zhou 各 2 AC 并列，wang 1，sun 0）：

username	ac_cnt	rn	rk	drk
lin	2	1	1	1
zhao	2	2	1	1
zhou	2	3	1	1
wang	1	4	4	2
sun	0	5	5	3

三人并列第 1 时：RANK 跳到 4（并列占位），DENSE_RANK 只进到 2（并列不占位），ROW_NUMBER 强行分出 1/2/3。取「第 N 名」用 RANK，取「前 N 行」用 ROW_NUMBER。

步骤 3：每人最快的一次 AC（Top-1 模板）

SELECT username, title, runtime_ms
FROM (
  SELECT
    u.username, p.title, s.runtime_ms,
    ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY s.runtime_ms) AS rn
  FROM cf_submission s
  JOIN cf_user u   ON u.id = s.user_id
  JOIN cf_problem p ON p.id = s.problem_id
  WHERE s.status = 'accepted'
) t
WHERE rn = 1
ORDER BY runtime_ms;

预期：lin/两数之和/12、zhou/LRU 缓存/20、wang/两数之和/15、zhao/图的广度优先遍历/48（sun 与 qian 无 AC 被过滤）。窗口函数的过滤必须套一层——窗口计算发生在 SELECT 阶段，WHERE 里碰不到 rn。

步骤 4：LAG——相邻两次提交的间隔

SELECT
  user_id,
  submitted_at,
  runtime_ms,
  LAG(submitted_at) OVER (PARTITION BY user_id ORDER BY submitted_at) AS prev_time,
  TIMESTAMPDIFF(MINUTE,
    LAG(submitted_at) OVER (PARTITION BY user_id ORDER BY submitted_at),
    submitted_at) AS gap_minutes
FROM cf_submission
WHERE user_id = 1;
-- 预期（lin）：第 1 行 prev_time 为 NULL；第 2 行间隔 35 分钟；第 3 行跨天约 875 分钟

组内第一行没有「上一行」，LAG 返回 NULL（可写第三参数给默认值）。环比分析、留存计算全靠 LAG/LEAD。

步骤 5：ROWS 与 RANGE——默认帧的坑

SELECT
  submitted_at,
  runtime_ms,
  SUM(runtime_ms) OVER (ORDER BY DATE(submitted_at)) AS s_range,
  SUM(runtime_ms) OVER (ORDER BY DATE(submitted_at)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s_rows
FROM cf_submission
ORDER BY submitted_at
LIMIT 5;

预期前五行：

submitted_at	runtime_ms	s_range	s_rows
06-01 09:05	12	42	12
06-01 09:40	30	42	42
06-02 10:00	25	67	67
06-03 20:15	48	2115	115
06-03 21:00	2000	2115	2115

关键差异在第一天：RANGE 默认帧把「同一天」（ORDER BY 值相同）的行全部并入当前行，所以第一行就看到 42；ROWS 帧严格逐行累加。做累计值必须显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，否则同值并列时数字会「跳变」。

步骤 6：CTE 与递归 CTE

-- 普通 CTE：AC 率达标名单（逻辑与第 4 课相同，但分步命名）
WITH ac AS (
  SELECT user_id,
         COUNT(*) AS total,
         SUM(status = 'accepted') AS ac_cnt
  FROM cf_submission
  GROUP BY user_id
)
SELECT u.username, a.total, a.ac_cnt
FROM ac a JOIN cf_user u ON u.id = a.user_id
WHERE a.ac_cnt / a.total >= 0.6;

-- 递归 CTE：生成 1..5
WITH RECURSIVE seq AS (
  SELECT 1 AS n                    -- 锚点：起点
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5 -- 递归：每次 +1，WHERE 是终止条件
)
SELECT * FROM seq;
-- 预期 1 2 3 4 5

递归 CTE 三段式：锚点 + UNION ALL + 带终止条件的递归体。忘了终止条件会撞上默认 1000 层上限报 ERROR 3636。

讲清楚要点

1. OVER 三要素：PARTITION BY（分组窗口）、ORDER BY（组内排序，决定编号/累计顺序）、帧（ROWS/RANGE，决定累计看哪些行）。只有 OVER () 空窗口时是对全表计算。
2. 窗口函数发生在 SELECT 阶段：所以它不能出现在 WHERE/GROUP BY/HAVING 里（那时组还没算完），过滤窗口结果必须套一层派生表或 CTE——这是本课使用频率最高的固定套路。
3. 三个排名函数的差异要能背：ROW_NUMBER 无并列（1,2,3）、RANK 并列同号后跳号（1,1,3）、DENSE_RANK 并列同号不跳号（1,1,2）。分页取前 N 用 ROW_NUMBER，展示名次用 RANK/DENSE_RANK。
4. 累计必写 ROWS 帧：默认帧是 RANGE UNBOUNDED PRECEDING TO CURRENT ROW，ORDER BY 值有重复时会合并并列行导致累计跳变。显式 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 是累计值的标准写法。
5. LAG/LEAD 是「行间比较」的万能钥匙：LAG 看上一行、LEAD 看下一行，第三参数给默认值。环比、留存、间隔、连续性判断都从它出发。
6. CTE 让 SQL 从洋葱变流水线：每个 WITH ... AS 都是一个命名的中间结果，可以被后面的步骤引用（普通 CTE 只能引用一次，需要复用多次时 8.0 也支持，但注意可能被多次物化）。逻辑超过两层嵌套就无脑换 CTE。

你可能会懵的地方

• 窗口函数为什么不能放在 WHERE 里？ 执行顺序：WHERE 在 GROUP BY/SELECT 之前，而窗口计算发生在 SELECT 阶段（且作用于分组/排序之后的结果）。想过滤窗口列，套一层 SELECT * FROM (...) t WHERE rn = 1。

• PARTITION BY 和 GROUP BY 能同时用吗？ 能，而且常见。先 GROUP BY 折叠（如按天统计每人提交数），再用窗口在折叠结果上排名或算环比——「GROUP BY 的输出」就是窗口函数的输入行。

• MySQL 5.7 为什么报语法错？ 窗口函数和 CTE 都是 8.0 才有的。OVER 在 5.7 里被当成聚合函数缺少 GROUP BY 报错。这是本 track 强制 MySQL 8 的原因之一。

• 递归 CTE 的深度上限能改吗？ 能：SET SESSION cte_max_recursion_depth = 100000;。生成日期序列这类需求先估算好范围，别无终止地递归。

• 窗口函数会让查询变慢吗？ 会带来一次组内排序（PARTITION BY + ORDER BY 的组合）。数据量大时给表建上与窗口排序一致的联合索引（如 (user_id, submitted_at)）能显著缓解，原理与第 11 课的索引设计相同。

错法对照

错法	问题	正解
WHERE rn = 1 直接过滤窗口列	WHERE 阶段窗口还没算	套派生表/CTE 再过滤
累计用默认帧不写 ROWS	同值并列时累计跳变	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
递归 CTE 忘写终止条件	ERROR 3636 深度超限	递归体内必须带收敛条件
LEAD 不给默认值	末行 NULL 参与后续计算出错	LEAD(col, 1, 默认值) 或 COALESCE
该用 GROUP BY 的地方用窗口	输出行数不符预期（明细全保留）	折叠用 GROUP BY，明细加列用窗口

卡点急救

1. ERROR 3587 Window function found where not allowed：窗口函数写进了 WHERE/GROUP BY/HAVING。移到 SELECT 里，过滤需求套一层。
2. ERROR 3636 Recursive query aborted after N iterations：递归没收敛或超过 cte_max_recursion_depth。检查终止条件；确需更深则调大会话变量。
3. 排名结果与预期差一位：检查 ORDER BY 是否漏了决胜列（并列时顺序不定），以及 PARTITION BY 的分组列是否选对（按 user_id 还是按题分组）。

面试常问

• ROW_NUMBER、RANK、DENSE_RANK 的区别？ 数据 1、1、2 时三者输出：ROW_NUMBER 1,2,3（强行排序）；RANK 1,1,3（并列后跳号）；DENSE_RANK 1,1,2（并列不跳号）。取前 N 行用 ROW_NUMBER，报名次用后两者。

• SQL 找出每个部门工资前三的员工？ 标准答案：ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) 套一层 WHERE rn <= 3。追问「并列第三算不算」则换 RANK 或 DENSE_RANK——能主动问出这个细节是亮点。

• SQL 判断连续 N 天登录的思路？ 「日期减去按日期排序的行号」：连续日期的差值恒定，按「用户 + 差值」分组即可得到每段连续区间的起点与长度。第 12 课综合练习有完整可跑代码。

自测三问

1. OVER (PARTITION BY user_id ORDER BY submitted_at) 的三要素分别是什么、各管什么？
2. 为什么做累计值必须显式写 ROWS 帧？用本课步骤 5 的数据说明。
3. RANK 与 DENSE_RANK 在「并列第 1 之后」的编号分别是什么？

小结一句话

窗口函数保留明细行并在其上挂组内计算（排名/累计/行间比较），CTE 把多层逻辑变成命名流水线——两者组合是 8.0 时代分析 SQL 的标准姿势。

延伸

• MySQL 8.0 Reference Manual — Window Functions
• MySQL 8.0 Reference Manual — WITH (CTE)
• 下一课：视图与存储过程——把常用查询和批处理逻辑固化到数据库里。