第 11 章窗口函数面试精讲

站长2026-10-31 14:00:00

内容摘要

窗口函数（Window Functions）是 MySQL 8.0 最重要的 SQL 特性之一，也是中高级面试的高频考点。本篇从窗口函数的基本概念出发，系统讲解 OVER、PARTITION BY、ORDER BY、FRAME 帧、ROW_NUMBER/RANK/DENSE_RANK、LAG/LEAD、SUM/AVG 累计、NTILE、first_value/last_value 等核心用法，并对比窗口函数与 GROUP BY、自连接、相关子查询的性能差异。本示例基于 MySQL 8.0，学完后你能手写"分组 TopN""连续登录""累计求和""同比环比"等高频 SQL，并能在面试中讲清楚帧（FRAME）的默认行为与坑。

📖 本篇简介：窗口函数（Window Functions）是 MySQL 8.0 最重要的 SQL 特性之一，也是中高级面试的高频考点。本篇从窗口函数的基本概念出发，系统讲解 OVER、PARTITION BY、ORDER BY、FRAME 帧、ROW_NUMBER/RANK/DENSE_RANK、LAG/LEAD、SUM/AVG 累计、NTILE、first_value/last_value 等核心用法，并对比窗口函数与 GROUP BY、自连接、相关子查询的性能差异。本示例基于 MySQL 8.0，学完后你能手写"分组 TopN""连续登录""累计求和""同比环比"等高频 SQL，并能在面试中讲清楚帧（FRAME）的默认行为与坑。

题目 1：窗口函数与普通 GROUP BY 聚合有什么区别
题目 2：OVER() 括号里可以写哪些子句，各自作用是什么
题目 3：ROW_NUMBER、RANK、DENSE_RANK 三者区别，举个例子
题目 4：什么是窗口帧 FRAME，默认帧是什么
题目 5：ROWS BETWEEN 与 RANGE BETWEEN 有什么区别
题目 6：手写"每个部门工资 Top3"的 SQL
题目 7：LAG 与 LEAD 怎么用，典型业务场景是什么
题目 8：如何用窗口函数计算累计求和与同比环比
题目 9：NTILE 的作用与常见坑是什么
题目 10：FIRST_VALUE、LAST_VALUE、NTH_VALUE 怎么用
题目 11：窗口函数能否写在 WHERE 里过滤结果
题目 12：窗口函数与相关子查询、自连接相比性能如何
题目 13：COUNT(*) OVER() 与 COUNT(*) GROUP BY 的区别
题目 14：多个窗口函数能否共用一个窗口定义（WINDOW 子句）
题目 15：MySQL 8.0 之前没有窗口函数，如何实现分组 TopN
题目 16：窗口函数对索引的利用情况如何，会不会全表扫描
题目 17：如何用窗口函数找出连续登录 N 天的用户
题目 18：SUM() OVER(PARTITION BY ... ORDER BY ...) 不加帧会怎样
题目 19：窗口函数在 EXPLAIN 里看什么，Using temporary 是什么意思
题目 20：常见坑与注意事项
速查表：窗口函数速记
复习清单
练习题

题目 1：窗口函数与普通 GROUP BY 聚合有什么区别

考点

窗口函数不收缩行数，GROUP BY 会把多行合并成一行
窗口函数可以在保留明细行的同时附带聚合值
两者语法位置：窗口函数写在 SELECT，GROUP BY 作用于整个结果集

参考答案

1. 行数不收缩：普通 GROUP BY dept 会把每个部门的多行压成一行；而 SUM(salary) OVER(PARTITION BY dept) 会保留每一条员工明细，同时在每行后面附上该部门的工资总和。

2. 可以同时看明细与聚合：窗口函数允许"既看张三工资，又看部门平均工资"，不需要子查询或自连接。

3. 执行顺序：窗口函数在 WHERE、GROUP BY、HAVING 之后、ORDER BY 之前执行，因此不能在 WHERE 中引用窗口函数别名。

4. 示例对比：

-- 普通聚合：每个部门只剩一行
SELECT dept, SUM(salary) AS dept_sum FROM emp GROUP BY dept;

-- 窗口函数：保留每个员工一行，同时带出部门工资合计
SELECT name, dept, salary,
       SUM(salary) OVER (PARTITION BY dept) AS dept_sum
FROM emp;

追问

