窗口函数这个词第一次看到的人容易想复杂觉得是不是什么高深黑科技。我接手第一个窗口函数需求时特别朴素电商后台让我“把每个品类下面销量前3的商品打上标签”。我当时的本能反应是GROUP BY category_id再取最大值结果发现GROUP BY一旦加上其他商品明细就全丢了折腾了半天用两三层子查询才算做出来。后来同事给我看了一眼窗口函数的写法一行ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY sales DESC)就把事办了。从那天起我就明白窗口函数不是锦上添花的技巧而是一个SQL开发者绕不开的基本功。这篇内容是我这几年使用窗口函数的系统性梳理从核心定位、执行顺序、常用函数族到生产环境的坑和不同数据库的差异一次性讲透。适合刚接触窗口函数的人做体系化学习也适合用了一段时间但总觉得边界没吃透的人查漏补缺。1. GROUP BY做不到的事窗口函数到底解决什么问题1.1 从“每个品类销量前三”说起先看一个具体场景。假设有一张销售流水表sales结构大概是这样的CREATE TABLE sales ( id INT PRIMARY KEY, product_id INT, category_id INT, amount DECIMAL(10,2), sale_date DATE );业务方要拿到每个品类下销量排名前三的商品。在没有窗口函数的情况下最直接的写法是关联子查询对每一行商品数一数同品类里销量比它大的商品有多少个小于3的就是前三。SELECT * FROM sales s WHERE ( SELECT COUNT(*) FROM sales s2 WHERE s2.category_id s.category_id AND s2.amount s.amount ) 3;这个逻辑本身没毛病但它有一个潜在问题如果有两个商品销量相同且都排在第三名这个查询会把两个都选出来而业务上可能只想要“物理上的前3行”。另外这种写法对sales表要做多次相关子查询数据量大一点执行效率就很扎心。窗口函数的版本是这样的SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) AS rn FROM sales ) t WHERE rn 3;子查询里对每个品类独立编号外面的WHERE rn 3收口拿结果。这种写法语义清晰执行计划也更容易优化。这是我第一次直观感受到窗口函数的威力它能在不聚合、不丢失明细行的前提下给每一行算出一个“带上下文”的序号。1.2 GROUP BY折叠明细与窗口函数保留明细的根本差异很多人分不清GROUP BY和窗口函数的定位本质上是因为没理解它们对“行”的态度完全不同。GROUP BY做的是折叠操作。它把多行合并成一行输出结果里不可能再看到原始明细。聚合函数在GROUP BY里只能返回组的标量值比如每个品类的总销售额、平均销售额、最高销售额。你不可能在同一个结果集里既看到每个商品明细又看到品类级的汇总值——除非再做一次JOIN。窗口函数恰恰相反它不改变结果集的行数。每一行还是那一行只是在旁边多出一列计算结果这一列是通过一个“窗口”算出来的。窗口可以理解为以当前行为基准划定一个范围内的所有行然后在这个范围内做排序、聚合、偏移取值等操作。用一个生活化的类比来解释。把全班学生按班级分组后GROUP BY等于给每个班发一张合影合影上只有一个平均值窗口函数是给每个学生发一张成绩单成绩单上不仅有他自己的分数还印着“本班平均分”“全班最高分”“他在班里的名次”。你要看整体靠合影你要同时知道个体和整体的关系就得靠成绩单。两者核心差异我整理成了表格对比项GROUP BY窗口函数作用方式按分组键折叠多行为一行按分区范围逐行计算行数不变返回行数每组一行原表行数完整保留明细数据不可见完整可见典型场景汇总报表、指标大盘TopN、累计值、排名、环比同比执行时机在HAVING前完成在GROUP BY和HAVING之后SELECT阶段完成1.3 哪些场景其实用不到窗口函数这里多说一句窗口函数确实好用但不要为了用而用。如果你的需求只是“按品类汇总销售额”那GROUP BY category_id就是最合适的方案硬套窗口函数反而多此一举。窗口函数的核心优势在于“保留明细 附加上下文”只要需求里明确需要同时看到原始行和上下文计算值或者需要基于明细行做排名、取偏移量、算移动累计那么窗口函数就是那个不该跳过的选择。判断标准就一条如果我需要的结果集行数 原表行数但每一行又携带了分组后的统计信息那就轮到窗口函数上场了。2. 理解SQL执行顺序才能看懂窗口函数为什么“在这里”执行2.1 窗口函数在逻辑执行计划中的确切位置很多初学者写窗口函数报错比如在WHERE里直接引用窗口函数结果被数据库拒绝就是因为对执行顺序没有概念。SQL的逻辑执行顺序虽然不同数据库优化器会有差异但从语义上讲基本是固定的FROM/JOIN确定数据源组装表WHERE过滤原始行GROUP BY分组HAVING过滤分组后的组窗口函数在SELECT阶段计算DISTINCT去重ORDER BY排序LIMIT/OFFSET截断重点在第五步。窗口函数在WHERE、GROUP BY、HAVING全部结束后才执行所以你在WHERE里写ROW_NUMBER() OVER(...) 1数据库会直接报错因为它此时根本还没有计算出这个序号值。正确的姿势是先在一个子查询或CTE公共表表达式里算好再在外面过滤。-- 错误写法MySQL会直接报错 SELECT * FROM sales WHERE ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) 1; -- 正确写法先算后过滤 WITH ranked_sales AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) AS rn FROM sales ) SELECT * FROM ranked_sales WHERE rn 1;还有一个容易被忽略的细节如果查询中同时存在GROUP BY和窗口函数窗口函数看到的是分组之后的结果。比如你可以先按品类聚合再在品类级别上求“所有品类销售额的累加占比”这种写法会用到嵌套聚合像SUM(SUM(amount)) OVER(...)这样的形式。内层的SUM(amount)是GROUP BY产生的分组汇总值外层的窗口函数再对这个汇总值做计算。2.2 OVER()的三个构成部分分区、排序、窗口边界窗口函数的核心是OVER()子句它由三部分组成理解了这三部分就理解了窗口函数的90%第一部分PARTITION BY决定按什么维度切分窗口。它比GROUP BY轻量不折叠行只是把数据逻辑上分成若干块每一块内部独立计算。比如PARTITION BY category_id就是让每个品类的商品各自比较、各自排名。如果没有PARTITION BY整个结果集就是一个大窗口所有行放在一起比较。第二部分ORDER BY决定窗口内的排列顺序。很多窗口函数依赖顺序才有意义比如ROW_NUMBER()需要知道谁先谁后LAG()需要知道上一行是哪一行。如果没有ORDER BY窗口函数会按照数据出现在结果集中的不确定顺序计算结果不可预测。第三部分行帧ROWS / RANGE决定窗口的边界范围。这一部分最容易被忽略但它直接影响聚合窗口函数的计算结果。语法是ROWS BETWEEN ... AND ...常见的有ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区第一行到当前行ROWS BETWEEN 2 PRECEDING AND CURRENT ROW从当前行往前数2行到当前行ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING整个分区所有行ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING从当前行到分区最后一行如果只写ORDER BY不写行帧默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW也就是从分区起点到当前行。这个默认行为是很多“累计值”计算的基础同时也是很多“诡异结果”的根源后面我会专门讲这个坑。2.3 为什么PARTITION BY ORDER BY一起用时会出现“累计”效果PARTITION BY和ORDER BY同时出现时窗口的默认范围不再是整个分区而是“从分区第一行到当前行”。这个设计的结果就是聚合函数会呈现出累计效果。举个例子。每个品类按日期排序计算截至当前日期的累计销售额SELECT category_id, sale_date, amount, SUM(amount) OVER( PARTITION BY category_id ORDER BY sale_date ) AS cumulative_amount FROM sales;在这个查询里SUM(amount)并不是对全品类求和而是对每个品类从最早日期到当前日期这一段的所有行求和。排序键越靠后累计值越大。到了分区最后一行累计值等于整个品类的总和。这个机制理解到位后很多业务指标都能顺手算出来累计销售额、累计用户数、库存结余、日活峰值。你要做的就是在脑内模拟一遍窗口按分区和排序展开默认边界是我现在看到的这部分聚合函数从分区起点一路累到当前行。如果把这一节浓缩成一句话窗口函数不是对整个表算而是对“当前行所在的那个小集合”算这个小集合的范围由PARTITION BY划定窗口内的顺序由ORDER BY决定窗口的物理边界由行帧控制。3. 三大函数族逐个击破排序、聚合、偏移各管一摊3.1 排序函数ROW_NUMBER、RANK、DENSE_RANK怎么选这三个是窗口函数里出场率最高的。它们做的事很像都是给窗口内的行编号区别在于对并列值的处理方式完全不同。假设有一组数据按销售额排序销售额分别是100、90、90、80三个函数的结果如下销售额ROW_NUMBERRANKDENSE_RANK100111902229032280443ROW_NUMBER()纯粹按物理顺序编号不管值是否相同结果永远是1、2、3、4不会出现并列。RANK()值相同时并列排名但下一个排名会跳过。90并列第280直接排到第4中间空出第3。DENSE_RANK()并列时也排名但排名连续不跳号。90并列第280排第3。选型的基本逻辑是这样的业务上需要给每行一个唯一序号比如分页、去重、打标签用ROW_NUMBER()业务是比赛排名、业绩排行希望并列名次空出后续位置用RANK()业务希望并列名次不产生空档比如“等级评定”这种场景用DENSE_RANK()。实际写代码时我经常遇到一个需求经常会有人用ROW_NUMBER()来做每组取N条但忽略并列问题。如果你的业务是“每个班取成绩最好的两位学生”但并列第一特别多ROW_NUMBER()只会随机或按物理顺序保留其中一个并列者这就不符合语义了。这时应该考虑RANK()或DENSE_RANK()但要接受结果行数可能超过N。取“前N行”和取“排名前N”是两种不同的业务需求选函数前必须先跟业务方确认。3.2 聚合函数SUM、AVG在窗口内的累加与移动计算SUM、AVG、MIN、MAX、COUNT这些聚合函数放进窗口里玩法就完全不一样了。它们在窗口内对每一行都执行一次聚合结果附加到该行旁边。最经典的用法是累计求和。上一节我已经给了例子这里再补充一个移动平均的场景。假设要算每个商品最近3天的日均销售额SELECT product_id, sale_date, amount, AVG(amount) OVER( PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_3d FROM sales;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW把窗口限定为当前行及前面两行这样AVG算出来的就是三天移动平均。如果你想做更长时间窗口把这个2改成对应的数字即可。移动平均在金融、电商、运营数据分析中非常常见用于平滑短期波动观察趋势。窗口聚合的另一个实用场景是算占比SELECT product_id, amount, amount / SUM(amount) OVER(PARTITION BY category_id) AS category_share FROM sales;这个SQL给每个商品算出“它在自己品类里的销售额占比”。如果不用窗口函数你得先GROUP BY算品类总额再JOIN回明细表步骤繁杂。窗口函数一个OVER(PARTITION BY category_id)直接解决。3.3 偏移函数LAG、LEAD做环比和前后对比LAG和LEAD用来获取窗口内当前行的上一行或下一行的某个字段值是做环比、同比、前后对比的核心工具。LAG(column, n, default)取窗口内当前行往前数n行的column值n默认1取不到时返回default不写default则返回NULL。LEAD(column, n, default)取窗口内当前行往后数n行的column值参数含义同上。一个经典的环比示例计算每个商品每日销售额相比前一天的增长率。WITH daily_sales AS ( SELECT product_id, sale_date, SUM(amount) AS day_amount FROM sales GROUP BY product_id, sale_date ) SELECT product_id, sale_date, day_amount, LAG(day_amount) OVER(PARTITION BY product_id ORDER BY sale_date) AS prev_day_amount, ROUND( (day_amount - LAG(day_amount) OVER(PARTITION BY product_id ORDER BY sale_date)) / NULLIF(LAG(day_amount) OVER(PARTITION BY product_id ORDER BY sale_date), 0) * 100, 2 ) AS growth_rate FROM daily_sales;这里先用CTE把数据按天聚合再用LAG取前一天的值最后算增长率。NULLIF是为了避免除零错误。注意LAG在窗口里出现了三次代码看起来有些冗余你也可以在外面再套一层查询把prev_day_amount作为中间列一次算好外层再计算增长率。CTE的写法胜在可读性和可维护性。FIRST_VALUE和LAST_VALUE也是偏移取值类的常用函数分别取窗口内的第一个值和最后一个值。比如“对比当前行与部门最高薪水的差距”就可以用MAX(salary) OVER(PARTITION BY dept)或者FIRST_VALUE(salary) OVER(PARTITION BY dept ORDER BY salary DESC)。不过LAST_VALUE有个著名的坑我在第5章会专门拆解。4. 三步拆解法把复杂需求翻译成OVER子句4.1 第一步定分区第二步定顺序第三步定计算接触窗口函数一段时间后我发现大部分需求都可以用一个固定套路拆解我把它总结成三步定分区、定顺序、定计算方式。第一步想清楚“跟谁比”。找出业务上需要独立计算的那个维度。比如“每个品类内做排名”分区就是category_id“每个用户计算累计消费”分区就是user_id。“跟谁比”决定PARTITION BY后面写什么。这一步非常关键分区定错了结果全盘皆输。第二步想清楚“按什么排”。窗口内按什么字段、什么顺序进行计算。排名类需求看排序键累计类需求按时间演进环比类需求按时间找前后行。“按什么排”决定ORDER BY的内容和方向。这里还要注意同一个分区内排序键是否唯一如果不唯一要考虑并列以及默认RANGE帧带来的影响。第三步想清楚“算什么”。是一个序号一个累计值一个移动平均还是上一行某个字段的值这一步决定用哪个函数以及要不要显式声明行帧。如果需要限制窗口范围就在这里写完整的ROWS BETWEEN子句如果只需要默认累计范围那就不写行帧让数据库按默认行为执行。这三步走完窗口函数的骨架就出来了函数(字段) OVER(PARTITION BY 分区字段 ORDER BY 排序字段 行帧子句)。4.2 实战案例品类销售分析同时算排名、累计和环比用一个综合案例把三步法串起来。业务方给了一张sales_detail表字段包括category_id、product_id、trade_date、amount现在要输出一份报表每一行是一个商品在某天的销售记录同时包含三列这个商品当天在自己的品类里的销售排名这个商品自上线以来的累计销售额这个商品前一天的销售额以及环比增长率。按照三步法拆解定分区排名按category_id, trade_date分区累计按product_id分区环比也按product_id分区定顺序排名按amount DESC累计和环比按trade_date定计算排名用ROW_NUMBER()业务确认只要物理前几累计用SUM(amount)前一天用LAG(amount, 1)。SQL可以这样组织WITH enriched AS ( SELECT category_id, product_id, trade_date, amount, ROW_NUMBER() OVER( PARTITION BY category_id, trade_date ORDER BY amount DESC, product_id ) AS day_rank_in_category, SUM(amount) OVER( PARTITION BY product_id ORDER BY trade_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount, LAG(amount, 1) OVER( PARTITION BY product_id ORDER BY trade_date ) AS prev_day_amount FROM sales_detail ) SELECT category_id, product_id, trade_date, amount, day_rank_in_category, cumulative_amount, prev_day_amount, ROUND( (amount - prev_day_amount) / NULLIF(prev_day_amount, 0) * 100, 2 ) AS day_over_day_growth FROM enriched ORDER BY category_id, trade_date, day_rank_in_category;注意几个细节。排名那里我加了product_id作为第二排序键这是为了防止金额相同的情况下ROW_NUMBER()的物理顺序不稳定加上一个确定性排序键能让结果可复现。累计那里我显式写了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW虽然这是默认帧但写出来之后阅读代码的人一眼就能看出意图可读性更好。环比计算放在外层而不是直接在enriched里算是因为LAG已经取过一次值再嵌套一层更清爽也避免在同一个SELECT里重复写三次LAG。4.3 进阶案例连续登录天数的窗口函数解法再举一个窗口函数的高频考题给定用户登录记录表求每个用户连续登录的最大天数。核心思路是用ROW_NUMBER()给每个用户的登录记录按日期排序然后用登录日期减去序号得到一个分组标识。如果登录是连续的日期减去序号的结果是同一个值一旦中断这个值就会跳变。按这个分组标识聚合就能算出每段连续登录的天数。WITH login_history AS ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ), numbered AS ( SELECT user_id, login_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS rn, login_date - ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS grp FROM login_history ) SELECT user_id, MIN(login_date) AS continuous_start, MAX(login_date) AS continuous_end, COUNT(*) AS continuous_days FROM numbered GROUP BY user_id, grp ORDER BY user_id, continuous_start;第一步先去重保证同一个用户同一天只出现一次第二步用窗口函数给每行编号计算登录日期和编号的差值第三步按用户和差值分组一组就是一段连续登录区间。这个题目考察的核心其实不是窗口函数本身而是“用窗口函数生成分组键”这个思路属于典型的窗口函数进阶用法。理解了这个案例你就掌握了窗口函数在辅助生成新分组维度上的灵活用法。5. 生产环境常见的五个坑根因和修复姿势5.1 ROWS和RANGE的边界差异是最隐蔽的坑窗口函数的帧类型有两种ROWS和RANGE。两者的差异在于ROWS按物理行数定位当前行前面两行就是前面两行不看行内的值RANGE按值定位窗口边界由ORDER BY字段的值决定排序键相同的行会被一并纳入窗口。这个差异在排序键有重复值时会被放大而且默认行为就是RANGE。举个例子下面这条SQLSELECT sale_date, amount, SUM(amount) OVER(ORDER BY sale_date) AS cum_amount FROM sales;如果10月1日有多笔销售默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW那么在10月1日的每一行窗口都会把10月1日所有的销售都包含进去而不是只包含当前物理行之前的行。结果是10月1日的所有行拥有相同的累计值这个累计值已经包含了整天的全部销售额。如果改成ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW则是逐行累加物理行10月1日的第一行只包含第一行销售额第二行包含前两行销售额依此类推。很多报表算累计值算到一半发现“数对不上”排查半天查不出原因问题往往就出在这里。判断标准很简单如果你的排序键可能重复并且业务上希望累计值跟随物理行逐个增长就必须显式写ROWS帧如果业务上希望同一天的记录共享同一个累计值那就用默认的RANGE帧。这个选择没有绝对的对错但一定要明确语义后再写SQL。5.2 LAST_VALUE取不到末尾因为默认帧只到当前行LAST_VALUE是一个看起来应该“取窗口内最后一行”的函数但实际结果经常让人懵。原因还是默认帧。如果你写SELECT department, employee_name, salary, LAST_VALUE(employee_name) OVER( PARTITION BY department ORDER BY salary ) AS lowest_salary_employee FROM employees;你以为会拿到整个部门薪水最低的人实际上每一行返回的是当前行自己。为什么因为默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW窗口的上边界是当前行所以“窗口内最后一行”就是当前行本身。LAST_VALUE在这个默认帧下毫无意义。修复方法有两种。一种是把帧显式扩展到整个分区LAST_VALUE(employee_name) OVER( PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS lowest_salary_employee另一种更省事既然要取整个分区的最小值直接用聚合函数MIN(employee_name) OVER(PARTITION BY department)但要注意对字符串求MIN取到的是字典序最小的名字不是薪水最低的人所以如果目标取的是“薪水最低的人的名字”还是要用FIRST_VALUE按薪水正序取第一个或者LAST_VALUE配全帧。这里我的建议是能用FIRST_VALUE配合ORDER BY方向解决就尽量别依赖LAST_VALUE和长帧逻辑清晰且不容易出错。5.3 排名并列带来的行数问题取前三和排名前三不一样排名的坑主要体现在结果行数上。还是那个老问题业务说要“取每个品类销售额前三的商品”到底是要“物理上的3行”还是“排名小于等于3的所有商品”两者在数据没有并列时结果一样一旦出现并列销售额差异立刻显现。ROW_NUMBER()帮你稳定地取3行但遇到并列时它必须自己决定谁排第二、谁排第三这个决定在业务上往往是随机的RANK()或DENSE_RANK()能保证所有并列者都进入结果但如果并列太多返回行数可能远超3行。我的处理经验是在写SQL前先跟业务方把需求里的“前三”确认清楚。如果是“排行榜单只显示三条数据”用ROW_NUMBER()如果是“所有达到前三名成绩的人都算数”用DENSE_RANK()或RANK()。这个确认动作看起来多余实际上能避免上线后数据对不上号的尴尬。如果你在SQL里看到同事用RANK()实现了“每个组取前3”而外层又写了WHERE rn 3结果返回了5行不要惊讶这就是并列名次的正常表现。5.4 窗口函数不能直接出现在WHERE里这个坑前面提过一次但因为它太常见值得单独拎出来。窗口函数是在SELECT阶段计算的它的结果在WHERE执行时根本不存在所以直接写在WHERE里会报错。印象里最常见的报错是窗口函数不允许出现在WHERE子句。正确姿势是套一层子查询或CTESELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) AS rn FROM sales ) t WHERE t.rn 1;注意窗口函数也不能直接用在HAVING里原因类似。如果你想基于窗口函数结果做过滤唯一的办法就是先计算出窗口函数的结果列再在外层过滤。这个模式在写复杂统计时几乎天天用建议形成肌肉记忆。5.5 性能排查全表排序和无效分区是重灾区窗口函数性能问题的根源通常逃不开两个原因全表排序和不合理的分区设计。用EXPLAIN看执行计划时窗口函数通常会出现WindowAgg节点它前面经常跟着一个Sort节点。如果这个Sort没有走索引或者排序的数据量特别大查询就会明显变慢。一个常见例子是在同一张千万级大表上不做任何过滤直接SUM(amount) OVER(PARTITION BY category_id ORDER BY sale_date)数据库需要把全表数据按品类和日期做一次完整排序耗时长内存压力也大。缓解思路有这么几条先过滤再开窗。用WHERE把数据范围缩小到需要计算的集合尽量减少进入窗口函数的数据量。比如只算最近30天的数据就先WHERE掉三个月前的历史记录。分区字段不要选基数过大的列。分区本质上是用哈希或排序把数据分成若干组如果分区键唯一值特别多每个分区只有一两行窗口函数几乎退化成逐行运算索引优势也没了。反而选一些业务语义清晰的维度比如品类、区域、用户分组效果更好。不要对一个大窗口反复排序。如果同一个查询里写了三个窗口函数但它们的PARTITION BY和ORDER BY完全一样部分数据库可以复用排序结果。写法上尽量把相同窗口定义提取出来或者干脆在一个子查询里算好所有需要的结果列减少重复扫描。数据量实在太大时考虑预聚合。窗口函数适合分析型查询但如果每天的跑批任务是先对明细做累计再拿累计结果去做下一步计算不如先物化成中间表减少重复计算。有一次我在生产环境排查慢查询发现一个报表的窗口函数执行时间占了总耗时80%看执行计划WindowAgg对一张5000万行的表做了全量排序。后来在查询里加了一个时间范围过滤把数据量缩到300万行执行时间从40秒降到了3秒。这个优化思路其实和普通SQL一样尽量减少数据进入高成本算子之前的数据量。6. 不同数据库的差异版本选型和SQL迁移避坑6.1 MySQL 8.0之前怎么模拟窗口函数窗口函数在MySQL 8.0才正式支持8.0之前的版本比如大量生产环境还在用的5.7只有GROUP BY和用户变量可用。如果你在维护老项目会经常看到类似下面这种用用户变量模拟行号的写法SET rn : 0; SET cat : ; SELECT category_id, product_id, amount, rn : IF(cat category_id, rn 1, 1) AS rn, cat : category_id AS current_category FROM sales ORDER BY category_id, amount DESC;这段代码的逻辑是按品类和金额排序后遍历每一行如果品类相同就把编号加一品类变了就把编号重置为1。它确实能在5.7上模拟出ROW_NUMBER()的效果但有两个致命弱点结果完全依赖ORDER BY的执行顺序如果排序不稳定编号就会错乱变量赋值和读取在同一个SELECT里依赖MySQL特定的执行细节换个版本或换台机器结果可能不同。所以后来我接手老项目时只要发现这种写法出现在核心报表中都会建议推动升级到MySQL 8.0或者在迁移到8.0后第一时间把这类写法替换成标准窗口函数。迁移本身不复杂但替换时要注意老写法依赖物理顺序新写法要显式指定ORDER BY语义要重新核对一遍。6.2 PostgreSQL的FILTER和GROUPS扩展PostgreSQL对窗口函数的支持比MySQL早能力也更丰富。两个特性在实际使用中价值很高。第一个是FILTER子句。它允许在窗口聚合时只对满足条件的行进行聚合SELECT category_id, sale_date, SUM(amount) FILTER (WHERE amount 1000) OVER( PARTITION BY category_id ) AS high_value_amount FROM sales;这条SQL在计算每个品类的窗口总额时只统计金额大于1000的行。在MySQL里没有FILTER语法需要用CASE WHEN来模拟SUM(CASE WHEN amount 1000 THEN amount ELSE 0 END) OVER( PARTITION BY category_id ) AS high_value_amount第二种写法其实对所有数据库都通用所以在写跨库兼容的SQL时CASE WHEN是更稳妥的方案。第二个特性是GROUPS帧类型。ROWS按物理行数定位RANGE按值定位GROUPS则按排序键的分组数定位比如GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING表示当前排序键值的前一组、当前组、后一组。这个帧类型在分析“同组比较”场景下非常方便但不是所有数据库都支持迁移到MySQL时要特别注意改写。6.3 SQL Server和云数据库的注意事项SQL Server从2005版开始支持窗口函数比MySQL早了十几年所以很多老数据库的窗口函数实践经验都沉淀在SQL Server社区里。使用SQL Server时有一个重要的版本提示老版本2012之前不支持LAG和LEAD当时要做环比只能靠ROW_NUMBER自连接实现代码非常绕。从2012版开始偏移函数才正式加入如果你还在维护SQL Server 2008 R2的项目建议写代码前先确认目标语法版本。SQL Server 2022引入了IGNORE NULLS选项可以在LAG、LEAD、FIRST_VALUE、LAST_VALUE中跳过NULL值取下一个非NULL值这个能力在填补缺失数据时很实用。PostgreSQL的LAG也支持IGNORE NULLS而MySQL目前还不支持迁移时需要用CASE WHEN或子查询实现类似逻辑。不同数据库在窗口函数上的支持情况我整理了一份简表能力MySQL 8.0PostgreSQL 14SQL Server 2019基础窗口函数支持支持支持FILTER子句不支持支持不支持GROUPS帧类型不支持支持部分支持IGNORE NULLS不支持LAG/LEAD支持2022起支持聚合函数嵌套窗口支持支持支持跨数据库迁移时最稳妥的策略是把窗口函数的语法限定在所有目标数据库的公共子集内。具体来说就是少用FILTER和GROUPS用CASE WHEN和ROWS替代不用IGNORE NULLS用子查询预清洗数据实现同样的效果。这样写出来的SQL基本可以在主流关系型数据库之间平滑迁移。6.4 我在实际项目中的几个使用习惯窗口函数用久了我养成了几个固定的习惯。第一复杂查询优先用CTE组织窗口函数。每个窗口计算的结果都放进同一个CTE里给列起一个能表达业务含义的名字比如day_rank、cumulative_amount外层再引用这些列做过滤、排序或进一步计算。这样做的好处是每一层只做一件事排查问题时按CTE逐段验证哪里出错一眼就能定位。第二窗口函数的结果尽量在计算时就把边界写清楚。除了标准的排名和累计场景只要窗口聚合涉及行的范围我都会显式写ROWS BETWEEN ...不依赖默认帧。这不是为了炫耀语法而是防止未来排序键出现重复值时行为发生变化。第三写窗口函数之前先问自己三个问题分区对吗、排序对吗、帧对吗。这三个问题问完大概率的坑都能提前避开。窗口函数本身不难难的是对窗口范围的直觉。我见过很多线上事故最后定位下来不是函数写错了而是窗口边界比想象中宽或者窄了一截。数据量越大这种边界问题越难发现所以提前把边界想清楚比事后排查要省心得多。