MySQL窗口函数面试全解：组内TopN、连续登录与性能优化

面试聊到SQL，问到后面基本都会落到同一个地方：怎么取"组内前几名"。这个问题看起来简单，但它的答案分水岭非常明显——只会 GROUP BY 的人会卡住，会用窗口函数的人三行写完。窗口函数（Window Function）是 MySQL 8.0 引入的重头戏，也是这几年数据库岗、后端岗、数据分析岗面试里出现频率最高的考点之一，问法从"ROW_NUMBER 和 RANK 有什么区别"到"连续登录7天的用户怎么找"再到"窗口函数的性能你怎么评估"，一层比一层深。

这篇内容我按面试官的真实提问顺序来组织：先把 OVER() 的语法骨架拆干净，再用五类高频真题从建表造数一路写到出结果，然后往下钻到窗口帧、执行顺序、索引与执行计划这些加分项，最后把我自己踩过的坑和常见追问整理成速查表。文中所有 SQL 都可以直接复制到本地 8.0 环境跑，涉及参数和版本差异的地方我会明确标出来。不管你是在准备面试，还是要在生产里把一段跑不动的自连接改成窗口函数，应该都能直接抄作业。

1. 窗口函数为什么会成为面试分水岭

1.1 一道真题：怎么把"组内前N"写对

我拿这道题做过很多次对照实验：一张员工薪资表，取每个部门薪资最高的前 2 名。用传统写法，标准答案一般是自连接或者相关子查询，代码长、可读性差，而且数据量一上来就崩。用窗口函数，主体就一段子查询加一个rn <= 2。

为什么面试官偏爱这种题？因为它同时考察三件事：你知不知道窗口函数存在、你分不分得清三种排名函数的差异、你懂不懂"窗口函数不能写在 WHERE 里"这个硬性限制。第三点特别关键，很多人第一次写就会写成下面这样：

-- 错误写法：窗口函数不能出现在 WHERE 中 SELECT dept_id, emp_name, salary FROM emp_salary WHERE ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) <= 2;

MySQL 会直接报语法错误。原因稍后在第 4 章讲执行顺序时会说透，这里先记住结论：窗口函数的计算发生在 WHERE 之后，所以它没法反过来给 WHERE 当过滤条件，必须套一层子查询或者 CTE，把排名结果物化成普通列，外层再过滤。

1.2 窗口函数与GROUP BY的本质差异

很多人把窗口函数理解成"加强版 GROUP BY"，这个类比方向对，但会误导。两者最本质的区别是：GROUP BY 会把多行压成一行，窗口函数不会改变行数。窗口函数是在每一行上"侧着看一眼"它所属的那一组，把聚合结果贴回原来的行上。

举个具体例子。同样是算部门总薪资：

• GROUP BY dept_id之后，一个部门只剩一行，你拿不到每个员工的明细。
• SUM(salary) OVER (PARTITION BY dept_id)之后，每个员工那一行都多了一列"本部门总薪资"，明细一行没少。

这个差异直接决定了适用场景。要做报表汇总、按维度降维，用 GROUP BY；要做明细+汇总同屏、组内排名、组内占比、环比同比，窗口函数才是正解。面试里如果被问"什么时候不该用窗口函数"，一个稳妥的回答是：结果集本身就需要降维的时候，硬上窗口函数等于先展开再聚合，纯属浪费。

还有个容易被忽略的点：窗口函数和 GROUP BY 是可以同时出现在一条 SQL 里的。看下面这段：

SELECT dept_id, SUM(salary) AS dept_total, ROUND(SUM(salary) / SUM(SUM(salary)) OVER () * 100, 2) AS pct_of_all FROM emp_salary GROUP BY dept_id;

SUM(SUM(salary)) OVER ()这层嵌套看着别扭，但逻辑很干净：内层SUM(salary)是分组聚合的结果，外层再把它当成一个普通表达式做整窗求和。这条正是热搜里"sql group 窗口函数"想问的东西——窗口函数作用在聚合之后的结果集上，而不是原始表上。理解这一层，很多"聚合函数套窗口函数"的写法就不神秘了。

1.3 先把环境对齐：确认你的版本支持窗口函数