追问：窗口函数能不能和 GROUP BY 一起用？应对：可以，但窗口函数作用于 GROUP BY 之后的结果集，即先分组聚合再开窗。
追问：窗口函数能否出现在 WHERE？应对：不能，因为执行顺序在 WHERE 之后；要过滤窗口结果需包一层子查询或 CTE。

题目 2：OVER() 括号里可以写哪些子句，各自作用是什么

考点

PARTITION BY：按列分区，类似 GROUP BY 但不收缩行
ORDER BY：分区内排序，影响排名类与累计类函数
帧定义 ROWS/RANGE BETWEEN：限定窗口计算范围

参考答案

1. PARTITION BY 列：把结果集按列切成多个分区，窗口函数在每个分区内独立计算；不写则整个结果集是一个分区。

2. ORDER BY 列：分区内排序，是 ROW_NUMBER/RANK、累计 SUM、LAG/LEAD 等函数的语义基础；不写 ORDER BY 时排名类函数结果不确定。

3. 帧子句：ROWS BETWEEN ... AND ... 或 RANGE BETWEEN ... AND ...，定义当前行参与计算的行范围；只有带 ORDER BY 时帧才有意义。

4. 完整语法骨架：

func_name(expr) OVER (
  PARTITION BY col1, col2   -- 分区
  ORDER BY col3 DESC        -- 分区内排序
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 帧
)

追问

追问：OVER() 空括号代表什么？应对：不分区、不排序、不指定帧，整个结果集作为一个窗口，常用于 COUNT(*) OVER() 求总行数。
追问：PARTITION BY 后面能写表达式吗？应对：可以，例如 PARTITION BY DATE(create_time)。

题目 3：ROW_NUMBER、RANK、DENSE_RANK 三者区别，举个例子

考点

并列名次处理方式不同
必须配合 ORDER BY 使用
业务中 TopN 取数首选 ROW_NUMBER

参考答案

假设有分数列 score = (100, 90, 90, 80)：

|-------|-----------|------|------------|

| 100 | 1 | 1 | 1 |

| 90 | 2 | 2 | 2 |

| 90 | 3 | 2 | 2 |

| 80 | 4 | 4 | 3 |

1. ROW_NUMBER：不管并列，强行给 1,2,3,4 的连续编号。

2. RANK：并列同名次，下一名次跳号（两个第 2 名后直接是第 4 名）。

3. DENSE_RANK：并列同名次，但不跳号（两个第 2 名后是第 3 名）。

-- 按分数排名，三种写法对比
SELECT 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 drk
FROM exam_score;

追问

追问：ORDER BY 的列有重复值时 ROW_NUMBER 稳定吗？应对：不稳定，MySQL 不保证同值行的顺序；要稳定排序需在 ORDER BY 后追加唯一列（如 id）。
追问：取 Top3 用哪个？应对：业务上"每个部门取工资前 3 的员工"通常用 ROW_NUMBER，避免并列导致取多。

题目 4：什么是窗口帧 FRAME，默认帧是什么

考点

帧是分区内进一步限定的行范围
带 ORDER BY 与不带 ORDER BY 默认帧不同
累计求和、移动平均都依赖帧

参考答案

1. 帧（FRAME）：在一个分区内，再框定一个"从哪一行到哪一行"的范围，聚合函数只对这个范围内的行计算。

2. 默认帧规则（MySQL 8.0）：

若 OVER 里没有 ORDER BY：默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即整个分区。
若 OVER 里有 ORDER BY：默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从分区第一行到当前行。

3. 这个默认行为是巨坑：很多人以为 SUM(salary) OVER(PARTITION BY dept ORDER BY hire_date) 是部门总和，其实它是"从部门最早入职到当前行的累计和"。

-- 带 ORDER BY：默认累计帧，每行结果都不一样
SELECT name, hire_date, salary,
       SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS cum_sum
