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) ) ENGINEInnoDB;用自连接的写法取每人金额前三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把结果集切成若干互不相干的小块相当于 GROUP BY但不合并行ORDER BY决定块内每一行的先后顺序相当于给每块内部排序ROWS/RANGE BETWEEN决定当前行能看到块内的哪些行相当于一个滑动的取景框理解这三件套有个很直观的类比把整张结果集想象成一栋楼的住户名单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()RANK()DENSE_RANK()951119521188332884328853276663一眼能看出的规律ROW_NUMBER是纯序号从 1 数到 N 绝不重复RANK是跳跃排名遇到并列就占用后面的名次两个第 1 之后直接跳到第 3DENSE_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 ) ENGINEInnoDB; 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等于 0MySQL 返回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写法第 1 行第 2 行第 3 行第 4 行SUM(amount) OVER (PARTITION BY user_id)430430430430SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)100150350430同样一个SUM只因为加了个ORDER BY语义就从总计变成了截至当前累计。我见过最典型的事故是有人在算用户总消费额时顺手写了ORDER BY结果报表里每个人的总消费额都变成了一串递增的阶梯数查了半天才找到这个原因。对应的 SQLSELECT 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顺序amountprev_amountnext_amountdiff1100NULL500250100200-5032005080150480200NULL-120三个细节值得说清楚。第一第一行的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 时除法会产生NULLMySQL 默认配置下除以零返回NULL而不报错但如果开了ERROR_FOR_DIVISION_BY_ZERO严格模式插入时会报错。为了生成一个干净的报表我用NULLIF把 0 转成NULL这样环比增长率就是NULL前端显示成—比显示Infinity或者负无穷要体面得多。第二处是WITH ... ASCTE。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 INTERVALSELECT 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索引帮不上忙就得老老实实排序。由此得出几条实用的优化经验现象原因对策加窗口函数后查询从 0.1s 变 10s触发了临时表排序建(分区列, 排序列)的联合索引同一条 SQL 里多个窗口定义越来越慢每个不同的窗口定义都要单独排一次序尽量统一窗口定义或用WINDOW子句复用加了ORDER BY的分页查询更慢窗口计算完还要再排一次给ORDER BY用让外层ORDER BY复用窗口的排序键EXPLAIN里出现Using temporary分区后的数据需要落临时表检查是否能在过滤后输出更少的行把条件放进内层一个重要的实操原则是能早过滤就早过滤。窗口函数是在WHERE之后执行的所以把status 1、created_at 2024-01-01这类条件写进内层子查询能显著减少参与排序的行数。我见过有人把过滤条件写在外层也就是窗口算完之后再筛结果对一个亿级表做了全量排序改成内层过滤之后从 40 秒降到 1 秒出头。6.2 用 EXPLAIN 判断窗口有没有走索引EXPLAIN的输出里窗口函数相关的开销主要体现在这几个信号上Using temporary需要临时表通常是窗口定义和索引顺序不匹配。Using filesort需要额外排序同上。rows估算值很大但filtered很低说明过滤条件没下推到内层扫描了太多无用行。EXPLAIN ANALYZE8.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视为最小值升序时排最前。如果排序列可能有NULLROW_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这两个动作帮我在上线前拦下过好几次事故。