动手之前先确认版本，这是最容易被跳过、也最容易白折腾的一步。窗口函数是 MySQL 8.0.2 引入的，5.7 及以前完全没有。不同版本的行为差异也实打实存在，比如EXPLAIN ANALYZE要 8.0.18 才有，GROUPS帧类型虽然 8.0 一开始就支持，但早期小版本 bug 相对多。生产上建议至少 8.0.30+，新项目直接上 8.4 LTS。

一句命令确认：

SELECT VERSION();

想搭个干净的练习环境，用容器是最省事的，不会污染本机已经装好的实例：

docker run -d --name mysql8-lab \ -e MYSQL_ROOT_PASSWORD=Root_1234 \ -p 13306:3306 \ -v ~/mysql-lab/data:/var/lib/mysql \ mysql:8.0 \ --character-set-server=utf8mb4 \ --collation-server=utf8mb4_0900_ai_ci

这里有两个我踩过的坑。第一，端口别直接用 3306，本机如果已经有 MySQL 在跑会冲突，映射成 13306 更稳。第二，-v挂载目录一定要先在宿主机建好，权限给足，否则容器起来后初始化会失败，docker logs里一堆权限报错。连上去之后，客户端用命令行、MySQL Workbench、DBeaver 都行，我个人练手习惯用命令行，因为能清楚看到每一列的对齐关系，跑窗口函数时尤其有用。

提示：如果你手上只有 5.7 环境，窗口函数的题目照样能练，但要换写法（用户变量或自连接），具体在第 5 章给对照方案。不过要注意 8.0 里用户变量赋值在 SELECT 中的求值顺序不再保证，5.7 时代那种一行搞定的排名写法在 8.0 上不可靠。

2. 语法骨架拆解：OVER()里到底放了什么

2.1 三个子句：PARTITION BY、ORDER BY、帧

窗口函数的完整语法长这样：

函数名(...) OVER ( PARTITION BY 分区列 ORDER BY 排序列 帧定义 )

三个部分各管一件事，缺省行为差别很大。

PARTITION BY决定"分组边界"，等价于 GROUP BY 的分组，但它不合并行。不写 PARTITION BY 时，整张结果集就是一个大窗口，这点常被用来算全局占比、全局排名。

ORDER BY决定窗口内的顺序。这是最容易被低估的一处：只要你在 OVER() 里写了 ORDER BY，默认帧就从"整个分区"变成"从分区第一行到当前行"。这意味着SUM(x) OVER (ORDER BY d)算出来的不是总和，而是累计和。很多人第一次写累计求和成功了，第二次只想算总和却忘了去掉 ORDER BY，结果拿到一列递增的数字，排查半天。

帧（Frame）决定"当前行往前看多少、往后看多少"。它只在有 ORDER BY 的时候才真正有意义。完整的帧语法：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW

三种帧类型的细节在第 4 章展开，这里先建立一个直觉：ROWS数的是物理行数，RANGE数的是排序值，GROUPS数的是"并列值的组数"。

另外提一个让代码变干净的写法——命名窗口：

SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER w AS rn, RANK() OVER w AS rk, SUM(salary) OVER w AS dept_salary_sum FROM emp_salary WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);

一条 SQL 里反复写同样的 OVER() 内容，既啰嗦又容易改漏，WINDOW 名字 AS (...)提出来之后复用，可作为面试时的加分细节。

2.2 排名类函数：ROW_NUMBER、RANK、DENSE_RANK、NTILE

这四个是面试出现率最高的，尤其是前三个的区别，几乎每场必问。用一句话概括：ROW_NUMBER 不管并列、RANK 跳跃、DENSE_RANK 不跳跃。

拿前面部门1的数据看（张伟 28000、李娜 32000、王强 32000、赵敏 21000），按薪资降序：

关键点在于：两行并列第一之后，RANK 直接跳到 3，DENSE_RANK 还是 2。这个差异在实际业务里是有语义的：取"薪资排名前三的员工"，如果并列第二名也算，那用 DENSE_RANK 会多出来人；如果要严格只取 3 个人，必须用 ROW_NUMBER。

ROW_NUMBER 有个隐藏陷阱——它是不确定的。上面李娜和王强薪资一样，谁拿 1 谁拿 2 完全取决于存储引擎返回的顺序，同一台机器上多跑几次、加了索引之后重建，结果可能就变了。生产上如果要把这个结果落库或者做对账，务必在 OVER() 的 ORDER BY 里加一个唯一列做兜底，比如ORDER BY salary DESC, id ASC。这个细节我在一次数据对账里吃过亏，两边系统用的都是 ROW_NUMBER，数据一样，跑出来的"第一名"却不是同一个人，查了大半天。

