MySQL 8.0 窗口函数与 CTE 的五个真实场景

MySQL 8.0 的窗口函数和 CTE 属于"知道有、但写的时候想不起来"的特性。多数团队的 SQL 里仍堆着自连接和相关子查询，性能与可读性都吃亏。本文用五个高频业务场景把这两样东西变成日常工具。

一、分组内 Top N

需求：每个分类下点赞最多的 3 篇文章。传统写法是相关子查询，每行都要重跑一次聚合：

SELECT a.* FROM articles a
WHERE (
  SELECT COUNT(*) FROM articles b
  WHERE b.category = a.category AND (b.like_count, b.id) > (a.like_count, a.id)
) < 3;

窗口函数一次扫描解决：

WITH ranked AS (
  SELECT a.*,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY like_count DESC, id) AS rn
  FROM articles a
  WHERE status = 'published'
)
SELECT id, title, category, like_count
FROM ranked WHERE rn <= 3
ORDER BY category, rn;

三个函数要分清：ROW_NUMBER 严格不重复（并列也切开，需加次级排序键保证确定性）、RANK 并列跳号（1,1,3）、DENSE_RANK 并列不跳号（1,1,2）。

性能前提：若存在复合索引 (category, like_count)，窗口排序可复用索引顺序；否则需要 filesort，大表上代价明显。

二、同比环比与累计

WITH daily AS (
  SELECT DATE(created_at) AS d, COUNT(*) AS new_users
  FROM users
  WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  GROUP BY DATE(created_at)
)
SELECT d, new_users,
       SUM(new_users) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS w7_total,
       AVG(new_users)  OVER (ORDER BY d ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS m30_avg,
       LAG(new_users, 7) OVER (ORDER BY d) AS w1_ago
FROM daily ORDER BY d;

两个易错点必须刻住：日期稀疏（没有数据的日期不会出现在 daily 里，ROWS BETWEEN 6 PRECEDING 会算成"往前 7 行"而不是"往前 7 天"），以及 LAG(x, 7) 取的同样是前 7 行。窗口 frame 的单位是行，不是天。

补连续日期序列：

WITH RECURSIVE days AS (
  SELECT DATE_SUB(CURDATE(), INTERVAL 90 DAY) AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < DATE_SUB(CURDATE(), INTERVAL 1 DAY)
)
SELECT days.d, COALESCE(x.new_users, 0) AS new_users
FROM days LEFT JOIN daily x ON x.d = days.d;

递归深度由 cte_max_recursion_depth（默认 1000）控制，长序列要显式调整。

三、递归 CTE 处理树形结构

分类树、评论楼中楼、组织架构。老办法是应用层多次查询拼树，递归 CTE 能一次拿到全树并带深度：

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth, CAST(name AS CHAR(500)) AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1, CONCAT(t.path, ' / ', c.name)
  FROM categories c JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 10
)
SELECT * FROM tree ORDER BY path;

WHERE t.depth < 10 不只是保险，它是防止脏数据成环导致无限递归的必需护栏。递归列要显式 CAST，否则 CONCAT 结果长度被初始 SELECT 的列宽限制住，表现为路径莫名被截断。

四、每组取最新一行

需求：每个用户最近一次登录记录。这是窗口函数最实用的场景之一。

SELECT user_id, ip, login_at
FROM (
  SELECT user_id, ip, login_at,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_at DESC) AS rn
  FROM login_logs
) t
WHERE rn = 1;

反面写法是自连接：

SELECT l.* FROM login_logs l
JOIN (SELECT user_id, MAX(login_at) m FROM login_logs GROUP BY user_id) x
  ON x.user_id = l.user_id AND x.m = l.login_at;

自连接版本在时间并列时会返回多行，且要扫两遍表；ROW_NUMBER 版本一次扫描且结果确定。

五、CTE 复用与物化特性

CTE 最大的工程价值是命名与复用：

WITH active_users AS (
  SELECT DISTINCT user_id FROM actions WHERE dt >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
),
spend AS (
  SELECT user_id, SUM(amount) AS total FROM orders
  WHERE dt >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY user_id
)
SELECT COUNT(DISTINCT a.user_id) AS active,
       SUM(s.total) AS gmv,
       SUM(s.total) / COUNT(DISTINCT a.user_id) AS arpu
FROM active_users a LEFT JOIN spend s ON s.user_id = a.user_id;

重要实现细节：MySQL 的 CTE 是可合并视图，不保证只算一次。被多次引用时优化器可能多次展开，此时 CTE 反而成为性能陷阱。用 EXPLAIN FORMAT=TREE 看展开次数，发现重复就把该 CTE 落成临时表并加主键：

CREATE TEMPORARY TABLE tmp_active AS
  SELECT DISTINCT user_id FROM actions WHERE dt >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);
ALTER TABLE tmp_active ADD PRIMARY KEY(user_id);

同时要注意：CTE 内出现 LIMIT、聚合或 DISTINCT 会强制物化，外层 WHERE 无法下推。需要过滤就把条件下沉到 CTE 内部。

六、执行计划三个检查点

1. 窗口排序上是否出现 Using filesort，考虑建"分区列在前、排序列在后"的复合索引；
2. 递归 CTE 的迭代规模，用 SHOW STATUS LIKE 'Handler_read%' 观察是否爆炸；
3. 中间结果是否落盘，Created_tmp_disk_tables 增长说明超过 tmp_table_size。

EXPLAIN ANALYZE
WITH ranked AS (
  SELECT id, category, like_count,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY like_count DESC) rn
  FROM articles
) SELECT * FROM ranked WHERE rn <= 3;

EXPLAIN ANALYZE 会真正执行并给出每步实际耗时与行数，是窗口函数调优唯一可靠的工具。

小结

值得立即改写的三类旧写法：分组 Top N 与每组最新一行用 ROW_NUMBER、同比累计用 frame 明确的窗口聚合、树形路径用递归 CTE 并设深度上限。同时要记住窗口 frame 的单位是行不是时间（日期稀疏必须补序列），以及 MySQL 的 CTE 可能被多次展开（大结果集落临时表更稳）。养成 EXPLAIN ANALYZE 的习惯，这两样特性就能从"知道有"变成日常工具。