企业官网建设流程全解析

1. 从一条自连接 SQL 说起：开窗函数真正替你省下的是什么

前阵子帮同事改一个报表 SQL，需求很朴素：把每个用户最近三笔订单挑出来，顺便标上这笔订单在本人所有订单里的金额排名。他写出来的版本是这样的——orders表自己 JOIN 自己，用b.amount > a.amount统计比自己大的行数，再GROUP BY过滤COUNT(*) < 3。表里只有二十万行的时候跑得还算勉强，数据涨到两百万行，这条 SQL 直接把数据库连接池占满了，十几个任务排队等着超时。这不是 SQL 写得不好，这是思路还停留在没有开窗函数的年代。

MySQL 的开窗函数（Window Function，也叫窗口函数、分析函数）从8.0.2版本才开始支持，这个时间点很多人没意识到——网上大量流传的"MySQL 分组排名"教程，本质都是 5.7 时代的用户变量 hack 或者自连接方案。开窗函数解决的核心问题就一句话：在不折叠结果行数的前提下，跨行做计算。GROUP BY会把十行压成一行，你只能拿到聚合值；开窗函数则是每一行都保留，同时把"这一组内的排名""从第一行到当前行的累计值""上一行的值"当成一列贴在旁边。这个能力在报表、排行榜、同比环比、TopN 筛选这些场景里几乎是刚需。

这篇文章我会把 MySQL 的开窗函数拆成三大类来讲：排序编号类、开窗聚合类、位移取值类。之所以按这个方式分，不是教科书上的分类法，而是按照"你写业务 SQL 时脑子里蹦出的那个念头"来分的——想排名就查第一类，想累计和占比就查第二类，想跟上一行比就查第三类。每一类我会给出可以贴进客户端直接跑的例子、结果集对照表，以及我自己踩过的坑。

1.1 先看那段"没有开窗函数的日子"到底难在哪

把刚才的场景铺开。假设有一张订单表：

CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, KEY idx_user_time (user_id, created_at) ) ENGINE=InnoDB;

用自连接的写法取"每人金额前三"：

SELECT a.user_id, a.order_id, a.amount FROM orders a LEFT JOIN orders b ON a.user_id = b.user_id AND b.amount > a.amount GROUP BY a.user_id, a.order_id, a.amount HAVING COUNT(b.order_id) < 3;

这条 SQL 有三个致命的点。第一，复杂度是分区内行数的平方级——同一个用户有 1000 笔订单，就要产生 100 万次比较；第二，b.amount > a.amount这个条件遇到金额完全相同的两笔订单时会出问题，因为严格大于统计不到并列，排名会出现"两个第一、没有第二"的诡异结果；第三，GROUP BY里必须把order_id、amount都列全，否则在ONLY_FULL_GROUP_BY模式下直接报错，这也是很多人从 5.6 升级到 5.7 之后老 SQL 突然跑不动的原因。

关联子查询版本同理：

SELECT user_id, order_id, amount, (SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o.user_id AND o2.amount > o.amount) AS rk FROM orders o;

写起来更好懂，但因为需要对外层每一行回头扫一遍内层表，实际执行里基本就是"每行一次索引扫描"，两百万行数据跑四十分钟不稀奇。

提醒一句：如果你现在还在生产环境用用户变量@rank := @rank + 1来做分组排名，务必知道从 8.0.13 起，MySQL 已经明确标记这种"在 SELECT 列表中同时赋值和读取用户变量"的用法为废弃行为，官方文档明确说不保证求值顺序。升级到 8.0 之后结果可能悄悄变了，而且是静默变化，不会报错。

1.2 开窗函数的语法骨架：函数 + OVER 里的三件套

开窗函数的通用形式是这样的：

函数名([参数]) OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [ROWS|RANGE BETWEEN 起点 AND 终点] )

OVER括号里最多三件事，我习惯叫它"三件套"：

理解这三件套有个很直观的类比：把整张结果集想象成一栋楼的住户名单，PARTITION BY是"按楼栋分单元"，ORDER BY是"按门牌号排队"，ROWS BETWEEN是"你站在第 7 层，告诉你往下看几层、往上看几层"。开窗函数的每行输出，都是"以当前行为基准，从这个可视范围里算出来"的。

一个最小的例子：

SELECT order_id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total FROM orders;

这条 SQL 会把订单按用户分组、组内按时间排序，然后给出"该用户从第一笔到当前这笔的累计消费"。总行数跟原表一样，一条不多一条不少。