NTILE(n)是把分区均分成 n 桶，用于分位数分析。它也有个坑：如果行数除不尽，前面的桶会比后面的多一行，比如 7 行分 3 桶是 3、2、2 而不是 3、3、1。做等频分箱的时候要知道这一点，否则分布会略微倾斜。

还有两个偏统计的：PERCENT_RANK()返回(rank - 1) / (总行数 - 1)，CUME_DIST()返回"小于等于当前值的行数占比"。做用户分层、绩点百分位的时候有用，面试问到的频率不高，但能说出来会显得体系比较完整。

2.3 取值类函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE

这一族是解决"跨行取值"的，环比同比、和上一次比较、取首尾值全靠它们。

LAG(expr, offset, default)取当前行往前第 offset 行的值，LEAD是往后。三个参数里，第三个默认值参数千万别省。不写的话，第一行取上月数据会返回 NULL，然后你拿 NULL 去做除法或者加减，整列结果全变 NULL。写LAG(amount, 1, 0)至少保证算出来是个数。

SELECT month_str, amount, LAG(amount, 1, NULL) OVER (ORDER BY month_str) AS prev_amount, LEAD(amount, 1) OVER (ORDER BY month_str) AS next_amount FROM monthly_sales;

FIRST_VALUE/LAST_VALUE/NTH_VALUE取窗口帧内的第一个、最后一个、第 n 个值。这里面LAST_VALUE 有一个经典陷阱，值得单独说。

-- 陷阱写法：得到的是当前行的值，不是分区最后一行的值 SELECT user_id, order_date, amount, LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS last_amt FROM order_detail;

因为默认帧是"从分区首行到当前行"，当前行恰好就是这个帧的最后一行，所以 LAST_VALUE 返回的永远是它自己。要取真正的末值，必须把帧显式撑到分区末尾：

SELECT user_id, order_date, amount, FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_amt, LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amt FROM order_detail;

同一个原因，FIRST_VALUE一般「碰巧」是对的（第一行永远是首行），而LAST_VALUE十有八九是错的。这个不对称性非常坑，我在面试里问过几次，能主动提出来的人不到三成。

注意：窗口函数不能嵌套。SUM(ROW_NUMBER() OVER (...)) OVER (...)这种写法直接报错。要嵌套就先落成子查询或 CTE，外层再开一个窗口。

2.4 聚合函数的"窗口化"：与GROUP BY配合使用的正确姿势

SUM、AVG、COUNT、MAX、MIN这五个聚合函数加上 OVER() 就变成了窗口聚合，这是最实用的一类，也是面试里最容易出彩的。

按用途分，窗口聚合常见三种形态：

整窗聚合——不带 ORDER BY，返回整个分区的值，用于算占比、和组内均值比较：

SELECT order_id, user_id, amount, ROUND(amount / SUM(amount) OVER (PARTITION BY user_id) * 100, 2) AS pct FROM order_detail;

累计聚合——带 ORDER BY 不写帧，默认就是累计：

SELECT user_id, order_date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total FROM order_detail;

滑动聚合——显式指定帧，用于移动平均：

