MySQL 8.0 窗口函数与 CTE（公用表表达式）是查询写法的分水岭。过去要算”分组内排名””累计求和””同环比”，往往要写多层嵌套子查询；现在一行 OVER() 就能表达。本文用可复跑的实战案例，带你掌握这两类特性，并把它们组合成清晰、易维护的 SQL。
窗口函数与 CTE 并非 MySQL 首创，PostgreSQL、SQL Server、Oracle 早已支持多年，但作为最主流的开源关系库之一，MySQL 在 8.0 才补齐这套能力，意味着大量仍运行在 5.7 的老系统迁移到 8.0 后，能直接享受更现代的写法。如果你的线上库还停留在 5.7，升级前务必在测试环境验证窗口函数的执行计划与兼容性。
一、为什么需要窗口函数
在 8.0 之前，MySQL 想要”每个部门薪资最高的前 3 名员工””订单的累计金额””相比上一天的环比”这类需求，几乎都依赖关联子查询或临时表，不仅冗长，优化器也难以给出好计划。窗口函数不改变行数——它在不聚合掉明细的前提下，为每一行计算一个”基于某组相关行”的值，这正是它与 GROUP BY 的本质区别。
打个比方：GROUP BY 像把一叠纸按类别装订成册、每册只留一张汇总页；窗口函数则保留每一页，同时在每页旁手写一行”你在本科目里的名次/累计值”。正因为保留明细，窗口函数特别适合既要展示明细、又要计算汇总指标的报表场景，比如”列出每个商品的销量，同时标注它在品类内的排名”。
配套登场的 CTE（WITH 子句）则把”中间结果”从嵌套子查询里解放出来，让复杂查询像搭积木一样分层表达，可读性大幅提升。关于索引如何支撑这类分析查询，可参考 MySQL 索引实战 与 MySQL 性能调优。
二、窗口函数语法骨架
2.1 PARTITION BY 与 ORDER BY
窗口函数的核心是一个 OVER(...) 子句：PARTITION BY 决定”哪些行算一组”（类似 GROUP BY 但不合并），ORDER BY 决定组内计算顺序。ROWS/RANGE BETWEEN 还能进一步限定”滑动窗口”的上下界。
这里有个容易混淆的点：PARTITION BY 控制”分组边界”，ORDER BY 控制”组内顺序”，二者相互独立。没有 ORDER BY 时，聚合类窗口函数（如 SUM OVER）默认对整组求和；一旦加上 ORDER BY，默认就变成”从组首累计到当前行”。这个默认行为的切换，正是写出正确累计指标的关键，也是新手最容易踩的坑。
2.2 窗口函数分类
| 类别 | 代表函数 | 典型用途 |
|---|---|---|
| 排名 | ROW_NUMBER / RANK / DENSE_RANK | 分组内排序、取 Top N |
| 分布 | PERCENT_RANK / CUME_DIST / NTILE | 分位、分桶 |
| 偏移 | LAG / LEAD / FIRST_VALUE / NTH_VALUE | 取前/后行、环比 |
| 聚合 | SUM/AVG/COUNT OVER | 累计、移动平均 |
三、实战：分组内排名
需求：每个部门按薪资降序排名，取前 3。下面用三张排名函数对比其行为差异——RANK 遇并列会跳号，DENSE_RANK 不跳号，ROW_NUMBER 永远连续（并列时顺序不确定，可再加次要排序键）。
SELECT
dept_id,
emp_name,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drk
FROM employees;
-- 取每个部门薪资前 3：把上面的查询包一层
SELECT * FROM (
SELECT dept_id, emp_name, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
) t WHERE rn <= 3;注意：用 ROW_NUMBER 取 Top N 时，若有并列薪资，”第 3 名”可能漏掉并列者；对榜单类需求常改用 DENSE_RANK 或显式次要排序键。
另一个经常被忽略的细节：窗口函数里的 ORDER BY 只决定”如何计算”，并不保证最终结果的输出顺序。如果你的报表要求”按排名升序展示”，一定要在整条语句最外层再写一次 ORDER BY，否则返回行的顺序是不确定的。这一点在做分页接口时尤为重要。
四、实战：累计与滑动窗口
需求：按天计算销售额的”累计总和”与”近 3 天移动平均”。ORDER BY 后不写窗口边界默认到当前行，配合 ROWS BETWEEN 可精确控制滑动范围。
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(amount) OVER (ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM daily_sales
ORDER BY sale_date;UNBOUNDED PRECEDING 表示”从组内第一行到当前行”，适合累计；2 PRECEDING AND CURRENT ROW 则是含当前行在内的最近 3 行，适合移动平均。与MySQL 主从复制配合做读写分离时，这类分析查询通常应落在只读从库上，避免拖慢线上写入。
五、实战：LAG / LEAD 取前后行
需求：计算每天销售额相对前一天的环比。无需自关联，LAG 直接取”上一行”，LEAD 取”下一行”，第三个参数可设默认值（如首行无前值时返回 0）。
SELECT
sale_date,
amount,
LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_day,
ROUND((amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date))
/ NULLIF(LAG(amount, 1, 0) OVER (ORDER BY sale_date), 0) * 100, 2) AS mom_pct
FROM daily_sales
ORDER BY sale_date;六、CTE 公用表表达式
6.1 非递归 CTE 拆解复杂查询
WITH 定义的临时结果集只在一条语句内有效，却能让查询像函数一样分层。下例先把”每个部门平均薪资”算出来，再筛选高于平均的部门，逻辑一目了然。
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
)
SELECT e.dept_id, e.emp_name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;6.2 递归 CTE 生成序列与遍历树
递归 CTE 由”锚点 + 递归体”组成，非常适合生成数字序列或遍历组织结构树。下面生成 1 到 10 的序列：
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;换成 employees 里带 manager_id 的自关联字段，只需把锚点设为 CEO（manager_id IS NULL），递归体 JOIN 自身即可层层下钻出整棵汇报树。
递归 CTE 虽强，但有安全阀：MySQL 默认限制递归深度为 1000（参数 cte_max_recursion_depth），超过会报错中断，这其实保护了实例不被意外环路拖垮。如果你确实需要更深的展开，应显式调大该参数并确认数据无环；生产环境建议配合应用层加深度上限，避免一张脏数据把整条查询打挂。
七、窗口函数 + CTE 组合实战
把两者结合：先用 CTE 准备基础数据，再用窗口函数一次性算出”部门内排名 + 部门薪资占比”。这种写法比多重子查询更容易验证正确性。
你会发现组合后的 SQL 读起来像一段”数据管道”：第一步准备中间集，第二步在其上做窗口计算，语义清晰、可调试性强。当查询再复杂一层时，还可以把多个 CTE 用逗号串联，每层只解决一个问题，远比一层套一层的嵌套 SELECT 容易维护，也更容易让同事 Code Review 时一眼看懂你的意图。
WITH base AS (
SELECT dept_id, emp_name, salary,
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total
FROM employees
)
SELECT dept_id, emp_name, salary,
ROUND(salary / dept_total * 100, 2) AS pct_in_dept,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM base
ORDER BY dept_id, rn;八、性能与避坑
| 常见误区 | 建议做法 |
|---|---|
在 WHERE 里直接引用窗口函数别名 | 窗口函数执行晚于 WHERE，需用子查询或 CTE 包一层再过滤 |
大表无索引做 PARTITION BY/ORDER BY | 确保排序列有索引，避免额外排序（filesort） |
用 ROW_NUMBER 取 Top N 漏掉并列 | 榜单场景改用 DENSE_RANK 或加次要排序键 |
| 递归 CTE 无限展开 | 数据有环时加深度上限，避免死循环拖垮实例 |
本地用 Docker 跑 MySQL 8.0 即可随时复跑上述示例；生产环境建议把分析类查询放到只读从库，并通过 VS Code 的数据库插件做交互式调试。
九、小结
窗口函数让”组内计算”摆脱嵌套子查询，CTE 让”分层逻辑”摆脱括号地狱。二者都是 MySQL 8.0 后每个后端工程师该掌握的硬技能：排名用 ROW_NUMBER/RANK，累计与移动平均用 SUM/AVG OVER，环比用 LAG/LEAD，复杂拆解用 WITH，树与序列用递归 CTE。把它们写进你的 SQL 工具箱，复杂报表的 SQL 会短一半、稳一倍。
最后给一条落地建议：不要为了”炫技”而堆窗口函数。先用最朴素的写法跑通结果，再用窗口函数做一次重构对比，确认两者输出一致后再上线。对于高频的分析查询，记得结合前文提到的索引与只读从库策略，让”写得好看”和”跑得飞快”同时成立。当你习惯这套思维后，会发现很多曾经要写几十行的报表需求，如今十行就能清晰收尾。