1.3 三大类划分：我为什么这么分

MySQL 8.0 支持的开窗函数一共十来个，硬背清单很容易忘。按用途分成三类，记忆负担会小很多：

第一类，排序编号：ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST()、NTILE()。这一类不接收参数（NTILE除外），只依赖OVER里的排序，输出的是"名次""百分位""桶号"这类序号语义的值。

第二类，开窗聚合：SUM()、AVG()、COUNT()、MAX()、MIN()、STDDEV()、VARIANCE()、BIT_OR()、BIT_AND()、BIT_XOR()、GROUP_CONCAT()、JSON_ARRAYAGG()。这一类本身就是聚合函数，加上OVER之后就变成"不折叠行的聚合"。它们的关键在于是否存在 ORDER BY，因为这会决定默认的取景范围，这是最容易出错的地方。

第三类，位移取值：LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()。这一类解决的是"我需要拿到同一组里另一行的值"——上一行的金额、组内第一个值、组内最后一个值。同比环比、首末对比、行间差值全靠它们。

下面三章，一类一章，逐个拆。

2. 排序编号这一类：ROW_NUMBER、RANK、DENSE_RANK 的差别比想象中大

这三个函数经常被当成"差不多"的东西混着用，直到某天报表上出现"并列第 1 名和并列第 3 名之间少了第 2 名"，或者"分页接口在两页里都返回了同一条记录"，才发现选错了。它们三个在面对并列值时的行为完全不同，而现实数据里并列值是常态——成绩表有同分、销售额有同额、点击数有同数，你以为的"金额唯一"往往只是因为样本太小。

先把差异用一张对照表说清楚。假设有一组成绩 95、95、88、88、88、76：

一眼能看出的规律：ROW_NUMBER是"纯序号"，从 1 数到 N 绝不重复；RANK是"跳跃排名"，遇到并列就占用后面的名次（两个第 1 之后直接跳到第 3）；DENSE_RANK是"紧凑排名"，并列只占一个名次号（两个第 1 之后是第 2）。选哪个完全取决于业务对"名次"的定义：发奖学金看RANK还是DENSE_RANK，取决于规则里写的是"并列第一后下一名是第二还是第三"；而做分页、做去重、做"每人只取一条"，只能选ROW_NUMBER，因为只有它保证序号唯一。

2.1 建一张能跑出上面结果的实验表

为了后面所有例子都能直接复现，我先建两张小表。成绩表：

CREATE TABLE scores ( id INT PRIMARY KEY AUTO_INCREMENT, student VARCHAR(20) NOT NULL, subject VARCHAR(20) NOT NULL, score INT NOT NULL ) ENGINE=InnoDB; INSERT INTO scores (student, subject, score) VALUES ('小明','数学',95),('小红','数学',95), ('小刚','数学',88),('小美','数学',88),('小强','数学',88), ('小丽','数学',76), ('小明','语文',82),('小红','语文',90), ('小刚','语文',90),('小美','语文',71);

三个排名函数并排跑：

SELECT student, subject, score, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS rn, RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rk, DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS drk FROM scores ORDER BY subject, score DESC;

数学部分的结果会和上面那张对照表完全一致。注意ORDER BY score DESC里的DESC不能丢，因为排名默认是按升序排的——这一点我在帮人看 SQL 时见过太多次，明明是"取最高分"，代码里却写了不带DESC的排序，结果把最低分排到了第一名。

还有一个容易被忽略的细节：ROW_NUMBER在遇到并列值时，到底谁排在前谁排在后，MySQL 官方是不保证的。它取决于执行计划里排序算子的稳定性，同一份数据在不同版本、不同索引情况下可能给出不同结果。所以如果你的业务对并列行的取舍有要求（比如"金额相同时按时间早的优先"），必须在ORDER BY里把决定性的列补全：

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC, created_at ASC) AS rn

这是一个小改动，但能让结果从"看起来随机"变成"永远确定"，做数据核对时差别巨大。

2.2 分组取 TopN：开窗函数最经典的应用场景

前面那段自连接 SQL，用ROW_NUMBER重写就是：

SELECT user_id, order_id, amount, created_at FROM ( SELECT user_id, order_id, amount, created_at, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY amount DESC, created_at DESC ) AS rn FROM orders WHERE status = 1 ) t WHERE t.rn <= 3;