SELECT order_date, ROUND(AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7 FROM daily_amount;

这里有个反直觉的地方值得强调：累计聚合和 GROUP BY 的聚合结果看起来一样，但语义完全不同。SUM(x) OVER (PARTITION BY u ORDER BY d)算的是"截止当前行的累计"，它的值随行变化；SUM(x) ... GROUP BY u算的是"该组的总额"，组内所有行是同一个值。面试里被要求"算每个用户截止每天的累计消费"，如果答成 GROUP BY 就彻底跑偏了。

另外一个细节：窗口 COUNT 里的COUNT(*)和COUNT(列名)行为差异和普通聚合一致，前者数行数，后者不数 NULL。做"每个用户第几笔订单"的时候，我更倾向用 ROW_NUMBER 而不是 COUNT，因为 COUNT 遇到重复时间戳会有并列歧义，ROW_NUMBER 加上 ID 兜底后结果唯一。

3. 高频真题实战：从建表造数到拿到结果

3.1 造两张能覆盖80%题型的表

先把练习环境准备好，两张表基本能覆盖面试里绝大多数窗口函数题：一张员工薪资表（考组内排名），一张订单/登录表（考时间序列类的题）。

DROP TABLE IF EXISTS emp_salary; CREATE TABLE emp_salary ( id BIGINT PRIMARY KEY AUTO_INCREMENT, dept_id INT NOT NULL, emp_name VARCHAR(32) NOT NULL, salary DECIMAL(10,2) NOT NULL, hire_date DATE NOT NULL, KEY idx_dept_salary (dept_id, salary) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO emp_salary (dept_id, emp_name, salary, hire_date) VALUES (1,'张伟',28000,'2019-03-11'), (1,'李娜',32000,'2018-07-02'), (1,'王强',32000,'2020-01-15'), (1,'赵敏',21000,'2021-05-20'), (2,'陈磊',45000,'2017-09-01'), (2,'刘洋',26000,'2020-11-03'), (2,'孙宇',19000,'2022-02-18'), (3,'周涛',15000,'2021-08-09'), (3,'吴迪',15000,'2020-06-25'), (3,'郑凯', 9000,'2022-04-01');

DROP TABLE IF EXISTS order_detail; CREATE TABLE order_detail ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_date DATE NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 1, KEY idx_user_date (user_id, order_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

索引不是随便加的。idx_dept_salary (dept_id, salary)的列顺序刻意和PARTITION BY dept_id ORDER BY salary对齐，idx_user_date (user_id, order_date)对齐PARTITION BY user_id ORDER BY order_date。这不是装饰——在第 4 章的性能对比里你会看到，索引顺序匹配时排序开销能明显下降，不匹配时会多出一次 filesort。

再看登录表，连续N天这类题的标配：

DROP TABLE IF EXISTS user_login; CREATE TABLE user_login ( user_id INT NOT NULL, login_date DATE NOT NULL, PRIMARY KEY (user_id, login_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO user_login (user_id, login_date) VALUES (1001,'2024-03-01'),(1001,'2024-03-02'),(1001,'2024-03-03'), (1001,'2024-03-05'),(1001,'2024-03-06'), (1002,'2024-03-01'),(1002,'2024-03-03'),(1002,'2024-03-04'),(1002,'2024-03-05'), (1003,'2024-03-02'),(1003,'2024-03-03');

刻意把用户1001的登录日期做成断开的（3月3日之后跳到3月5日），这样能立刻验证你的连续判断逻辑是不是真的对——很多人写的SQL在连续数据上跑得通，一遇到断点就露馅。

3.2 组内TopN：三种排名函数的取舍

先看最标准的解法，取每个部门薪资前 2 名：

SELECT dept_id, emp_name, salary, rn FROM ( SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id ASC) AS rn FROM emp_salary ) t WHERE rn <= 2 ORDER BY dept_id, rn;

跑出来的结果，部门1是李娜(32000)和王强(32000)，部门2是陈磊和刘洋，部门3是周涛和吴迪。注意部门1两个人薪资相同，谁排第一取决于id ASC这个兜底条件——这就是我前面强调的确定性。

现在换个问法：如果并列都算，比如"取薪资排名前三（含并列）"，那就要用 DENSE_RANK：

SELECT dept_id, emp_name, salary, dr FROM ( SELECT dept_id, emp_name, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr FROM emp_salary ) t WHERE dr <= 2 ORDER BY dept_id, dr, salary DESC;

这里结果会多出来人：部门1的 dr<=2 会返回三个员工（李娜、王强、张伟），因为 DENSE_RANK 把两个并列第一压成一个名次，28000 的张伟就成了第二名。

那 RANK 用在哪？它的语义是"竞赛排名"——两个第一，下一个就是第三。业务上对应的是"跳过名次"的场景，比如绩效强制分布、竞赛榜单。三者怎么选，我一般按这个判断：

实操心得：写完 TopN 之后，一定用SELECT COUNT(*)对一下总数。我遇到过一次 DT 引擎切换导致 ROW_NUMBER 的行数莫名多出几十条，最后定位是上游数据有重复主键。TopN 类需求对重复数据极其敏感，加一步总数校验能省掉后面几个小时的排查。

3.3 连续登录N天：差值分组法

这道题是窗口函数的"进阶门槛"，面试里出现率极高，核心思路是date - row_number 得到分组标记（gap and island）。

原理不难。如果日期是连续的，那么每个日期减去它在该用户内的行号，得到的结果是同一个常量：

连续段内这个差值恒定，一旦日期断开，差值就变了。于是"连续"就转化成了"按差值分组"，标准聚合就能解决。

SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( 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 user_login ) t GROUP BY user_id, grp HAVING COUNT(*) >= 3 ORDER BY user_id, start_date;

跑完你会看到只有用户1001的 03-01 到 03-03 被返回，用户1002那几天是断开的，用户1003只有两天。这个结果正好验证了逻辑的正确性，如果你写的SQL把1002也返回了，多半是漏了 PARTITION BY user_id 或者忘了去重。

注意：如果上游表可能有同一天重复登录的记录（比如加了秒级时间戳），必须先DISTINCT或者先按天聚合再排序，否则 ROW_NUMBER 会把同一天编成两个号，连续段被硬生生拆开，结果全错。这个坑我在线上见过两次，第二次是因为上游改了埋点逻辑，同一天产生了多条记录。

如果问的是"连续N天的起止日期"或者"最大连续天数"，在上面基础上再包一层就行：

SELECT user_id, MAX(continuous_days) AS max_continuous FROM ( SELECT user_id, COUNT(*) AS continuous_days FROM ( 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 user_login ) t GROUP BY user_id, grp ) s GROUP BY user_id;

注意这里出现了"窗口函数 → 子查询 → GROUP BY → 外层 GROUP BY"的三层结构，这是窗口函数题的典型形态。写的时候建议一层一层跑，每层都看一眼中间结果，比一次性写完再调试快得多。

3.4 环比同比与占比：LAG和整窗SUM

时间序列类的题基本绕不开 LAG。先按月聚合，再算环比：

SELECT month_str, amount, LAG(amount, 1) OVER w AS prev_amount, ROUND((amount - LAG(amount, 1) OVER w) / LAG(amount, 1) OVER w * 100, 2) AS mom_pct, LAG(amount, 12) OVER w AS last_year_amount, ROUND((amount - LAG(amount, 12) OVER w) / LAG(amount, 12) OVER w * 100, 2) AS yoy_pct FROM ( SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_str, SUM(amount) AS amount FROM order_detail WHERE status = 1 GROUP BY month_str ) t WINDOW w AS (ORDER BY month_str) ORDER BY month_str;

三个细节值得说。

第一，月份必须先在子查询里聚合。窗口函数作用在聚合结果上，所以SUM(amount)的 GROUP BY 要放在内层。如果直接在外层对明细行算环比，结果就是"每一行相对上一行的变化"，和业务要的"月度环比"完全不是一回事。

第二，用DATE_FORMAT生成月份字符串时要确认排序正确。'2024-03'这种格式字典序和时间序一致，可以直接 ORDER BY；如果写成'2024-3'，字典序就乱了，'2024-10'会排在'2024-2'前面。要么改成补零格式，要么用DATE_FORMAT(order_date,'%Y-%m-01')再转成日期类型。这个坑很隐蔽，数据只有两三个月的时候根本看不出来。

第三，除零风险。prev_amount为 0 或者 NULL 时，mom_pct会变成 NULL 或者报错。稳妥写法是套NULLIF或者CASE WHEN：

ROUND((amount - prev_amount) / NULLIF(prev_amount, 0) * 100, 2) AS mom_pct

占比类的问题更简单，整窗聚合一行搞定：

SELECT dept_id, emp_name, salary, ROUND(salary / SUM(salary) OVER (PARTITION BY dept_id) * 100, 2) AS pct_in_dept, ROUND(salary / SUM(salary) OVER () * 100, 2) AS pct_in_all FROM emp_salary ORDER BY dept_id, salary DESC;

这里OVER ()不带任何参数，表示整个结果集是一个窗口，这就是"全体占比"。很多人误以为必须要写 PARTITION BY，其实不写就是全局窗口。

3.5 累计求和与移动平均：帧的真实作用

累计求和可能是最早让人尝到窗口函数甜头的一类需求：

SELECT user_id, order_date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM order_detail ORDER BY user_id, order_date;

这里我把帧显式写全了。虽然不写帧默认就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，但RANGE和ROWS在同一天有多笔订单时行为不同：RANGE 会把排序值相同的行一起算进来，也就是同一天的所有订单会被视为一行，累计值会"跳"；ROWS才是严格按行累加。做对账、算余额的场景必须用 ROWS，用默认的 RANGE 会得出错误结果。

移动平均是帧最典型的应用，算7日移动平均：

SELECT stat_date, amount, ROUND(AVG(amount) OVER (ORDER BY stat_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7, ROUND(AVG(amount) OVER (ORDER BY stat_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) * 1.0 AS ma7_float FROM daily_amount ORDER BY stat_date;

前 6 天因为窗口不满 7 行，算出来的平均值是用不足 7 天的数据算的。有些业务要求"不满7天不出值"，这时候用COUNT(*) OVER (...)判断一下行数，不够就返回 NULL。这个细节在监控告警类需求里很关键，否则刚开始那几天会给出剧烈波动的均值，触发误告警。

3.6 去重取最新：ROW_NUMBER的另一个主战场

除了排名，ROW_NUMBER 还有一个使用率极高的场景：分组取最新一条记录。比如订单表里一个订单号有多条状态变更记录，要取每个订单最新那条：

SELECT order_no, status, update_time FROM ( SELECT order_no, status, update_time, ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY update_time DESC, id DESC) AS rn FROM order_status_log ) t WHERE rn = 1;

这个写法的价值在于，它在语义上比GROUP BY order_no + MAX(update_time)加自连接要清楚得多，而且不怕时间戳重复。如果时间戳可能重复，用 MIN/MAX 方案会一次返回多条记录，这个 bug 在并发写入的场景里出现频率不低。加上id DESC兜底，永远只返回一条。

如果是要物理删掉重复数据，MySQL 有个额外的限制需要知道：窗口函数不能直接写在 UPDATE 或 DELETE 的 SET/WHERE 里。可行方案是先建临时表存好待删主键，再按主键删除：

CREATE TEMPORARY TABLE tmp_dup_ids AS SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY order_no ORDER BY update_time DESC, id DESC) AS rn FROM order_status_log ) t WHERE rn > 1; DELETE FROM order_status_log WHERE id IN (SELECT id FROM tmp_dup_ids);

提示：这个写法在千万级表上要分批执行，一次删几十万行的做法会把 undo log 撑爆，还可能拖慢主从同步。我一般按 5000 一批循环删，批间隙加个几百毫秒，对线上影响几乎察觉不到。

4. 执行与性能：面试官追问的深水区

4.1 窗口帧的三种类型：ROWS、RANGE、GROUPS

把三种帧类型摊开讲，这是区分"会用"和"懂原理"的分界线。

ROWS以物理行为单位。ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING就是字面意思：前一行、当前行、后一行，一共三行。

RANGE以排序值为单位。RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING表示"排序值在当前值减 1 到加 1 之间的所有行"。注意这里的 1 是数值偏移量，所以ORDER BY 的表达式必须是单个数值或日期类型，多列排序时不能用带偏移量的 RANGE。

GROUPS以并列组为单位。GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW表示当前并列组加上前一个并列组。当排序值有大量重复时，GROUPS 比 ROWS 更符合"按值看"的直觉。

三者放在一起跑一遍最直观：

SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS rows_sum, SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS range_sum, SUM(amount) OVER (ORDER BY order_date GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS groups_sum FROM order_detail;

如果某天有三笔订单，ROWS 只取上下各一行；RANGE 会把"日期在这个区间内"的订单全算进来；GROUPS 则会包含并列组。同一份数据三种结果，选哪个完全取决于业务语义。

还有两个限制值得背下来，面试问到了能加分：

• 带偏移量的 RANGE 帧只能有一个 ORDER BY 表达式，而且要能转换成数值。写RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW这种日期偏移也是支持的，但同样只能有一个排序表达式。
• 帧里不能出现窗口函数或聚合函数，ROWS BETWEEN SUM(x) PRECEDING AND CURRENT ROW直接报错。

4.2 窗口函数在SQL执行顺序中的位置

这个知识点解释了第 1 章那个"为什么不能写在 WHERE 里"的问题。MySQL 8.0 的逻辑执行顺序大致是：

FROM → JOIN → WHERE → GROUP BY → HAVING → WINDOW → SELECT → DISTINCT → ORDER BY → LIMIT

关键点是WINDOW 阶段排在 WHERE、GROUP BY、HAVING 之后，SELECT 之前。由此可以推出几条硬性限制：

第一，窗口函数不能出现在 WHERE、GROUP BY、HAVING、JOIN ON 里。这些阶段执行时，窗口函数的结果还不存在，引擎根本没有东西可以比较。所以才必须套一层子查询，把窗口结果落成普通列，外层 WHERE 才能过滤。

第二，窗口函数作用在 GROUP BY 之后的结果集上，这就是为什么SUM(SUM(salary)) OVER ()是合法的——内层的聚合已经在上一个阶段算完了。

第三，外层 ORDER BY 和内层 OVER() 里的 ORDER BY 是两码事。前者决定最终结果的展示顺序，后者决定窗口内的计算顺序。两者可以完全不同，也可以一致。只写 OVER() 里的 ORDER BY、不写外层 ORDER BY，最终结果的行顺序是不保证的，测试时必须加外层排序，否则两次跑出来的结果顺序可能不一样。

实操心得：调试窗口函数时我习惯先单独跑内层子查询，看每一行的 rn、累计值对不对，确认无误再套外层过滤。一步到位写完直接跑大查询，一旦结果不对就得从头二分排查，效率差好几倍。

4.3 索引能不能帮上忙：EXPLAIN看什么

窗口函数的性能瓶颈通常有两个：排序和结果集物化。索引能不能帮上忙，取决于你的索引列顺序和 PARTITION BY + ORDER BY 的列顺序是否匹配。

还是拿emp_salary举例，索引是(dept_id, salary)：

EXPLAIN FORMAT=TREE SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp_salary;

EXPLAIN FORMAT=TREE是看窗口函数执行计划最直观的方式（8.0 支持），输出里会看到排序和窗口处理相关的节点。对比一下：把 ORDER BY 改成ORDER BY hire_date DESC（hire_date 不在索引里），你会看到多出一个显式的排序步骤，成本估算明显上升。

几个判断依据：

• 如果PARTITION BY的列和ORDER BY的列按顺序构成了某个索引的前缀，扫描时天然有序，排序可以省掉。
• 如果 ORDER BY 用了索引里没有的列，必然多一次 filesort，数据量大的时候这一层就是主要耗时。
• EXPLAIN ANALYZE（8.0.18+）能看到实际执行时间和实际行数，比估算的 rows 可靠得多。EXPLAIN ANALYZE会真正执行 SQL，别在写库上跑没有 LIMIT 的大查询。

另外，窗口函数本质上是需要把整个结果集物化后再处理的，MySQL 在这块会用到临时表，如果结果集特别大，可能会落到磁盘临时表。可以通过SHOW STATUS LIKE 'Created_tmp_disk_tables'观察，落盘了就要考虑先减少参与窗口计算的数据量——在子查询里先把无关列和无关行裁掉，比任何参数调优都有效。

4.4 一份可复现的性能对比

我做过一次对比实验：100 万行订单数据，取每个用户金额最高的前 3 笔订单。硬件是 4 核 8G 的云主机，MySQL 8.0.34，InnoDB，idx_user_amount (user_id, amount)。三种写法各跑三次取中位数：

数量级差异很明显。相关子查询是 O(n²) 的复杂度，100 万行基本不可用；自连接虽然优化器能做不少事，但中间结果会膨胀；窗口函数只需要一次有序扫描。

不过这里有个前提要讲清楚：这个结论成立的条件是索引列顺序匹配。我把索引换成(user_id, order_date)再跑同样的 SQL，窗口函数那版涨到了约 4.5s，因为 order_date 不是金额，ORDER BY amount 没法用索引序，多了一次全量排序。也就是说，窗口函数不是银弹，索引设计跟不上的时候它一样慢。

反过来还有一种情况值得注意：如果分区数很少、每个分区行数极多，窗口函数要一次性物化整个分区，内存压力会比较大。我遇到过一个 case，PARTITION BY tenant_id只分了 3 个租户，每个租户几百万行，结果临时文件写到磁盘，跑了两分多钟。解决办法是在子查询里先按时间范围过滤，把单次参与计算的行数压下来。

5. 踩坑记录与高频追问速查

5.1 报错与结果不对的速查表

下面这些是我和身边同事真实遇到过的问题，按"现象 → 原因 → 处理"整理：

关于最后一条再补一句：SELECT ROW_NUMBER() OVER (ORDER BY d) FROM t ORDER BY d里的两个 ORDER BY 各管各的，前者管编号顺序，后者管展示顺序。它们可以不一致，比如按金额编号但按日期展示，这时候是合法的。

注意：MySQL 8.0 里sql_mode开了ONLY_FULL_GROUP_BY之后，GROUP BY 的列限制比 5.7 严得多，混写 GROUP BY 和窗口函数时更容易报错。遇到"column not in GROUP BY"的提示，先把非聚合列补进 GROUP BY，或者用ANY_VALUE()包一下。

5.2 窗口函数、自连接、子查询怎么选

面试里经常被追问"不用窗口函数能不能做"，这是在考察你对替代方案的掌握。以组内 TopN 为例，5.7 时代的自连接写法：

SELECT a.dept_id, a.emp_name, a.salary FROM emp_salary a LEFT JOIN emp_salary b ON a.dept_id = b.dept_id AND b.salary > a.salary GROUP BY a.dept_id, a.emp_name, a.salary HAVING COUNT(b.id) < 2;

逻辑是：对每一行，数一数同部门里比它薪资高的行有几条，小于2就说明它在前两名。这个写法的可读性和性能都不如窗口函数，但在老版本上是标准解法，面试提到能显示你了解历史演进。

三种方案的选择，我的经验是这样：

• 组内排名、累计、环比、跨度取值：优先窗口函数，一次扫描，可读性最好。
• 结果需要降维聚合：用 GROUP BY，别硬套窗口。
• 老版本环境（5.7 及以下）：小数据量用相关子查询，大数据量用自连接，但要严格控制中间结果规模。
• 需要去重取最新：8.0 用 ROW_NUMBER，8.0 以下用ORDER BY update_time DESC LIMIT 1配合临时表，或者用主键反查。

还有一个常被忽略的对比：窗口函数和 DISTINCT 一起用。SELECT DISTINCT和窗口函数混在一层里，结果往往不是你想的那样，因为去重可能发生在窗口计算之后，也可能因为窗口列的差异导致本来该合并的行没合并。稳妥做法是先在子查询里 DISTINCT，外层再算窗口。

5.3 面试官顺带追问的那些点

窗口函数问到后面，面试官常常会顺着往旁边的知识点延伸，这几个出现频率最高。

第一个是执行计划。会问"你怎么确认这条窗口函数 SQL 走得好不好"。答法就是前面说的：EXPLAIN FORMAT=TREE看有没有额外排序，EXPLAIN ANALYZE看实际耗时和行数，配合Created_tmp_disk_tables判断有没有落盘临时表。能说出"我主要看排序步骤和临时表"就够了，比背术语管用。

第二个是索引设计。常见问法是"这条 SQL 你会怎么建索引"。思路是把 PARTITION BY 的列放前面、ORDER BY 的列放后面，匹配窗口的有序需求。如果过滤条件里还有等值列（比如 status），考虑放最前面做覆盖。不要忘了评估维护成本——每多一个索引，写入就多一份开销。

第三个是 MVCC。窗口函数和 MVCC 没有直接关系，但面试官经常顺手问一句隔离级别。简单说，InnoDB 靠 undo log 和读视图实现一致性读，普通 SELECT 走快照读，不加锁；而窗口函数只是执行阶段的一环，不改变读的性质。要注意的是，如果你在窗口函数子查询里加上FOR UPDATE，那就是当前读了，会加锁，这在统计类场景里应该极力避免。

第四个是版本兼容。如果面试官说"我们线上是 5.7"，别慌，把自连接方案和用户变量方案讲清楚就行。同时可以提一句用户变量在 8.0 里不再是可靠方案，因为 SELECT 中赋值表达式的求值顺序没有保证——这一句话往往能把话题引到你熟悉的深水区。

第五个是结果一致性。面试官可能会问"这个统计结果下班跑和凌晨跑一样吗"。这时候要主动提两点：一是窗口函数本身是确定性的（前提是没有用 ROW_NUMBER 且没有加唯一兜底），二是数据本身在变，如果统计口径涉及 T+1 快照，要确保数据源已经定格。这个角度很少有人主动展开，提出来会显得有真实的生产经验。

最后分享一个我自己养成的习惯。每次写完一段复杂的窗口函数 SQL，我都会在下面附一行注释，写清楚"这段解决什么业务问题、依赖哪个索引、预期行数量级"。三个月后回头看，那句注释能省掉重新读一遍 SQL 的时间。窗口函数写得越熟，越容易把一段逻辑写得很紧凑，而紧凑的代码恰恰是最需要注释的——毕竟ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date, id)这一行，半年后连自己都要想两秒才知道当初为什么补了个 id 上去。