📖 本篇简介：窗口函数是 MySQL 8.0 面试和手写 SQL 的高频考点，也是最容易"写出来结果不对"的一类 SQL——它不像普通聚合那样只出一行，语法细节多、默认框架隐蔽。本章聚焦窗口函数使用中的 11 个真实踩坑：排名函数选错、OVER 漏 ORDER BY、把窗口函数塞进 WHERE、ROWS/RANGE 用反、FIRST_VALUE 取错边界等。示例基于 MySQL 8.0（本机无服务器，排名差异部分用 sqlite3 3.x 实跑验证过输出）。读完你能写出结果正确、性能可控的窗口函数 SQL。
目录
- 坑 1：窗口函数当成 GROUP BY 聚合只出一行
- 坑 2：OVER 里漏写 ORDER BY 导致排名乱
- 坑 3：ROW_NUMBER RANK DENSE_RANK 选错
- 坑 4：把窗口函数写进 WHERE 过滤
- 坑 5：ROWS 和 RANGE 框架用反
- 坑 6：PARTITION BY 和 ORDER BY 列写混
- 坑 7：FIRST_VALUE LAST_VALUE 默认框架取错值
- 坑 8：以为 5.7 也能跑窗口函数
- 坑 9：NTILE 分桶不均匀就以为函数错了
- 坑 10：多个窗口函数重复写一长串 OVER
- 坑 11：窗口函数里再套子查询导致巨慢
- 速查表：窗口函数速记
- 注意事项：写窗口函数前的 Checklist
- 自测：你能写对窗口函数吗
坑 1：窗口函数当成 GROUP BY 聚合只出一行
场景 / 错误示范：
-- 需求：每个班级取分数最高的学生，同时还要保留该生的其他信息
-- 错误写法一：把窗口函数当聚合用，以为它和 MAX 一样只出一行
SELECT cls, name, MAX(score) FROM score GROUP BY cls;
-- name 要么报错（ONLY_FULL_GROUP_BY），要么随便取一行，根本不是最高分那行
-- 错误写法二：误以为窗口函数会折叠行
SELECT cls, name, score, RANK() OVER (PARTITION BY cls ORDER BY score DESC)
FROM score; -- 这会保留全部行，不是每组一行
为什么是坑：窗口函数和普通聚合最大的区别就是——它不折叠行。GROUP BY 每组出一行，窗口函数每一行都出一行，只是多算一列排名/累计值。开发者脑子里还停留在"分组聚合"模型，自然对"为什么出来这么多行"产生误判，进而写出错的筛选条件。
正确做法：
-- 正确：窗口函数负责打排名列，外层再 WHERE 排名 = 1
SELECT * FROM (
SELECT cls, name, score,
RANK() OVER (PARTITION BY cls ORDER BY score DESC) AS rk
FROM score
) t
WHERE rk = 1; -- 外层过滤，拿到每个班最高分（同分并列都保留）
# sqlite3 实跑验证（输出已在本机打印过）：
# ('A', '甲', 90, 1, 1, 1) -- 甲是 A 班第一
# ('A', '乙', 80, 2, 2, 2) -- 乙、丙并列第二
# 外层 WHERE rk=1 就只留下甲
加分点：牢记一句话——窗口函数是"给每行加一列"，不是"每组缩成一行"。要取每组 Top N，必须子查询包一层再过滤。
坑 2：OVER 里漏写 ORDER BY 导致排名乱
场景 / 错误示范：
-- 想按分数排名，结果 OVER 里忘了 ORDER BY
SELECT name, score,
ROW_NUMBER() OVER (PARTITION BY cls) AS rn
FROM score;
-- rn 是 1,2,3... 但按什么排的？数据库没保证，等于随机编号
为什么是坑：排名类函数（ROW_NUMBER/RANK/LAG/SUM 累计）的语义完全依赖 OVER 里的 ORDER BY。漏写它，窗口内行顺序不确定，"第几名"就没有意义，换个执行计划结果就变。这类 SQL 在线上时好时坏，极难复现。
正确做法：
-- 排名类窗口函数必须带 ORDER BY，且尽量再补一个唯一列做稳定排序
SELECT name, score,
ROW_NUMBER() OVER (
PARTITION BY cls
ORDER BY score DESC, id ASC -- score 相同按 id 兜底，结果稳定
) AS rn
FROM score;
加分点：ORDER BY 后再加一个唯一列（主键）做 tie-breaker，保证同样分数下两次跑结果一致，避免"今天排甲明天排乙"。
坑 3：ROW_NUMBER RANK DENSE_RANK 选错
场景 / 错误示范：
-- 需求：班级排名，同分要并列，下一名不跳号
-- 错误：随手用了 ROW_NUMBER
SELECT name, score, ROW_NUMBER() OVER (PARTITION BY cls ORDER BY score DESC) AS rk
FROM score;
-- 两个 80 分被排成 2、3，业务上他们应该并列第 2，下一名是第 3
为什么是坑：三个排名函数语义不同：ROW_NUMBER 行号唯一不并列；RANK 并列且跳号（1,2,2,4）；DENSE_RANK 并列不跳号（1,2,2,3）。选错直接导致"第三名"到底是谁出错，业务报表数字对不上。
正确做法：
-- 用 sqlite3 实跑过的真实输出对照：
-- ('A','甲',90, rn=1, rk=1, drk=1)
-- ('A','乙',80, rn=2, rk=2, drk=2)
-- ('A','丙',80, rn=3, rk=2, drk=2)
-- ('A','丁',70, rn=4, rk=4, drk=3)
SELECT name, score,
ROW_NUMBER() OVER w AS rn, -- 要"去重取一条"用它
RANK() OVER w AS rk, -- 要"并列且跳号（如运动会名次）"用它
DENSE_RANK() OVER w AS drk -- 要"并列不跳号（如分数段）"用它
FROM score
WINDOW w AS (PARTITION BY cls ORDER BY score DESC);
加分点：面试被问"这三个区别"，直接报上面这组输出：80 分两人，ROW_NUMBER 给 2、3，RANK 给 2、2、4，DENSE_RANK 给 2、2、3。
坑 4：把窗口函数写进 WHERE 过滤
场景 / 错误示范：
-- 想取每班第一名，直接在 WHERE 里写窗口函数
SELECT name, score, RANK() OVER (PARTITION BY cls ORDER BY score DESC) AS rk
FROM score
WHERE rk = 1; -- 报错！WHERE 里不能用窗口函数别名
-- Unknown column 'rk' in 'where clause'
为什么是坑：SQL 执行顺序里 WHERE 在 SELECT 之前，窗口函数属于 SELECT 阶段的计算，WHERE 执行时它还没算出来。这和"WHERE 里不能用 SELECT 别名"是同一个道理。
正确做法：
-- 正确：包一层子查询或 CTE，在外层过滤
WITH t AS (
SELECT cls, name, score,
RANK() OVER (PARTITION BY cls ORDER BY score DESC) AS rk
FROM score
)
SELECT * FROM t WHERE rk = 1;
-- 注意：窗口函数也不能用在 GROUP BY、HAVING 的等价位置直接引用
加分点：记执行顺序口诀——FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT(窗口函数) → ORDER BY → LIMIT。窗口函数只能出现在 SELECT 和 ORDER BY 里。
坑 5：ROWS 和 RANGE 框架用反
场景 / 错误示范：
-- 需求：按日期累计每天销售额
SELECT d, v,
SUM(v) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s
FROM t;
-- 问题：同一天有两行（如两笔订单）时，ROW S 只累加当前这行，同一天第二笔漏算
为什么是坑：这是窗口函数最隐蔽的坑。ROWS 按"物理行"算框架，RANGE 按"ORDER BY 表达式的值"算框架。ORDER BY 的列有重复值时，RANGE 会把"同值的所有行"划进同一个 peer group 一起累计，ROWS 不会。两者结果完全不同。
正确做法：
-- sqlite3 实跑过的真实输出（d 有重复的 '2026-01-02'）：
-- ('2026-01-01',10, rows_sum=10, range_sum=10)
-- ('2026-01-02',20, rows_sum=30, range_sum=60)
-- ('2026-01-02',30, rows_sum=60, range_sum=60)
-- 同一天 RANGE 直接累计到 60，ROWS 是逐行 30、60
SELECT d, v,
SUM(v) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_sum,
SUM(v) OVER (ORDER BY d RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_sum
FROM t;
-- 想要"按日期累计"用 RANGE；想要"逐行累计"用 ROWS
-- 扩展：MySQL 8.0 还支持"数值偏移"的 RANGE 框架，做"前后 10 元窗口"分析
-- 每行累计：与当前 price 差值在 10 以内的所有行的金额之和
SELECT d, price,
SUM(amt) OVER (
ORDER BY price
RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
) AS around_price_sum
FROM t;
-- 这种"按值范围开窗"是 ROWS 做不到的，也是 RANGE 真正的威力
加分点：MySQL 8.0 中 SUM/AVG ... OVER(ORDER BY x) 默认框架就是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，不显式写框架时就是 RANGE 语义，这是无数 bug 的来源。
坑 6：PARTITION BY 和 ORDER BY 列写混
场景 / 错误示范：
-- 需求：每个班级内部按分数排名
-- 错误：把排序列写进 PARTITION，把分区列写进 ORDER BY
SELECT cls, name, score,
RANK() OVER (PARTITION BY score ORDER BY cls) AS rk
FROM score;
-- 变成了"每个分数一组，组内按班级排"，完全不是业务要的东西
为什么是坑：PARTITION BY 决定"怎么分组"（窗口切分），ORDER BY 决定"组内按什么排序计算排名/累计"。两列写反，语义天差地别，而且 SQL 不报错，结果悄悄错——这比报错更危险。
正确做法：
-- 分区列=业务上"按什么分组排名"，排序列=组内按什么排序
SELECT cls, name, score,
RANK() OVER (PARTITION BY cls ORDER BY score DESC) AS rk
FROM score;
-- 写完心里默念：先按 cls 切块，每块里按 score 从高到低排
加分点：写完窗口函数先口头翻译一遍这句 SQL 在算什么，翻译不出来就是列写混了。
坑 7：FIRST_VALUE LAST_VALUE 默认框架取错值
场景 / 错误示范：
-- 需求：每行都显示"当前累计区间里的第一条价格"和"最后一条价格"
SELECT d, price,
FIRST_VALUE(price) OVER w AS first_p,
LAST_VALUE(price) OVER w AS last_p
FROM t
WINDOW w AS (ORDER BY d);
-- first_p 是对的；但 last_p 每行都等于当前行 price，不是区间最后一条
为什么是坑：LAST_VALUE 默认框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——到当前行为止。也就是说"窗口最后一条"就是当前这行自己，自然每行都等于自己。这是窗口函数面试最爱考的陷阱。
正确做法：
-- 想要"整个分区的最后一行"，必须把框架扩到分区末尾
SELECT d, price,
FIRST_VALUE(price) OVER w AS first_p,
LAST_VALUE(price) OVER (
ORDER BY d
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 扩到全分区
) AS last_p
FROM t
WINDOW w AS (ORDER BY d);
-- 或者干脆用 LAST_VALUE 换成 FIRST_VALUE(... DESC)，或用 MAX/MIN
-- 等价替代：要"整个分区最后一行"也可以直接用聚合思路
SELECT d, price,
FIRST_VALUE(price) OVER w AS first_p,
MAX(price) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_p
FROM t
WINDOW w AS (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
-- 语义上"最后一条"不等于"最大值"，取具体某行还是用 LAST_VALUE 扩框架更准
加分点：记住"默认框架只到 CURRENT ROW"这条铁律，所有需要"取整个分区某端"的窗口函数，都要手动把 FOLLOWING 开到 UNBOUNDED。
坑 8：以为 5.7 也能跑窗口函数
场景 / 错误示范：
-- 本地是 8.0 写得好好的，上了一台 5.7 的老库直接报错
SELECT RANK() OVER (PARTITION BY cls ORDER BY score DESC) FROM score;
-- 5.7 报错：You have an error in your SQL syntax near '(PARTITION BY ...'
为什么是坑：窗口函数是 MySQL 8.0 才引入的，5.7 完全不支持。很多公司生产还有 5.7 老库，在本地 8.0 写爽了的 SQL 一上线就语法错误。这是升级迁移期最常见的"环境不兼容"坑。
正确做法：
-- 部署前确认目标库版本：SELECT VERSION();
-- 5.7 要算 Top N 只能用变量模拟：
SET @rk := 0;
SELECT name, score,
@rk := IF(@prev_cls = cls, @rk + 1, 1) AS rn, -- 变量模拟行号
@prev_cls := cls
FROM score ORDER BY cls, score DESC;
-- 但变量写法有各种边界坑，能升 8.0 就别在 5.7 上硬写窗口逻辑
加分点：简历/面试说"用了窗口函数"时，主动补一句"确认过目标库是 8.0；5.7 环境我们用变量或离线计算替代"，显得你考虑过兼容性。
坑 9：NTILE 分桶不均匀就以为函数错了
场景 / 错误示范：
-- 想把 10 行数据平均分成 3 桶
SELECT id, NTILE(3) OVER (ORDER BY id) AS bucket FROM t;
-- 结果是 1,1,1,1, 2,2,2, 3,3,3——前两桶多一行，开发者以为函数算错了
为什么是坑：NTILE 的规则是"尽量均分，前面的桶先补满"。10 行分 3 桶，10/3 除不尽，多出来的余数从第一桶开始分配，所以前面的桶行数多。这是设计如此，不是 bug。
正确做法：
-- 接受这个规则：余数优先分给靠前的桶
-- 10 行分 3 桶 = 4,3,3；11 行分 3 桶 = 4,4,3
-- 如果业务要"尽量均匀"，按这个结果做切片即可；
-- 如果要严格等量，先算好总行数 N 和桶数 k，用 ROW_NUMBER() 自己映射：
SELECT id,
CEIL(ROW_NUMBER() OVER (ORDER BY id) * 3.0 / COUNT(*) OVER ()) AS bucket
FROM t;
加分点：NTILE 不适合做"严格等分抽样"，它是近似分桶。要严格等量用 ROW_NUMBER + COUNT 自己算映射更可控。
坑 10：多个窗口函数重复写一长串 OVER
场景 / 错误示范：
-- 一条 SQL 里 5 个窗口函数，每个都把 OVER (PARTITION BY cls ORDER BY score DESC) 抄一遍
SELECT name, score,
RANK() OVER (PARTITION BY cls ORDER BY score DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY cls ORDER BY score DESC) AS drk,
LAG(score) OVER (PARTITION BY cls ORDER BY score DESC) AS prev,
LEAD(score) OVER (PARTITION BY cls ORDER BY score DESC) AS nxt,
SUM(score) OVER (PARTITION BY cls ORDER BY score DESC) AS ssum
FROM score;
-- 5 遍一模一样的窗口定义，写错一个参数就全乱
为什么是坑：同一个窗口定义抄多遍，既冗长又容易改漏。更隐蔽的是：多个相同 OVER 的窗口函数，优化器本可以共用一次窗口计算，分开写反而读起来让人怀疑是不是故意不同。
正确做法：
-- 用 WINDOW 子句给窗口起别名，一处定义多处复用
SELECT name, score,
RANK() OVER w AS rk,
DENSE_RANK() OVER w AS drk,
LAG(score) OVER w AS prev,
LEAD(score) OVER w AS nxt,
SUM(score) OVER w AS ssum
FROM score
WINDOW w AS (PARTITION BY cls ORDER BY score DESC);
加分点：WINDOW 别名还可以在 OVER 里局部覆盖（OVER (w ORDER BY name)），写法既干净又不易错。
坑 11：窗口函数里再套子查询导致巨慢
场景 / 错误示范：
-- 在窗口函数的参数里嵌子查询，或者窗口内层套多层子查询
SELECT id,
RANK() OVER (ORDER BY (SELECT MAX(p.price) FROM price p WHERE p.id = o.id) DESC) AS rk
FROM orders o;
-- 每行都触发一次相关子查询，N 行就是 N 次子查询，慢到爆炸
为什么是坑：窗口函数本身已经要做一次排序/分区，参数里再放相关子查询等于把相关子查询的开销乘到每一行。大表上这种写法就是慢查询事故的源头。
正确做法：
-- 先把子查询结果 JOIN 成普通列，再在窗口函数里引用这个列
WITH price_max AS (
SELECT o.id, MAX(p.price) AS max_price
FROM orders o LEFT JOIN price p ON p.id = o.id
GROUP BY o.id
)
SELECT id, max_price,
RANK() OVER (ORDER BY max_price DESC) AS rk
FROM price_max;
-- 窗口函数里只引用普通列，让优化器一次扫表完成
-- 排查手段：EXPLAIN 看窗口函数的执行
EXPLAIN
WITH price_max AS (
SELECT o.id, MAX(p.price) AS max_price
FROM orders o LEFT JOIN price p ON p.id = o.id
GROUP BY o.id
)
SELECT id, max_price, RANK() OVER (ORDER BY max_price DESC) AS rk FROM price_max;
-- Extra 列出现 "Window aggregate" / "Using filesort" 属正常；
-- 若 type=DEPENDENT SUBQUERY 出现在窗口层，就是嵌套相关子查询在拖慢
加分点：慢查询里看到窗口函数，先 EXPLAIN 看是不是窗口内嵌套了相关子查询；把"算值"和"排名"拆成两步，通常就能优化下来。
速查表：窗口函数速记
| 点 | 错误认知 | 正确结论 |
|---|---|---|
| 行粒度 | 每组出一行 | 每行出一行，只是多一列 |
| OVER 漏 ORDER BY | 能跑就行 | 排名类必须带 ORDER BY，否则无序 |
| 排名函数 | 三个随便用 | ROW_NUMBER 不并列/RANK 并列跳号/DENSE_RANK 并列不跳号 |
| WHERE 过滤 | WHERE rk=1 | 子查询/CTE 包一层再过滤 |
| 默认框架 | 全分区 | 默认 RANGE 到 CURRENT ROW |
| LAST_VALUE | 取分区最后一行 | 默认只到当前行，要扩 FOLLOWING |
| 5.7 兼容 | 都能跑 | 8.0 才有，5.7 用变量替代 |
| NTILE | 严格等分 | 余数靠前桶，近似分桶 |
| 重复 OVER | 抄多遍 | WINDOW 子句起别名复用 |
| 窗口内子查询 | 能写 | 先 JOIN 成列再排名，避免相关子查询 |
| 复杂窗口复用 | 抄多遍 | WINDOW 子句起别名 |
| 数值偏移窗口 | 做不到 | RANGE BETWEEN n PRECEDING AND n FOLLOWING |
注意事项：写窗口函数前的 Checklist
任何窗口函数 SQL 提交前过一遍：
- [ ] 我是要"每行加一列"还是"每组缩一行"？想清楚了吗？
- [ ] 排名类函数的 OVER 里写 ORDER BY 了吗？tie-breaker 唯一列补了吗？
- [ ] 过滤排名是不是包在子查询外层做的（不是写进 WHERE）？
- [ ] 用到 SUM/LAST_VALUE/LAG 时，框架范围（ROWS/RANGE）是我要的吗？
- [ ] 目标库是 8.0 吗？5.7 上有没有替代方案？
- [ ] EXPLAIN 看过吗，窗口里有没有嵌套相关子查询？
一句话：窗口函数不折叠行、默认框架只到当前行、过滤要包外层——这三条背下来能避开 90% 的坑。
自测：你能写对窗口函数吗
题 1：RANK() OVER (PARTITION BY cls ORDER BY score DESC) 忘了写 ORDER BY，结果会怎样？
<details>
<summary>参考答案</summary>
窗口内行顺序未定义，排名变得不确定，换个执行计划结果就变。排名类函数必须带 ORDER BY，并尽量补唯一列做稳定排序。
</details>
题 2：三个同分的人，ROW_NUMBER、RANK、DENSE_RANK 分别给什么值？
<details>
<summary>参考答案</summary>
假设他们原本是第 2 名同分：ROW_NUMBER 给 2、3、4（不并列）；RANK 给 2、2、2，下一名跳到 5；DENSE_RANK 给 2、2、2，下一名是 3。
</details>
题 3：为什么 SELECT ..., RANK() OVER(...) rk FROM t WHERE rk=1 报错？
<details>
<summary>参考答案</summary>
WHERE 在 SELECT 之前执行，窗口函数别名此时还没算出来。必须包一层子查询或 CTE，在外层 WHERE rk=1。
</details>
题 4：LAST_VALUE(price) OVER (ORDER BY d) 为什么每行都等于当前行？
<details>
<summary>参考答案</summary>
默认框架是 RANGE UNBOUNDED PRECEDING 到 CURRENT ROW，"窗口最后一行"就是当前行自己。要取分区末尾必须扩到 UNBOUNDED FOLLOWING。
</details>
题 5：ROWS 和 RANGE 在 ORDER BY 列有重复值时，累计结果差在哪？
<details>
<summary>参考答案</summary>
ROWS 按物理行逐行累计；RANGE 把同值的所有 peer 一起累计。如同一天两行 20、30，ROWS 得到 30、60，RANGE 得到 60、60。
</details>
题 6：MySQL 5.7 上能用 ROW_NUMBER 吗？不能的话怎么替代？
<details>
<summary>参考答案</summary>
不能，窗口函数是 8.0 才有的。5.7 只能用用户变量（@rk 按组重置）模拟行号，或在应用层/离线计算。
</details>
题 7：NTILE(3) 把 10 行分桶，为什么前两桶多一行？是 bug 吗？
<details>
<summary>参考答案</summary>
不是 bug。10/3 除不尽，余数优先分给靠前的桶，结果是 4、3、3。NTILE 是近似分桶，要严格等量用 ROW_NUMBER + COUNT 自己映射。
</details>
题 8：一条 SQL 里 4 个窗口函数都用同一个 OVER，怎么写更干净？
<details>
<summary>参考答案</summary>
用 WINDOW 子句给窗口起别名（WINDOW w AS (...)），各函数写 OVER w，一处定义多处复用，避免抄错。
</details>
题 9：窗口函数里塞相关子查询为什么慢？怎么改？
<details>
<summary>参考答案</summary>
每行触发一次子查询，N 行 N 次。先把子查询结果 JOIN/CTE 成普通列，再在窗口函数里引用该列，让优化器一次完成。
</details>
题 10：要"每个班级分数最高的人"，正确 SQL 骨架是什么？
<details>
<summary>参考答案</summary>
WITH t AS (SELECT *, RANK() OVER (PARTITION BY cls ORDER BY score DESC) rk FROM score) SELECT * FROM t WHERE rk=1;，用 RANK 保留并列最高分。
</details>
题 11：为什么 SUM(v) OVER (ORDER BY d) 在 d 有重复值时，结果和直觉不一样？
<details>
<summary>参考答案</summary>
默认框架是 RANGE 语义，同 d 的所有行会被并进同一 peer group 一起累计，而不是逐行累加。想要逐行累计要显式写 ROWS。这是窗口函数最常见的"结果悄悄错"。
</details>
下一篇预告：第 52 章 8.0新特性踩坑
文章回复
0 条公开回复