结构非常清晰：内层算出每笔订单在本人名下的行号，外层筛掉行号大于 3 的。相比自连接，它有几个实实在在的好处。第一，复杂度从平方级降到O(n log n)，因为排序是可控的；第二，并列金额有明确处理方式——我这里补了created_at DESC作为第二排序键，保证结果稳定；第三，WHERE status = 1这样的过滤条件可以写在内层，先筛后排序，扫描行数大幅减少。

这里必须强调一个新手高频错误：窗口函数不能出现在WHERE子句里。写成WHERE ROW_NUMBER() OVER (...) <= 3会直接语法报错。原因是 SQL 的逻辑执行顺序里，WHERE早于SELECT中的窗口计算，WHERE执行的时候窗口函数的结果根本还不存在。正确的做法一定是外面包一层子查询或者 CTE，在外层过滤。这个限制在几乎所有支持窗口函数的数据库里都一样，不是 MySQL 的特例。

如果只需要"每组金额最高的一条"，还有个更省事的写法是ROW_NUMBER外层筛选改为= 1。这种"分组去重、每组留一条"的用法，在清洗数据时特别常见——比如从多来源合并的客户表里，同一手机号保留最新更新的一条：

SELECT * FROM ( SELECT c.*, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY updated_at DESC) AS rn FROM customers c ) t WHERE t.rn = 1;

这比GROUP BY phone加MAX(updated_at)再回表关联的写法简洁太多，而且在需要取出整行所有字段时优势尤其明显——GROUP BY方案要么违反ONLY_FULL_GROUP_BY，要么得写一堆MAX()兜底。

2.3 NTILE 与 PERCENT_RANK：把数据分桶和算百分位

NTILE(n)做的事情是把分区内的行尽可能均匀地分成 n 桶，桶号从 1 开始。它最典型的用途是"把用户按消费额分成 4 档"：

SELECT user_id, total_amount, NTILE(4) OVER (ORDER BY total_amount DESC) AS quartile FROM ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) t;

这里有个细节必须知道：当分区行数不能被 n 整除时，行数多的桶会排在前面。比如 10 行分成 4 桶，结果是 3、3、2、2 而不是 2.5、2.5、2.5、2.5。我之前做用户分层时按NTILE(10)分十档，第一档和第十档各多了几个人，一开始还以为是数据问题，后来才反应过来是这个规则。做等频分箱如果对桶大小有严格要求，用NTILE前得先确认总行数能不能整除。

PERCENT_RANK()输出的是相对排名，公式是(rank - 1) / (rows - 1)，取值范围 0 到 1，第一行永远是 0，最后一行永远是 1。CUME_DIST()输出的是累积分布，公式是"小于等于当前值的行数 / 总行数"，最小值大于 0，最大值等于 1。两者常用来做"这个值超过了百分之多少的人"这类判断，比如商城里展示"您的消费超过了 96% 的用户"，底层就是CUME_DIST()。

SELECT user_id, total_amount, ROUND(PERCENT_RANK() OVER (ORDER BY total_amount) * 100, 2) AS pct, ROUND(CUME_DIST() OVER (ORDER BY total_amount) * 100, 2) AS cume_pct FROM ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) t;

注意一个坑：如果分区内只有一行，PERCENT_RANK的分母rows - 1等于 0，MySQL 返回NULL而不是报错。做前端展示时记得对NULL做兜底处理。

3. 开窗聚合这一类：累计求和、移动平均与占比计算

如果说排序编号类解决的是"排第几"，那开窗聚合类解决的是"到现在为止一共多少"和"这一行占了多大比例"。这类函数最大的特点在于它同时具备两个身份：不加OVER的时候就是普通聚合函数，加了OVER就变成窗口函数。也正因为这个双重身份，它有一个默认取景范围的机制，这个机制是这类函数所有坑的源头。

3.1 带不带 ORDER BY，默认取景范围完全不一样

这是本节最核心的一句话：OVER()里有没有ORDER BY，决定了默认的窗口帧完全不同。

• 如果OVER里只有PARTITION BY，默认帧是整个分区，效果相当于把聚合值广播到分区内每一行。
• 如果OVER里同时有PARTITION BY和ORDER BY，默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是"从分区第一行到当前行"，这就是累计求和能自动生效的原因。

用实际数据验证一下。假设有某用户 1 月的订单金额是 100、50、200、80：

同样一个SUM，只因为加了个ORDER BY，语义就从"总计"变成了"截至当前累计"。我见过最典型的事故是：有人在算用户总消费额时顺手写了ORDER BY，结果报表里每个人的总消费额都变成了一串递增的阶梯数，查了半天才找到这个原因。