FROM emp;
-- 若想要部门总和，必须显式指定帧到分区末尾
SELECT name,
       SUM(salary) OVER (PARTITION BY dept
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS dept_sum
FROM emp;

追问

追问：为什么要这么设计默认帧？应对：累计求和、移动平均是最常见的需求，这样默认行为最省事。
追问：不带 ORDER BY 时写帧会怎样？应对：MySQL 会报错，帧子句必须配合 ORDER BY 使用。

题目 5：ROWS BETWEEN 与 RANGE BETWEEN 有什么区别

考点

ROWS 按物理行偏移，RANGE 按值范围
RANGE 对同值行会一起纳入窗口
MySQL 8.0 RANGE 只支持 UNBOUNDED/CURRENT ROW 边界

参考答案

1. ROWS BETWEEN：按物理行号偏移，例如 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 就是"上一行到下一行共 3 行"。

2. RANGE BETWEEN：按 ORDER BY 列的值范围偏移，所有与当前行 ORDER BY 值相等的行都会被视为"当前行"一起纳入。

3. 举例：ORDER BY 列是 (10, 20, 20, 30)，当前行是第二个 20：

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW：只取前一行 + 当前行（2 行）。
RANGE BETWEEN CURRENT ROW AND CURRENT ROW：取所有值=20 的行（2 行）。

4. MySQL 限制：MySQL 8.0 的 RANGE 只支持 UNBOUNDED PRECEDING、CURRENT ROW、UNBOUNDED FOLLOWING 三种边界，不支持 N PRECEDING 这种数值偏移（Oracle 才支持）。

追问

追问：求移动平均用哪个？应对：用 ROWS，明确"前 3 行到当前行"，避免同值行干扰。
追问：RANGE 和 ROWS 哪个快？应对：ROWS 更直观、优化器处理更稳定；RANGE 在有重复值时语义更复杂。

题目 6：手写"每个部门工资 Top3"的 SQL

考点

ROW_NUMBER 配合 PARTITION BY
外层过滤 rn <= 3
子查询/CTE 包裹

参考答案

-- 环境：MySQL 8.0，emp 表含 name, dept, salary
WITH ranked AS (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM emp
)
SELECT name, dept, salary, rn
FROM ranked
WHERE rn <= 3
ORDER BY dept, rn;

1. 先用 PARTITION BY dept 把员工按部门分组。

2. 在部门内部按 salary DESC 排序并编号。

3. 外层 WHERE rn <= 3 过滤出每个部门前 3 名。

4. 如果并列也要保留，把 ROW_NUMBER 换成 DENSE_RANK。

追问

追问：不用窗口函数怎么写？应对：用相关子查询 WHERE (SELECT COUNT(*) FROM emp e2 WHERE e2.dept=e1.dept AND e2.salary > e1.salary) < 3，数据量大时性能差。
追问：要取"倒数第 3"怎么办？应对：ORDER BY salary ASC。

题目 7：LAG 与 LEAD 怎么用，典型业务场景是什么

考点

LAG 取分区内前 N 行，LEAD 取后 N 行
不用自连接就能拿到上一行数据
典型场景：环比、涨跌、与上一次登录间隔

参考答案

-- 按月统计销售额，并计算与上月相比的环比
SELECT ym, amount,
       LAG(amount, 1) OVER (ORDER BY ym)  AS prev_month,   -- 上月销售额
       LEAD(amount, 1) OVER (ORDER BY ym) AS next_month     -- 下月销售额
FROM monthly_sales;

1. LAG(col, n)：取分区内当前行往前第 n 行的 col 值，没有则返回 NULL。

2. LEAD(col, n)：取分区内当前行往后第 n 行的 col 值。

3. 第三个参数可以指定默认值：LAG(amount, 1, 0) 表示取不到时返回 0。

4. 典型场景：环比/同比、股票每日涨跌、用户两次登录间隔、连续登录判断。

追问

追问：LAG 不指定 PARTITION BY 会怎样？应对：整个结果集是一个分区，相当于全局按 ORDER BY 取上一行；多部门数据混在一起就错了。
追问：求"两次登录之间隔了几天"？应对：DATEDIFF(login_date, LAG(login_date) OVER(PARTITION BY user_id ORDER BY login_date))。

题目 8：如何用窗口函数计算累计求和与同比环比

考点

累计求和依赖默认帧
环比用 LAG(...,1)，同比用 LAG(...,12)
注意 NULL 与除零

参考答案

-- 累计求和 + 环比增长率
SELECT ym, amount,
       SUM(amount) OVER (ORDER BY ym
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount,
       LAG(amount, 1) OVER (ORDER BY ym) AS prev_amt,
       ROUND( (amount - LAG(amount,1) OVER (ORDER BY ym))
              / NULLIF(LAG(amount,1) OVER (ORDER BY ym),0) * 100, 2) AS mom_rate
FROM monthly_sales
ORDER BY ym;

1. 累计求和：SUM(amount) OVER(ORDER BY ym ROWS UNBOUNDED PRECEDING TO CURRENT ROW)。

2. 环比（月环比）：本月 vs 上月，用 LAG(amount,1)。

3. 同比（年同比）：本月 vs 去年同月，用 LAG(amount,12)。

4. NULLIF：防止上月为 0 时除零报错。

追问

追问：每年都要从 0 开始累计怎么办？应对：PARTITION BY YEAR(ym)，每年重新累计。
追问：环比增长率第一行是 NULL 怎么处理？应对：外层用 COALESCE 包成 0 或空字符串。

题目 9：NTILE 的作用与常见坑是什么

考点

把分区内的行均分成 N 组
不能整除时前面的组多分一行
不能指定负数或 0

参考答案

1. 作用：NTILE(n) 把分区内的行尽量平均分成 n 组，返回组号（1~n），常用于"把用户按消费金额分成高/中/低三档"。

2. 不能整除时：余数从第 1 组开始每组多 1 行。例如 10 行分 3 组，结果是 4,3,3 行，而不是 3,3,4。

3. 常见坑：很多人以为 NTILE 是"按值范围分桶"，其实它只按行数切分，不关心值的分布；要按值分桶得用 CASE WHEN 或 PERCENT_RANK。

-- 把每个部门员工按工资从高到低分成 4 档
SELECT name, dept, salary,
       NTILE(4) OVER (PARTITION BY dept ORDER BY salary DESC) AS quartile
FROM emp;

追问

追问：要按工资金额范围分 3 档（>1万、5千~1万、<5千）怎么办？应对：直接用 CASE WHEN，不要用 NTILE。
追问：NTILE(100) 是百分位吗？应对：不是，它按行数百分位，不是值百分位；要值百分位用 PERCENT_RANK 或 CUME_DIST。

题目 10：FIRST_VALUE、LAST_VALUE、NTH_VALUE 怎么用

考点

取帧内第一行/最后一行/第 N 行的值
LAST_VALUE 默认帧下常踩坑
配合显式帧使用

参考答案

-- 每个部门：取最高工资、当前工资、当前行之前的最低工资
SELECT name, dept, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_sal,
       LAST_VALUE(salary)  OVER (PARTITION BY dept ORDER BY salary DESC
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_sal,
       NTH_VALUE(salary, 2) OVER (PARTITION BY dept ORDER BY salary DESC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_sal
FROM emp;

1. FIRST_VALUE：取帧内第一行的值。

2. LAST_VALUE：取帧内最后一行的值。坑：默认帧是"到当前行"，所以 LAST_VALUE 默认就是当前行自己，几乎没用；要拿分区最后一行必须显式扩帧到 UNBOUNDED FOLLOWING。

3. NTH_VALUE(col, n)：取帧内第 n 行的值。

追问

追问：为什么 LAST_VALUE 默认是当前行？应对：和 SUM 累计一样的设计哲学——默认帧到当前行，符合累计语义。
追问：要拿全表最高工资用什么？应对：MAX(salary) OVER() 更简单，不必用 FIRST_VALUE。

题目 11：窗口函数能否写在 WHERE 里过滤结果

考点

执行顺序：WHERE < GROUP BY < HAVING < 窗口函数 < ORDER BY
必须包子查询或 CTE 过滤
别名也不能在 WHERE 中用

参考答案

1. 不能。SQL 逻辑执行顺序中，窗口函数在 WHERE/GROUP BY/HAVING 之后才执行，因此 WHERE 看不到窗口函数的结果。

2. 正确做法是把窗口查询包成子查询或 CTE，外层再过滤：

-- 错误：WHERE 里直接用 ROW_NUMBER
-- SELECT * FROM emp WHERE ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) <= 3;

-- 正确：包一层 CTE
WITH t AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM emp
)
SELECT * FROM t WHERE rn <= 3;

3. 同理，SELECT 里定义的窗口别名也不能在 WHERE 中直接引用（MySQL 8.0 支持 HAVING 引用别名，但 WHERE 不行）。

追问

追问：HAVING 里能引用窗口函数别名吗？应对：MySQL 8.0 允许 HAVING 引用 SELECT 别名，但不允许直接在 HAVING 写窗口函数调用。
追问：ORDER BY 里能用窗口函数吗？应对：可以，ORDER BY 在窗口函数之后执行。

题目 12：窗口函数与相关子查询、自连接相比性能如何

考点

窗口函数通常只扫一遍表+排序
相关子查询是 O(N²)
自连接容易产生笛卡尔积

参考答案

1. 窗口函数：优化器一般做一次扫描 + 按 PARTITION BY 排序，复杂度近似 O(N log N)，内存够时在内存中完成。

2. 相关子查询：外层每一行都触发一次内层查询，百万行级就是百万次子查询，极慢。

3. 自连接：要小心 JOIN 条件，写不好就是笛卡尔积。

4. 结论：同一份"分组 TopN"需求，窗口函数通常比相关子查询快几倍到几十倍，且 SQL 更短更易读。

-- 性能对比思路：给 emp(dept, salary) 建联合索引
-- EXPLAIN 看窗口函数是否出现 Using filesort；若数据量大考虑加索引
EXPLAIN SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
FROM emp;

追问

追问：窗口函数一定比自连接快吗？应对：数据量小差异不明显；数据量大且有合适索引时窗口函数优势明显，但窗口函数本身仍可能产生 Using temporary;Using filesort。
追问：如何优化窗口函数？应对：让 PARTITION BY + ORDER BY 的列顺序与联合索引一致，避免 filesort。

题目 13：COUNT() OVER() 与 COUNT() GROUP BY 的区别

考点

COUNT(*) OVER() 保留明细并附总行数
GROUP BY 收缩行数
常用于分页/占比计算

参考答案

1. COUNT(*) OVER()：每行都附一个"全表总行数"，保留所有明细行。

2. COUNT(*) GROUP BY dept：每个部门只剩一行，是部门人数。

3. 典型用法：

-- 查询每个员工及其在全公司的占比
SELECT name, salary,
       salary / SUM(salary) OVER () AS salary_ratio
FROM emp;

-- 配合 LIMIT 分页时拿到总行数
SELECT SQL_CALC_FOUND_ROWS * FROM emp LIMIT 10;  -- 旧写法，已废弃
-- 推荐：
SELECT *, COUNT(*) OVER () AS total_rows FROM emp LIMIT 10;

追问

追问：SQL_CALC_FOUND_ROWS 为什么废弃？应对：MySQL 8.0.17 起标记废弃，性能差，推荐用窗口函数 COUNT(*) OVER()。
追问：OVER() 空括号就是全表吗？应对：是，不分区不排序，整个结果集作为一个窗口。

题目 14：多个窗口函数能否共用一个窗口定义（WINDOW 子句）

考点

WINDOW 子句给窗口命名
多个函数复用同一窗口减少重复
支持在 OVER 里再扩展

参考答案

-- 用 WINDOW 子句定义一次 w，多个函数复用
SELECT name, dept, salary,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rk,
       SUM(salary)  OVER w AS dept_sum
FROM emp
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

1. WINDOW w AS (...)：定义一个名为 w 的窗口。

2. OVER w：复用该窗口，避免重复写 PARTITION BY/ORDER BY。

3. 还能扩展：OVER (w ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 在命名窗口基础上加帧。

追问

追问：WINDOW 子句写在哪个位置？应对：FROM/JOIN/WHERE/GROUP BY/HAVING 之后，ORDER BY 之前。
追问：多个窗口函数 PARTITION BY 不同怎么办？应对：分别定义多个命名窗口。

题目 15：MySQL 8.0 之前没有窗口函数，如何实现分组 TopN

考点

5.7 时代的经典写法
变量模拟 ROW_NUMBER
相关子查询计数

参考答案

-- MySQL 5.7 经典写法：用用户变量模拟行号
SELECT name, dept, salary, rn
FROM (
  SELECT name, dept, salary,
         @rn := IF(@prev = dept, @rn + 1, 1) AS rn,
         @prev := dept AS prev
  FROM emp, (SELECT @rn := 0, @prev := '') AS init
  ORDER BY dept, salary DESC
) t
WHERE rn <= 3;

1. 变量法：按 dept, salary DESC 排序后，用 @prev 记录上一行部门，部门相同就 rn+1，不同就重置为 1。

2. 相关子查询法：WHERE (SELECT COUNT(*) FROM emp e2 WHERE e2.dept=e1.dept AND e2.salary > e1.salary) < 3。

3. 坑：变量法依赖 ORDER BY 的稳定性，MySQL 不保证 WHERE 过滤前按顺序赋值，8.0 已废弃这种写法的语义保证。

追问

追问：为什么 8.0 要抛弃变量模拟？应对：优化器可能重排执行顺序，导致变量赋值结果不确定，窗口函数是官方标准方案。
追问：升级到 8.0 后要做什么？应对：把旧变量 TopN 改写为 ROW_NUMBER() OVER，可读性和稳定性都更好。

题目 16：窗口函数对索引的利用情况如何，会不会全表扫描

考点

索引能避免 filesort
PARTITION BY + ORDER BY 与联合索引列顺序匹配
数据量大时考虑物化

参考答案

1. 理想情况：如果有联合索引 (dept, salary DESC)，PARTITION BY dept ORDER BY salary DESC 可以直接利用索引顺序，避免 filesort。

2. 不理想情况：索引列顺序与窗口定义不一致，或 SELECT 了索引不覆盖的列，优化器仍会走全表扫描 + 临时表排序。

3. EXPLAIN 关注：Extra 列出现 Using temporary; Using filesort 说明窗口函数在内存/磁盘排序，数据量大时要警惕。

4. 优化思路：让窗口定义的列顺序与联合索引一致；只 SELECT 需要的列；必要时先 WHERE 过滤缩小数据集再开窗。

追问

追问：Using temporary 一定是坏事吗？应对：数据量小时无所谓；几千万行就会爆 tmp_table_size，要调优或改写 SQL。
追问：能不能对窗口函数结果建索引？应对：不能直接对结果建索引，但可以把结果物化成汇总表再加索引。

题目 17：如何用窗口函数找出连续登录 N 天的用户

考点

经典"孤岛问题"（Island Problem）
日期减去行号 = 分组标记
GROUP BY HAVING COUNT >= N

参考答案

-- 找出连续登录 3 天及以上的用户
WITH login_distinct AS (
  -- 先去重：一个用户一天可能登录多次
  SELECT DISTINCT user_id, login_date FROM user_login
),
flagged AS (
  SELECT user_id, login_date,
         -- 关键：登录日期减去按日期排的行号，连续日期会得到相同的组号
         DATE_SUB(login_date,
                  INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM login_distinct
)
SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days
FROM flagged
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

1. 核心思想：连续日期减去各自的行号，差值相同说明原本是连续的。

2. 先对用户登录去重，再按用户分区、日期排序编号。

3. 按 user_id + grp 分组，每组就是一段连续登录区间；HAVING COUNT >= N 过滤。

追问

追问：为什么要先 DISTINCT？应对：同一用户一天登录多次不去重会导致行号错位。
追问：连续 3 周、连续 3 个月怎么算？应对：把日期换成周（YEARWEEK）或月（DATE_FORMAT）再做同样的减法。

题目 18：SUM() OVER(PARTITION BY ... ORDER BY ...) 不加帧会怎样

考点

默认累计帧行为
与"全分区求和"的差异
显式扩帧的写法

参考答案

1. 不加帧时，因为写了 ORDER BY，默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，结果是累计和，每行都不同。

2. 如果业务想要"整个部门的总和"，必须显式扩帧：

-- 错误理解：以为这是部门总和，其实是按入职日期的累计和
SELECT name, salary,
       SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS maybe_dept_sum
FROM emp;

-- 正确：扩帧到整个分区
SELECT name, salary,
       SUM(salary) OVER (PARTITION BY dept
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS dept_sum
FROM emp;

3. 这是面试中最容易翻车的点：很多人写 SQL 跑出来数字对不上，就是栽在默认帧上。

追问

追问：AVG 也有同样问题吗？应对：有，带 ORDER BY 时 AVG 默认也是累计平均。
追问：COUNT(*) OVER(PARTITION BY dept ORDER BY x) 呢？应对：默认累计 COUNT，不是部门人数；要部门人数加 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

题目 19：窗口函数在 EXPLAIN 里看什么，Using temporary 是什么意思

考点

Extra 字段含义
临时表与 filesort
与索引配合的判断

参考答案

1. Extra 出现 `Using temporary`：MySQL 为窗口函数/分组建了内部临时表来分区。

2. Extra 出现 `Using filesort`：分区内排序无法用索引完成，需要额外排序。

3. 出现 `Using where`：窗口计算前先经过 WHERE 过滤，是好事。

4. 优化判断：

如果 rows 行数很大且有 Using temporary;Using filesort，要考虑加 (PARTITION 列, ORDER BY 列) 联合索引。
如果 WHERE 过滤后只剩几百行，临时表在内存里，无需优化。

EXPLAIN
SELECT user_id, dt, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY dt) cum
FROM orders
WHERE dt >= '2026-01-01';

追问

追问：临时表在内存还是磁盘？应对：看 tmp_table_size/max_heap_table_size，超过会落盘，性能骤降。
追问：能不能避免临时表？应对：让 PARTITION BY + ORDER BY 列顺序与联合索引完全一致，优化器可直接用索引顺序。

题目 20：常见坑与注意事项

考点

窗口函数高频踩坑汇总
与其他子句的执行顺序
数据类型与 NULL

参考答案

1. 坑 1：LAST_VALUE 默认是当前行。必须显式 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 才能拿到分区末行。

2. 坑 2：带 ORDER BY 的 SUM/AVG 默认是累计值，不是全分区聚合；要全分区聚合必须扩帧。

3. 坑 3：在 WHERE 里用窗口函数报语法错误，必须包子查询/CTE。

4. 坑 4：ORDER BY 列有重复值时 ROW_NUMBER 顺序不稳定，面试写答案时要追加唯一列兜底。

5. 坑 5：NTILE 按行数切分不是按值切分，想按值分档用 CASE WHEN。

6. 坑 6：MySQL 8.0 的 RANGE 帧不支持 N PRECEDING 数值偏移，Oracle 写法搬过来会报错。

7. 坑 7：窗口函数别名不能在同层 WHERE 用，但可以在外层 WHERE 用。

8. 坑 8：DISTINCT 与窗口函数混用时语义容易乱，先 DISTINCT 再开窗更安全。

追问

追问：窗口函数里能用 DISTINCT 吗？应对：MySQL 8.0 不支持在窗口聚合函数里加 DISTINCT（如 SUM(DISTINCT x) OVER），要去重请在子查询里先做。
追问：窗口函数能嵌套吗？应对：不能在一个窗口函数里再写另一个窗口函数，必须包子查询。

速查表：窗口函数速记

| 函数 | 作用 | 典型场景 | 是否必须 ORDER BY |

|------|------|----------|-------------------|

| SUM/AVG/COUNT OVER | 聚合 | 累计、占比 | 视需求 |

| ROWS/RANGE | 帧边界 | 移动平均 | 是 |

| 默认帧场景 | 默认帧范围 | 结果语义 |

|-----------|-----------|----------|

| OVER() 无 ORDER BY | 整个分区 | 全分区聚合 |

| OVER(... ORDER BY x) | 分区首行到当前行 | 累计聚合 |

复习清单

| 题目 | 核心结论 | 易错点 |

|------|----------|--------|

| 1 | 窗口函数不收缩行，GROUP BY 收缩行 | 把窗口函数当 GROUP BY 用 |

| 2 | OVER 内可写 PARTITION BY/ORDER BY/帧 | 忘记帧必须配 ORDER BY |

| 3 | ROW_NUMBER 不并列、RANK 跳号、DENSE_RANK 不跳号 | 业务 TopN 选错函数 |

| 4 | 带 ORDER BY 默认累计帧 | 以为 SUM OVER 是全分区和 |

| 5 | ROWS 按行、RANGE 按值 | MySQL RANGE 不支持 N PRECEDING |

| 6 | CTE + ROW_NUMBER + WHERE rn<=3 | 在 WHERE 里直接写窗口函数 |

| 7 | LAG 取前 n 行，LEAD 取后 n 行 | 多业务表忘记 PARTITION BY |

| 8 | 累计求和 + LAG 做环比同比 | 除零未用 NULLIF |

| 9 | NTILE 按行数均分 | 误以为按值分桶 |

| 10 | LAST_VALUE 默认帧只到当前行 | 不扩帧拿不到分区末行 |

| 11 | 窗口函数在 WHERE 之后执行 | 在 WHERE 直接过滤窗口结果 |

| 12 | 窗口函数 O(N log N) 优于相关子查询 | 小数据量下过早优化 |

| 13 | COUNT(*) OVER() 附总行数 | 替代 SQL_CALC_FOUND_ROWS |

| 14 | WINDOW 子句复用窗口定义 | 多个函数重复写 PARTITION BY |

| 15 | 5.7 用变量模拟行号 | 变量法依赖排序稳定性 |

| 16 | 联合索引列顺序匹配窗口定义 | 出现 Using filesort 不优化 |

| 17 | 日期减行号 = 连续段标记 | 忘记先对登录去重 |

| 18 | 带 ORDER BY 的 SUM 是累计和 | 想要全分区和却不扩帧 |

| 19 | Extra 看 temporary/filesort | 临时表落盘不监控 |

| 20 | LAST_VALUE/默认帧/WHERE 顺序三大坑 | 在 WHERE 用窗口别名 |

练习题

1. 题目：给出订单表 orders(order_id, user_id, amount, create_time)，写 SQL 求每个用户订单金额 Top3 的订单。

参考答案：

   WITH t AS (
     SELECT order_id, user_id, amount,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
     FROM orders
   )
   SELECT * FROM t WHERE rn <= 3;

2. 题目：求每个用户截至当前订单的累计消费金额。

参考答案：

   SELECT order_id, user_id, amount, create_time,
          SUM(amount) OVER (PARTITION BY user_id ORDER BY create_time
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount
   FROM orders;

3. 题目：求每个用户与上一单的金额差额。

参考答案：

   SELECT order_id, user_id, amount,
          amount - LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY create_time) AS diff
   FROM orders;

4. 题目：为什么 SUM(salary) OVER(PARTITION BY dept ORDER BY hire_date) 不是部门工资总和？怎么改？

参考答案：因为带 ORDER BY 时默认帧是"分区首行到当前行"，是累计和。要部门总和需显式 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

5. 题目：RANK() 和 DENSE_RANK() 在分数 (90,90,80) 下分别返回什么？

参考答案：RANK 返回 (1,1,3)，DENSE_RANK 返回 (1,1,2)。

下一篇预告：第 12 章聚合与分组面试精讲

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 11 章 窗口函数面试精讲

目录

题目 1：窗口函数与普通 GROUP BY 聚合有什么区别

考点

参考答案

追问

题目 2：OVER() 括号里可以写哪些子句，各自作用是什么

考点

参考答案

追问

题目 3：ROW_NUMBER、RANK、DENSE_RANK 三者区别，举个例子

考点

参考答案

追问

题目 4：什么是窗口帧 FRAME，默认帧是什么

考点

参考答案

追问

题目 5：ROWS BETWEEN 与 RANGE BETWEEN 有什么区别

考点

参考答案

追问

题目 6：手写"每个部门工资 Top3"的 SQL

考点

参考答案

追问

题目 7：LAG 与 LEAD 怎么用，典型业务场景是什么

考点

参考答案

追问

题目 8：如何用窗口函数计算累计求和与同比环比

考点

参考答案

追问

题目 9：NTILE 的作用与常见坑是什么

考点

参考答案

追问

题目 10：FIRST_VALUE、LAST_VALUE、NTH_VALUE 怎么用

考点

参考答案

追问

题目 11：窗口函数能否写在 WHERE 里过滤结果

考点

参考答案

追问

题目 12：窗口函数与相关子查询、自连接相比性能如何

考点

参考答案

追问

题目 13：COUNT(*) OVER() 与 COUNT(*) GROUP BY 的区别

考点

参考答案

追问

题目 14：多个窗口函数能否共用一个窗口定义（WINDOW 子句）

考点

参考答案

追问

题目 15：MySQL 8.0 之前没有窗口函数，如何实现分组 TopN

考点

参考答案

追问

题目 16：窗口函数对索引的利用情况如何，会不会全表扫描

考点

参考答案

追问

题目 17：如何用窗口函数找出连续登录 N 天的用户

考点

参考答案

追问

题目 18：SUM() OVER(PARTITION BY ... ORDER BY ...) 不加帧会怎样

考点

参考答案

追问

题目 19：窗口函数在 EXPLAIN 里看什么，Using temporary 是什么意思

考点

参考答案

追问

题目 20：常见坑与注意事项

考点

第 11 章窗口函数面试精讲

题目 13：COUNT() OVER() 与 COUNT() GROUP BY 的区别