对应的 SQL：

SELECT order_id, user_id, amount, created_at, SUM(amount) OVER (PARTITION BY user_id) AS user_total, SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total, ROUND(amount / SUM(amount) OVER (PARTITION BY user_id) * 100, 2) AS pct FROM orders WHERE status = 1;

第三个字段pct展示了另一种常见玩法：同一行里同时用明细值和窗口聚合值做运算。这正是开窗函数比GROUP BY强的地方——GROUP BY之后你手里只剩聚合值，明细值已经被折叠掉了，要算占比必须再 JOIN 回原表。而开窗函数天然把两者放在同一行，一行 SQL 直接算出"单笔金额占用户总消费的百分比"。

3.2 累计求和与占比之外，还能算移动平均

累计求和只用到默认帧，但移动平均必须自己写ROWS BETWEEN。比如"最近 3 笔订单的平均金额"，窗口要能往前看 2 行、往后不看：

SELECT order_id, user_id, amount, created_at, ROUND(AVG(amount) OVER ( PARTITION BY user_id ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 2) AS ma3 FROM orders;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW的意思是：取景框从"当前行往前数 2 行"开始，到"当前行"结束，一共最多 3 行。第一行因为前面没有行，取景框里只有它自己，所以第一行的移动平均值就等于它自己的金额；第二行是前两行的平均。这个"边界处窗口自动缩小"的行为是标准规定，不用担心会取到NULL导致平均值为NULL。

再看一个做日报囤积位常用的场景：每个用户每天的下单金额总和、以及"截至当天的整月累计"。这时候PARTITION BY要按user_id和月份同时切：

SELECT user_id, DATE(created_at) AS d, SUM(amount) AS day_amount, SUM(SUM(amount)) OVER ( PARTITION BY user_id, DATE_FORMAT(created_at, '%Y-%m') ORDER BY DATE(created_at) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS month_running_total FROM orders WHERE status = 1 GROUP BY user_id, DATE(created_at);

这里出现了聚合函数嵌套的写法：内层SUM(amount)是GROUP BY的普通聚合，先把一天的多笔订单压成一行；外层SUM(...) OVER (...)再对这一行一行地做窗口累计。这种"先聚合再开窗"的组合非常常用，比如报表里先算日活、再算累计日活。执行顺序上，GROUP BY先于窗口函数发生，所以窗口函数看到的输入已经是聚合后的行了，这一点一定要建立清晰的心理模型，否则写复杂报表时会一直绕不出来。

3.3 COUNT 在窗口里的两个身份

COUNT在窗口函数里有两种完全不同的写法，很容易混淆：

• COUNT(*) OVER (...)或COUNT(1) OVER (...)：统计窗口内的行数，包括NULL行。
• COUNT(字段) OVER (...)：统计窗口内该字段非 NULL的行的数量。

举个实际例子，统计每个用户截至当前订单的"总订单笔数"和"有备注的订单笔数"：

SELECT order_id, user_id, remark, COUNT(*) OVER (PARTITION BY user_id ORDER BY created_at) AS cnt_all, COUNT(remark) OVER (PARTITION BY user_id ORDER BY created_at) AS cnt_remark FROM orders;

很多人算"去重计数"时会想当然地写COUNT(DISTINCT user_id) OVER (...)。好消息是 MySQL 8.0 确实支持COUNT(DISTINCT ...)在窗口里使用，但有一个限制：使用DISTINCT时，OVER里不能有ORDER BY，否则报错。要按顺序做去重累计计数，只能绕道——用子查询先算出行号，再在外面判断"这一行是不是该值的首行"，然后对布尔结果做SUM累计：

SELECT *, SUM(is_first) OVER (PARTITION BY dt ORDER BY dt, id) AS distinct_users FROM ( SELECT id, dt, visitor_id, CASE WHEN ROW_NUMBER() OVER ( PARTITION BY dt, visitor_id ORDER BY id ) = 1 THEN 1 ELSE 0 END AS is_first FROM visit_log ) t;

这个套路我第一次见的时候觉得绕，用两次之后就顺了，它其实是"用行号标记首次出现、再累计求和"的通用模板，算累计 UV、累计新增用户都靠它。

3.4 MAX/MIN 在窗口里的隐藏用途

MAX()加窗口最常见的用法是求"截至目前的最高值"，比如股票价格的历史最高点：

SELECT trade_date, close_price, MAX(close_price) OVER (ORDER BY trade_date) AS running_max FROM stock_daily WHERE stock_code = 'XXXXXX';

但它在数据清洗里还有个不那么显眼但很好用的用法：用窗口MAX/MIN判断某个值是不是组内的极值，从而定位异常记录。比如找出每个用户金额最大的一笔订单：

SELECT * FROM ( SELECT order_id, user_id, amount, MAX(amount) OVER (PARTITION BY user_id) AS max_amount FROM orders ) t WHERE amount = max_amount;

这种写法比ROW_NUMBER更直观，而且有个好处：如果同一个用户有多笔金额相同的最大订单，它会全部返回。这在排查"为什么同一用户出现两条最大记录"时非常有用——ROW_NUMBER只会给你一条，让你误以为数据是干净的。

4. 位移取值这一类：LAG/LEAD 与 FIRST_VALUE/LAST_VALUE 的实战

第三类函数解决的问题是"我需要看到同一组里别的行的值"。业务上最典型的就是同比环比——这个月的销售额，跟上一月比涨了多少；今天的访问量，跟昨天比是涨是跌。在窗口函数出现之前，这种需求要么靠自连接把"上个月"关联进来，要么靠应用层循环处理，代码量都不小。

4.1 LAG 与 LEAD 的基本语义与偏移量

LAG(表达式, 偏移量, 默认值)取的是"当前行往前数第 N 行"的值，LEAD则往后数。第三个参数是越界时的默认值，不填就是NULL。

SELECT order_id, user_id, amount, created_at, LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount, LEAD(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS next_amount, amount - LAG(amount, 1, amount) OVER ( PARTITION BY user_id ORDER BY created_at ) AS diff FROM orders;

结果集大致是这样（假设同一用户四笔订单金额 100、50、200、80）：

三个细节值得说清楚。第一，第一行的prev_amount是NULL，因为前面没有行；第二，我在算diff的时候给LAG传了默认值amount，也就是当取不到上一行时，把当前行的金额当作"上一行"，这样第一行的差值就是 0 而不是NULL，报表上不会出现空行——这是我做日报时的习惯，能省掉前端一堆判空逻辑；第三，LEAD和LAG的偏移量可以是任意正整数，LAG(amount, 7)就是"上周同一天"，做周同比很好用。

有个坑一定要注意：LAG/LEAD拿的是物理相邻行的值，不是"日期相邻"的值。如果你的数据存在日期跳跃（比如周末没有数据），LAG(amount, 1)拿到的是上一笔有记录的订单，而不是"昨天"的订单。这时候必须在应用层或 SQL 层先把日期补齐。补日期的方法通常是建一张日期维表或者用递归 CTE 生成日期序列，再左连接，最后才对补齐后的结果做LAG。

4.2 FIRST_VALUE 与 LAST_VALUE：那个让无数人翻车的默认帧

FIRST_VALUE(expr)取窗口内第一行的值，LAST_VALUE(expr)取窗口内最后一行的值。听起来简单，但LAST_VALUE是所有窗口函数里最容易踩坑的一个。

原因还是默认帧。当你写了ORDER BY而没写帧范围时，默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，取景框的终点是"当前行"。所以LAST_VALUE取到的"最后一行的值"，其实是当前行的值本身——因为取景框到当前行就截止了。于是你满怀期待地想取每人最后一笔订单的金额，结果每一行返回的都是自己的金额，看着像没生效。

正确写法是手动把帧的终点推到分区末尾：

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

这里我把FIRST_VALUE也一并加了完整帧，因为虽然它在默认帧下恰好也能取到第一行（起点是UNBOUNDED PRECEDING），但保持两个函数的帧定义一致，代码看起来更整齐，也避免了以后有人调整顺序时踩坑。

另一种绕开LAST_VALUE的方法是反过来排序用FIRST_VALUE——把ORDER BY created_at DESC加进去，然后用FIRST_VALUE取第一条，效果等价。这个技巧在我还没完全吃透帧语法的时候用得很多，实话说现在也偶尔偷懒这么写。

4.3 用 LAG 算环比增长率：一个完整的月度报表

把上面这些组合起来，做一个真实的月度环比报表。数据来自订单表，按用户按月汇总：

WITH monthly AS ( SELECT user_id, DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(amount) AS amt FROM orders WHERE status = 1 GROUP BY user_id, DATE_FORMAT(created_at, '%Y-%m') ) SELECT user_id, ym, amt, LAG(amt, 1) OVER (PARTITION BY user_id ORDER BY ym) AS prev_amt, ROUND( (amt - LAG(amt, 1) OVER (PARTITION BY user_id ORDER BY ym)) / NULLIF(LAG(amt, 1) OVER (PARTITION BY user_id ORDER BY ym), 0) * 100, 2 ) AS mom_rate FROM monthly ORDER BY user_id, ym;

这段 SQL 里有三处值得展开讲。

第一处是NULLIF(..., 0)。当上个月金额为 0 时，除法会产生NULL（MySQL 默认配置下除以零返回NULL而不报错，但如果开了ERROR_FOR_DIVISION_BY_ZERO严格模式，插入时会报错）。为了生成一个干净的报表，我用NULLIF把 0 转成NULL，这样环比增长率就是NULL，前端显示成"—"，比显示Infinity或者负无穷要体面得多。

第二处是WITH ... AS（CTE）。MySQL 8.0 也支持了 CTE，配合窗口函数使用非常舒服——先把月度汇总算成一个临时结果集，再在上面做窗口计算，逻辑分层清晰。相比嵌套三层的子查询，CTE 的可读性提升不是一点半点。另外 CTE 还可以在同一个查询里被引用多次，MySQL 8.0 会尝试物化，这在某些场景下比重复写子查询性能更好。

第三处是ORDER BY ym。因为ym是'2024-01'这样的字符串，'2024-01' < '2024-02'的字典序恰好和月份顺序一致，所以能直接排。但如果格式是'2024-1'这种不补零的月份，字典序就会出错（'2024-10'会排在'2024-2'前面），这是个很隐蔽的坑。稳妥做法是存成日期类型，或者统一补零，或者干脆用年月两个整数列。

4.4 NTH_VALUE 与其他取值函数的现实使用频率

NTH_VALUE(expr, n)取窗口内第 n 行的值，理论上很灵活，但实际业务里用得不多。它最典型的使用场景是"每个用户第二笔订单的金额"：

SELECT DISTINCT user_id, NTH_VALUE(amount, 2) OVER ( PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS second_amount FROM orders;

注意这里用了SELECT DISTINCT，因为NTH_VALUE会给分区内每一行都返回同一个值，想只保留一条就得去重。这其实提示了一个思路：当你想用NTH_VALUE取某个特定位置的值时，先想一想ROW_NUMBER加过滤是不是更直接。大多数情况下答案都是"是"，因为NTH_VALUE需要写完整的帧定义、还要去重，而ROW_NUMBER外层筛一下rn = 2就完事了。

MySQL 还有个限制需要知道：窗口函数不支持IGNORE NULLS/RESPECT NULLS选项。标准 SQL 里FIRST_VALUE(x IGNORE NULLS)可以跳过NULL取第一个非空值，但 MySQL 目前不支持这个语法，取到NULL就是NULL。要做"取最近一次非空的值"，得先用ROW_NUMBER标记非空行、再取首个，或者用MAX(CASE WHEN x IS NOT NULL THEN created_at END) OVER (...)这种变通写法。这个限制在写"取用户最近一次有效手机号"这类需求时会撞上，提前知道能少查半天文档。

5. 窗口定义子句的门道：PARTITION BY、ORDER BY 与帧范围

写到这里，三类函数都过了一遍，但有一个东西贯穿始终却没被单独拿出来讲——OVER里的那三件套本身。函数选对了，帧写错了，结果照样是错的。而且帧相关的错误往往不报错，只是默默地给出一个"看起来合理但实际不对"的结果，这是最危险的。

5.1 ROWS 和 RANGE 的区别到底在哪

这是窗口函数里最需要花时间理解的一对概念。

ROWS是按物理行计数，RANGE是按值计数。当ORDER BY的列存在重复值时，两者的差异就会显现出来。

举例说明。假设有订单金额排序为 100、100、200、300：

使用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW的时候，第二行（第二个 100）的窗口里只有两行：[100, 100]，累计和是 200。

使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW的时候，第二行的窗口会包含所有值等于 100 的行，也就是同样两行，结果还是 200，看起来一样。

但如果排序键有更多重复值，差异就出来了：金额是 100、100、100、200，处理到第一个 100 时，ROWS的窗口只有它自己一行，累计和 100；而RANGE的窗口会一次性包含所有值为 100 的行（三行），累计和 300。这就是所谓的peer rows（同值行）概念：RANGE会把排序值相同的行当作一个整体对待。

更典型的场景是日期维度上的RANGE用法，MySQL 8.0 支持RANGE INTERVAL：

SELECT trade_date, close_price, AVG(close_price) OVER ( ORDER BY trade_date RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW ) AS ma7 FROM stock_daily;

这个写法表示"取当前行日期往前 7 天（含当天）内的所有行算平均"，它按日期值滑动，而不是按"往前数 7 行"。对于有停牌、缺数据的股票来说，这种按实际日期取的移动平均才是正确的业务含义。用ROWS 6 PRECEDING会变成"往前数 7 条有记录的行"，遇到停牌就会把很久以前的数据算进来。

RANGE INTERVAL是 MySQL 8.0 的特性，8.0 之前的版本完全没有窗口函数，这里只是说明帧的定义方式。另外RANGE对ORDER BY的列有约束——只能有一列，且必须是数值或时间类型，多列排序时必须回退到ROWS。

5.2 命名窗口：把重复的 OVER 抽出来

如果一个查询里有多个窗口函数，OVER里的内容又完全一样，代码会变得非常臃肿。MySQL 8.0 支持WINDOW子句做命名：

SELECT user_id, order_id, amount, created_at, ROW_NUMBER() OVER w AS rn, SUM(amount) OVER w AS running_total, AVG(amount) OVER w AS avg_amount FROM orders WINDOW w AS ( PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW );

WINDOW w AS (...)定义了一个命名窗口，后面所有OVER w都复用它。这个写法有两个好处：一是可读性明显提升，读者一眼就知道这几个函数用的是同一个分区和排序规则；二是可能带来性能优化——当多个窗口函数的定义完全相同时，MySQL 只需要排一次序就能同时满足它们，如果写成三个独立的OVER (...)，优化器不一定能识别出它们是等价的。

要注意一个规则：命名窗口里定义的内容可以被覆盖一部分。比如你可以写OVER (w ORDER BY amount)，用命名窗口w的分区规则加上新的排序。但这个覆盖有方向限制——PARTITION BY不能被覆盖，ORDER BY可以。这个细节平时用得少，知道有这回事就够了。

5.3 什么时候窗口函数会退化成"全分区"

有个判断规律可以记住：OVER里没有ORDER BY，帧就是整个分区。这在某些场景下非常有用，比如"算每个用户总消费占全体用户总消费的比例"：

SELECT user_id, total, ROUND(total / SUM(total) OVER () * 100, 2) AS pct_of_all FROM ( SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ) t;

注意这里的OVER ()括号是空的——没有PARTITION BY也没有ORDER BY，整个结果集就是一个大分区，SUM(total) OVER ()得到的就是全局总和。这个写法比再写一个子查询求总和然后做交叉连接简洁得多。

同样的思路可以推广到"占比"类需求上：PARTITION BY里放什么，就决定了百分比的分母是谁。放user_id就是个人内的占比，放DATE(created_at)就是当天内的占比，什么都不放就是全局占比。切换分母只需要改一个字段，这是我特别喜欢开窗函数的一点。

6. 性能与踩坑：排序代价、索引失效和版本兼容

功能讲完了，说点更实际的东西。开窗函数在大多数场景下比自连接和关联子查询快得多，但它不是免费的——它有个绕不开的成本:排序。理解这个成本从哪来、能不能用索引消掉，决定了你写的 SQL 是 200 毫秒还是 20 秒。

6.1 开窗函数的性能账本

窗口函数的执行过程可以粗略概括为三个阶段：先按WHERE过滤数据，然后按PARTITION BY和ORDER BY的要求把数据排序或者分组，最后对每一行计算窗口结果。其中第二阶段是主要开销。

MySQL 8.0 引入了一个窗口函数优化：如果PARTITION BY和ORDER BY的列顺序与某个可用索引的前缀顺序一致，优化器可以直接按索引顺序读取数据，跳过一次额外的排序。举个例子，如果orders表上有KEY idx_user_time (user_id, created_at)，那么：

SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)

就能顺着idx_user_time的顺序走，避免排序。但如果写成PARTITION BY user_id ORDER BY amount，索引帮不上忙，就得老老实实排序。

由此得出几条实用的优化经验：

一个重要的实操原则是：能早过滤就早过滤。窗口函数是在WHERE之后执行的，所以把status = 1、created_at > '2024-01-01'这类条件写进内层子查询，能显著减少参与排序的行数。我见过有人把过滤条件写在外层（也就是窗口算完之后再筛），结果对一个亿级表做了全量排序，改成内层过滤之后从 40 秒降到 1 秒出头。

6.2 用 EXPLAIN 判断窗口有没有走索引

EXPLAIN的输出里，窗口函数相关的开销主要体现在这几个信号上：

• Using temporary：需要临时表，通常是窗口定义和索引顺序不匹配。
• Using filesort：需要额外排序，同上。
• rows估算值很大但filtered很低：说明过滤条件没下推到内层，扫描了太多无用行。

EXPLAIN ANALYZE（8.0.18 起支持）会给出实际的执行耗时，能看到窗口算子占了多少时间，比EXPLAIN的估算值靠谱得多。我排查慢 SQL 的习惯是先用EXPLAIN看有没有明显的排序和临时表，再用EXPLAIN ANALYZE定位到具体算子，比盲目加索引效率高。

6.3 从 5.7 升级到 8.0 时踩过的几个坑

这部分是我自己迁移项目时真实遇到的，列出来给准备升级的人参考。

第一个坑：窗口函数必须 8.0.2 以上。有些云数据库在 8.0 早期版本上，SELECT @@version显示 8.0 但实际是 8.0.1 或者更早，窗口函数直接语法报错。遇到"语法错误但语法明显没问题"的情况，先确认具体的小版本号。

第二个坑：ORDER BY里的别名行为。8.0 对ORDER BY中使用SELECT别名的处理更严格了，某些在 5.7 能跑的写法会报"Unknown column"。如果窗口函数的输出列别名要用于外层ORDER BY，务必把窗口计算放在子查询或 CTE 里，外层再引用。

第三个坑：用户变量的静默失效。前面提过，8.0 中在SELECT列表里混用赋值和读取用户变量的行为不再保证顺序。项目里凡是看到@rank、@prev这类变量做分组排名的，升级前一律改写成开窗函数，别赌运气。

第四个坑：ONLY_FULL_GROUP_BY默认开启。5.6 时代大量"GROUP BY只写一半字段"的 SQL，在 8.0 里全部报错。虽然这不直接是窗口函数的问题，但改这些 SQL 的时候，很多场景其实用开窗函数重写会更自然——比如"取每组最新一条"原本靠GROUP BY加隐式取值，现在用ROW_NUMBER一行就搞定，逻辑还更明确。

6.4 几个我反复踩过的小坑

说几个琐碎但坑人的点，都是真实排查出来的。

PARTITION BY里漏了列。做"每人每年消费排名"时忘记把年份放进PARTITION BY，结果变成了跨年累计排名，报表上每个人都排在同一个名次区间里。这类错误通常表现为"分组粒度不对"，排查方法是先单独跑一遍SELECT DISTINCT 分区列看粒度对不对。

ORDER BY里用了可为空的列。ORDER BY到NULL值的时候，MySQL 把NULL视为最小值（升序时排最前）。如果排序列可能有NULL，ROW_NUMBER会把NULL行排到最前面，LAG拿到的就可能是NULL。稳妥做法是在ORDER BY里对NULL做处理，比如ORDER BY COALESCE(finish_time, '1970-01-01')。

窗口函数和LIMIT的顺序。窗口函数在LIMIT之前执行，所以如果你在内层用窗口函数算好排名，外层LIMIT 10是"排名算完之后取前 10 行"，而不是"取前 10 行再算排名"。这个顺序在很多分页场景里会直接影响结果，需要按业务语义确认哪种才是对的。

GROUP_CONCAT加窗口的排序问题。GROUP_CONCAT(x) OVER (...)是支持的，但它的输出顺序遵循的是OVER里的ORDER BY，而不是GROUP_CONCAT函数内部可能存在的排序设置。想控制拼接顺序，只能通过OVER里的ORDER BY，这一点和普通GROUP_CONCAT的用法不一样。

最后聊一个我个人觉得最值得养成的习惯：写带窗口函数的 SQL 时，永远先把内层的结果集单独跑一遍看一眼。因为窗口函数的结果一旦被外层过滤或者聚合，错误会变得很难察觉——你看到的是一张格式完美的报表，但里面的数字可能因为一个帧定义的问题全错了。先看内层的原始行、看PARTITION BY的粒度、看ORDER BY有没有并列值，这三个检查花不了一分钟，能省掉后面几个小时的对数时间。我自己的习惯是PARTITION BY的列一定单独SELECT DISTINCT验一遍，ORDER BY的列一定看一眼有没有重复值和NULL，这两个动作帮我在上线前拦下过好几次事故。