1. 为什么说窗口函数是保留明细的GROUP BY1.1 自连接实现累计的笨办法先回到一个最常见的需求算累计销售额。比如有一张销售明细表sale_record字段是id、day_seq第几天、amount金额在窗口函数普及之前大家写累计的逻辑基本都是自连接SELECT a.id, a.day_seq, a.amount, SUM(b.amount) AS cum_amount FROM sale_record a JOIN sale_record b ON b.day_seq a.day_seq OR (b.day_seq a.day_seq AND b.id a.id) GROUP BY a.id, a.day_seq, a.amount ORDER BY a.day_seq, a.id;这段SQL的问题非常明显。第一它要求表里必须存在一个能区分行先后顺序的唯一键否则day_seq相同的多行记录会在JOIN时互相膨胀累计值直接翻倍第二随着表数据量增长这种自连接是典型的O(n²)操作几万行数据跑起来就开始难受了第三代码的可读性很差三个月后回头看你大概率要盯着ON条件琢磨半天。当年MySQL 5.7时代还有用变量模拟窗口函数的土办法写法更绕而且对索引和行序极其敏感换个执行计划结果可能就变了。窗口函数把这件事变成了一行代码SELECT id, day_seq, amount, SUM(amount) OVER (ORDER BY day_seq, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount FROM sale_record;1.2 GROUP BY做不到的事情窗口函数和GROUP BY的核心区别一句话就能讲透GROUP BY会压缩行数窗口函数不会。GROUP BYday_seq得到的是每天一行汇总你丢掉了每笔订单的明细信息。而窗口函数在每一行旁边多返回一列聚合结果行数一条不少。这个特性决定了它特别适合三类业务排名类ROW_NUMBER、RANK、DENSE_RANK、NTILE典型场景是每个区域按销售额排名每门课取前三名。取值类LAG、LEAD、FIRST_VALUE、LAST_VALUE典型场景是环比上一周期的值取组内最早一条记录的值。聚合类SUM、AVG、COUNT、MIN、MAX典型场景是累计求和、移动平均、组内占比。对聚合类窗口函数来说真正决定结果的是OVER子句里的窗口范围Window Frame也就是ROWS和RANGE。很多初学者把窗口函数写错不是函数本身不会而是没搞懂这两个词的语义。1.3 OVER子句的四个组成部分一个完整的OVER子句由四部分组成OVER ( PARTITION BY col1 -- 分区按什么分组可省略 ORDER BY col2 -- 排序窗口内按什么排可省略 ROWS BETWEEN ... AND ... -- 窗口范围圈定参与计算的行可省略 -- 还可能有 EXCLUDE ... -- 排除某些行部分数据库支持 )这里有个非常关键的默认值逻辑PARTITION BY省略整个结果集就是一个分区相当于没有分组。ORDER BY省略窗口内没有排序此时默认窗口范围是整个分区。只要写了ORDER BY窗口范围默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW也就是从分区起点到当前行。最后一条是无数人踩坑的根源后面我会专门展开说。先把这条记在脑子里有ORDER BY和没有ORDER BY同样一个SUM窗口函数结果逻辑完全不同。2. ROWS按行圈窗口RANGE按值圈窗口一张表讲透核心差异2.1 同一份数据两种窗口跑出不同的结果为了把差异讲到明处我用一份非常简单的数据做演示。创建表并插入数据CREATE TABLE sale_record ( id INT PRIMARY KEY, day_seq INT NOT NULL, amount DECIMAL(10,2) NOT NULL ); INSERT INTO sale_record VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150), (4, 3, 300), (5, 4, 120);两条查询一起跑它们唯一的区别就是窗口范围一个是ROWS BETWEEN 1 PRECEDING AND CURRENT ROW另一个是RANGE BETWEEN 1 PRECEDING AND CURRENT ROWSELECT id, day_seq, amount, SUM(amount) OVER (ORDER BY day_seq ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS rows_sum, SUM(amount) OVER (ORDER BY day_seq RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS range_sum FROM sale_record ORDER BY day_seq, id;结果如下idday_seqamountrows_sumrange_sum1110010030021200300300321503504504330045045054120420420注意看id1和id3这两行两个窗口算出来的结果完全不一样这不是BUG而是两种窗口的语义根本不同。id1这行ROWS窗口只包含它自己因为按物理行数往前数一行前面没有行了所以结果就是100。而RANGE窗口呢当前行的day_seq是1往前1个单位也就是值区间[0,1]内的所有行都要进来。id1和id2的day_seq都是1它们两个是并列行所以一起被圈了进来结果是300。id3这行day_seq是2。ROWS窗口包含id2和id3两行物理相邻行结果是200150350。RANGE窗口呢值区间[1,2]内的行也就是day_seq为1和2的所有行一共三行结果是100200150450。2.2 为什么RANGE会把并列行打包处理这两种窗口的底层逻辑用大白话说就是ROWS是物理窗口它只认行号。BETWEEN 1 PRECEDING AND CURRENT ROW的意思是从当前行往上数1行开始到当前行结束。它不关心排序键的值是多少哪怕排序键是日期、价格、得分它一律当成位置来看待。你可以把它想成排队我前面第3个人是谁跟这个人年龄多大、工资多少没有任何关系。RANGE是逻辑窗口它认的是排序键的数值。BETWEEN 1 PRECEDING AND CURRENT ROW的意思是排序键的值落在[当前行的排序键值 - 1, 当前行的排序键值]这个区间内的所有行。它根本不在乎这些行离当前行隔了几行只在乎值在不在区间里。你可以把它想成按年龄找人我要找年龄跟我相差不超过3岁的人这是一个属性条件不是位置条件。理解了底层逻辑就自然理解了一个重要推论**排序键值相等的行peer rows在RANGE模式下永远是一个整体要么一起进窗口要么一起出窗口。**因为它们落在同一个值区间里数据库无法把其中一行圈进来而把另一行排除掉。这也是RANGE处理并列数据时和ROWS产生差异的根本原因。2.3 排序键类型对RANGE边界格式的硬性要求RANGE既然是基于值来划边界的那么边界偏移量就必须跟排序键的类型匹配。这里有很多数据库方言的坑我先说通用规则排序键是数值类型偏移量直接写数字比如RANGE BETWEEN 5 PRECEDING AND CURRENT ROW。排序键是日期时间类型MySQL里必须写INTERVAL表达式比如RANGE BETWEEN INTERVAL 3 DAY PRECEDING AND CURRENT ROW直接写数字会报错。排序键是字符串、字符类型RANGE的数值偏移基本没有意义很多数据库直接禁止。不同数据库的支持程度差异非常大我实际遇到过的限制整理如下数据库RANGE数值偏移RANGE日期偏移RANGE的FOLLOWING边界MySQL 8.0支持支持须用INTERVAL不支持数值FOLLOWING只支持UNBOUNDED FOLLOWINGPostgreSQL支持支持支持且支持EXCLUDESQL Server完全不支持RANGE只允许UNBOUNDED和CURRENT ROW组合同左不支持这一点在写跨数据库兼容的SQL时特别值得留意。我自己的习惯是能用ROWS表达的窗口优先ROWS只有确实需要按值域划窗口才用RANGE并且先用小数据集验证当前数据库的方言限制。3. ROWS模式实操累计、移动平均和窗口边界的正确姿势3.1 累计求和与累计占比的业务SQLROWS模式最典型的应用就是累计计算。还是用sale_record表我把数据扩充到8行方便演示滑动效果INSERT INTO sale_record VALUES (6, 5, 280), (7, 6, 90), (8, 7, 210);累计求和的标准写法是SELECT id, day_seq, amount, SUM(amount) OVER (ORDER BY day_seq, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount, amount / SUM(amount) OVER () AS amount_ratio FROM sale_record ORDER BY day_seq, id;这里用了两个窗口第一个窗口求累计值UNBOUNDED PRECEDING表示从分区起点开始一直加到当前行第二个窗口是空OVER()没有ORDER BY、没有PARTITION BY默认就是整个分区求和返回每一行都是同一个总数。两者一除就得到每一笔订单占总销售额的比例。累计占比这种指标用GROUP BY做不到因为GROUP BY会把明细行合并用自连接又太慢。ROWS加空OVER()的组合是我日常写报表用得最多的一套。3.2 近N行滑动均值的分步推演移动平均是另一个高频场景。计算近3笔订单的平均金额SELECT id, day_seq, amount, AVG(amount) OVER (ORDER BY day_seq, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3 FROM sale_record ORDER BY day_seq, id;逐行推演一下结果你就能彻底理解ROWS的行为idday_seqamount窗口内的行avg_311100{id1}10021200{id1,id2}15032150{id1,id2,id3}15043300{id2,id3,id4}216.6754120{id3,id4,id5}19065280{id4,id5,id6}233.33注意窗口开头那两行id1前面没有足够的行窗口只有1行id2只有2行。这是ROWS窗口的边界自适应行为——窗口不会因为前面行数不够就越界报错而是有多少算多少。这一点在做移动平均时特别重要它意味着前N-1行的均值天然就是不完整窗口的均值如果你的业务要求前面不足N行时返回NULL需要自己用CASE WHEN加ROW_NUMBER()判断行号后再处理。3.3 UNBOUNDED、CURRENT ROW和数字偏移怎么组合才对ROWS窗口的边界可以自由组合我用一个表格把最常用的几种搭配列出来顺便说清各自的应用场景窗口范围写法含义典型场景ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区首行到当前行累计求和、累计计数ROWS BETWEEN 2 PRECEDING AND CURRENT ROW当前行及前面2行近N笔移动平均ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING从当前行到分区末行反向累计剩余额度、剩余库存ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING当前行前后各2行中心化平滑、局部上下文计算最后一个中心化窗口在数据预测和异常检测里很常用比如算某时刻前后各一段时间内的平均值作为基线。这里有个兼容性提示如果你用RANGE BETWEEN CURRENT ROW AND n FOLLOWING很多数据库尤其MySQL 8.0会直接报错或行为异常但改成ROWS模式就完全没问题。所以涉及FOLLOWING边界时我几乎总是优先用ROWS。4. RANGE模式实操并列排名与时间区间场景4.1 数值Range统计每个价格带内的商品数RANGE真正的用武之地是按值域做统计。举一个商品价格的例子CREATE TABLE product_price ( product_id INT PRIMARY KEY, price INT NOT NULL ); INSERT INTO product_price VALUES (1, 50), (2, 80), (3, 80), (4, 120), (5, 150);需求是对于每个商品统计价格在当前商品价格上下浮动50元范围内的商品数量。这个需求用ROWS根本没法写因为行号和价格之间没有换算关系。用RANGE就是一行SQLSELECT product_id, price, COUNT(*) OVER (ORDER BY price RANGE BETWEEN 50 PRECEDING AND CURRENT ROW) AS band_cnt, COUNT(*) OVER (ORDER BY price ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS row_cnt FROM product_price ORDER BY price, product_id;结果如下product_idpriceband_cntrow_cnt150112802238023412033515023对比一下product_id3和product_id5这两行差异一目了然。product_id3的价格是80RANGE窗口的值区间是[30,80]价格50和两个80都落在里面所以band_cnt2ROWS窗口数的是物理行从当前行往上数2行把价格50、80、80都圈进来了row_cnt3。product_id5的价格是150RANGE窗口值区间是[100,150]只有120和150band_cnt2ROWS窗口还是数物理行把80、120、150三行都算进去了row_cnt3。这就是RANGE最大的价值当你的窗口边界需要跟业务上的值挂钩而不是跟物理行数挂钩时RANGE是唯一正确的选择。价格带、分数段、年龄段这类统计天然就是RANGE的主场。4.2 日期Range近7天订单量与INTERVAL写法RANGE处理时间序列数据时更顺手。比如每个交易日统计近7天的总销量用ROWS你必须保证每天只有一行数据一旦某天缺少记录滑动窗口的天数就对不齐了。而RANGE直接按日期值划窗口每天有没有记录都不影响值区间的计算。MySQL里的语法是这样的SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS last_7_days FROM daily_sales ORDER BY sale_date;INTERVAL 6 DAY PRECEDING加上当前日正好凑成7天的窗口。如果某一天没有销售记录ROWS模式会把这个有数据的日期误当成连续的一天来数行数RANGE模式则严格按日期差来不会算错。同理计算本季度累计近30天活跃用户数这类需求RANGE的写法都比ROWS更贴合业务语义。4.3 RANGE与ROWS选型的一张决策清单被问得多了之后我总结了一张简短的选型清单基本能覆盖日常90%以上的场景排序键有重复值且并列行在业务上应该被当成一个整体来对待选RANGE。窗口边界要按照时间、数量、价格这类数值区间来控制选RANGE。只关心物理上相邻的N行比如最近3笔交易选ROWS。要用到FOLLOWING边界且数据库是MySQL或SQL Server优先选ROWS。要跨多个数据库写通用SQL优先选ROWS兼容性最好。纯粹求整个分区汇总或者累计值两者都行我建议显式写ROWS语义更明确。5. 最容易翻车的四个场景与排查思路5.1 WHERE里直接过滤窗口函数结果我见过无数次这种写法SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employee WHERE rn 3;在MySQL里这个SQL直接报Unknown column rn in where clause。原因在于SQL的执行顺序FROM先加载表WHERE先筛行然后才轮到窗口函数计算最后才是SELECT投影。窗口函数的结果是在WHERE之后才产生的你当然没法在WHERE里直接引用它。正确的做法是包一层子查询或者用CTEWITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employee ) SELECT * FROM ranked WHERE rn 3;如果哪天你发现某个取每组分数的TOP N查询结果总是全表数据先检查是不是忘了包子查询。5.2 有ORDER BY却以为窗口是整个分区这个坑比上一个更隐蔽因为它不报错只是结果感觉不对。比如你写了SELECT id, day_seq, amount, SUM(amount) OVER (ORDER BY day_seq) AS total FROM sale_record;你的本意是求全表金额总和参照我的第1.3节这条SQL实际得到的是累计值因为只要有ORDER BY默认窗口就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。第一行输出的是第一个值不是全表总和越到后面越大。如果不小心你甚至可能把当前行的累计值当成总量去做占比算出来的比例跑偏到天边。想求整个分区的总和同时保留每一行明细我建议这样写SELECT id, day_seq, amount, SUM(amount) OVER () AS total FROM sale_record;不写ORDER BY空OVER()的默认窗口就是整个分区。把这条和累计窗口放在同一个查询里对比一眼就能看出差异。5.3 RANGE的数据库方言差异RANGE看起来语法统一实际各数据库的接受度差别很大。我在2.3节列过一个对比表这里再补一个常见的翻车场景在SQL Server里写RANGE BETWEEN 1 PRECEDING AND CURRENT ROW直接报错因为SQL Server的RANGE不允许数字偏移它只支持UNBOUNDED PRECEDING、CURRENT ROW、UNBOUNDED FOLLOWING这三者的组合。换句话说SQL Server的RANGE在绝大多数情况下只能退化成默认窗口你写它基本没有意义。MySQL则相反RANGE支持数字偏移和INTERVAL日期偏移但不支持RANGE的FOLLOWING数字偏移。我曾在MySQL 8.0上写过RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING直接报了语法错误改成ROWS就正常了。这些限制文档里都有但实际踩到的时候还是会让人一愣。排查的方法是把窗口范围换成最朴素的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW如果结果变了说明是RANGE语义导致的问题如果直接报错那就是数据库不支持该边界写法。5.4 NULL排序值怎么影响窗口边界ORDER BY列的NULL值在窗口计算里很容易制造怪结果。MySQL和PostgreSQL默认把NULL排在最后ASC时SQL Server默认把NULL排在最前。当NULL参与RANGE窗口时它的排序键值无法参与数值比较因此NULL行会自成一个独立的peer组。举个例子ORDER BY price RANGE BETWEEN 50 PRECEDING AND CURRENT ROW如果当前行的price是NULL数据库没法判断NULL减50等于多少窗口会退化成只包含和它同组的那些NULL行。如果你本想把NULL当成0或者当成一个无穷大的值来做价格带统计结果一定跟你预期差很远。我的建议是在进入窗口计算之前先通过COALESCE把NULL转成业务上明确的边界值同时用ORDER BY的NULLS FIRST/NULLS LASTPostgreSQL支持或CASE表达式显式控制NULL行的位置避免把不确定性留给数据库。6. 性能观察与调试习惯几年踩坑后的个人心得6.1 窗口函数的执行代价主要花在哪窗口函数不会减少行数所以它的执行代价主要集中在两个环节分区和排序。数据库为了计算窗口函数通常要把每个分区内的数据按ORDER BY排好序如果没有可利用的索引就会发生filesort。在几十万行数据上做一次移动平均排序时间往往远大于计算时间。我有一条预防性优化原则给窗口函数涉及的排序字段建立合适的索引优先考虑(PARTITION BY字段, ORDER BY字段)的复合索引。这样数据库可以直接利用索引顺序完成分区内的排序省掉一次显式排序。另外RANGE模式因为要动态判断值区间执行开销通常比ROWS更大所以同一个需求能用ROWS表达的我会优先用ROWS。有一点需要提醒窗口函数别嵌套窗口函数。类似SUM(SUM(x) OVER (...)) OVER (...)这种写法不仅可读性差还容易导致数据库做多次重复计算。正确的做法是用CTE或子查询一层层拆开每一步算清楚一个中间结果再喂给下一步。6.2 LAG/LEAD不归窗口范围管这是一个流传很广的误解。很多人以为LAG、LEAD也会受ROWS/RANGE窗口限制其实完全不是。LAG和LEAD只依赖OVER子句里的ORDER BY顺序它们按这个顺序往前或往后取指定位移的行跟frame一点关系都没有。也就是说你写LAG(amount, 1) OVER (ORDER BY day_seq)它取的是排序后当前行前面一行的amount改成ROWS BETWEEN 2 PRECEDING AND CURRENT ROW也好改成RANGE ...也好LAG的结果不会变。真正受窗口范围影响的是聚合函数SUM、AVG、COUNT、MIN、MAX以及FIRST_VALUE、LAST_VALUE、NTH_VALUE这些取值函数。搞清楚哪些函数吃frame、哪些不吃排查问题时能少走很多弯路。6.3 如何用最小复现表排查窗口计算错乱窗口函数的结果一旦不对劲我最常用的排查方法不是对着大表反复改SQL而是三步走第一步取5到8行有代表性的数据手动构造成临时表复现问题。第二步在SELECT里同时输出排序键、ROW_NUMBER()、以及目标窗口结果三者并排看SELECT id, day_seq, amount, ROW_NUMBER() OVER (ORDER BY day_seq, id) AS rn, SUM(amount) OVER (ORDER BY day_seq, id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS w_sum FROM tmp_sale_record;先确认rn的排序和你心里预期一致再去对w_sum的每一行手算。通常问题不是出在窗口函数本身而是出在排序键上——要么排序键有重复值导致并列行的归属和你预想不同要么排序键里混进了NULL。第三步把ORDER BY补一个唯一键比如id再跑一次如果结果变规整了那基本就可以判定是并列值或NULL导致的语义差异再决定用ROWS还是RANGE。这个方法帮我排查过不少凭空多算了一行或者某个分组数据串到隔壁组的诡异问题。窗口函数不像普通查询它依赖分区、排序、边界三重状态任何一个环节变了输出就跟着变。把状态拆开看问题就藏不住。最后分享一个我自己的习惯凡是线上核心报表里的窗口计算我都会把frame显式写出来哪怕默认值恰好就是我要的。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW这行字看着啰嗦但三个月后再翻代码它比隐含在ORDER BY里的默认行为可靠得多。窗口函数的语法本身不复杂复杂的是对边界的理解把每个边界的取舍写清楚代码自己会说话。 
长河君 · 2026/9/13 9:21:01 · 阅读 1.2k SQL窗口函数详解：ROWS与RANGE的区别与应用
1. 为什么说窗口函数是保留明细的GROUP BY1.1 自连接实现累计的笨办法先回到一个最常见的需求算累计销售额。比如有一张销售明细表sale_record字段是id、day_seq第几天、amount金额在窗口函数普及之前大家写累计的逻辑基本都是自连接SELECT a.id, a.day_seq, a.amount, SUM(b.amount) AS cum_amount FROM sale_record a JOIN sale_record b ON b.day_seq a.day_seq OR (b.day_seq a.day_seq AND b.id a.id) GROUP BY a.id, a.day_seq, a.amount ORDER BY a.day_seq, a.id;这段SQL的问题非常明显。第一它要求表里必须存在一个能区分行先后顺序的唯一键否则day_seq相同的多行记录会在JOIN时互相膨胀累计值直接翻倍第二随着表数据量增长这种自连接是典型的O(n²)操作几万行数据跑起来就开始难受了第三代码的可读性很差三个月后回头看你大概率要盯着ON条件琢磨半天。当年MySQL 5.7时代还有用变量模拟窗口函数的土办法写法更绕而且对索引和行序极其敏感换个执行计划结果可能就变了。窗口函数把这件事变成了一行代码SELECT id, day_seq, amount, SUM(amount) OVER (ORDER BY day_seq, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount FROM sale_record;1.2 GROUP BY做不到的事情窗口函数和GROUP BY的核心区别一句话就能讲透GROUP BY会压缩行数窗口函数不会。GROUP BYday_seq得到的是每天一行汇总你丢掉了每笔订单的明细信息。而窗口函数在每一行旁边多返回一列聚合结果行数一条不少。这个特性决定了它特别适合三类业务排名类ROW_NUMBER、RANK、DENSE_RANK、NTILE典型场景是每个区域按销售额排名每门课取前三名。取值类LAG、LEAD、FIRST_VALUE、LAST_VALUE典型场景是环比上一周期的值取组内最早一条记录的值。聚合类SUM、AVG、COUNT、MIN、MAX典型场景是累计求和、移动平均、组内占比。对聚合类窗口函数来说真正决定结果的是OVER子句里的窗口范围Window Frame也就是ROWS和RANGE。很多初学者把窗口函数写错不是函数本身不会而是没搞懂这两个词的语义。1.3 OVER子句的四个组成部分一个完整的OVER子句由四部分组成OVER ( PARTITION BY col1 -- 分区按什么分组可省略 ORDER BY col2 -- 排序窗口内按什么排可省略 ROWS BETWEEN ... AND ... -- 窗口范围圈定参与计算的行可省略 -- 还可能有 EXCLUDE ... -- 排除某些行部分数据库支持 )这里有个非常关键的默认值逻辑PARTITION BY省略整个结果集就是一个分区相当于没有分组。ORDER BY省略窗口内没有排序此时默认窗口范围是整个分区。只要写了ORDER BY窗口范围默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW也就是从分区起点到当前行。最后一条是无数人踩坑的根源后面我会专门展开说。先把这条记在脑子里有ORDER BY和没有ORDER BY同样一个SUM窗口函数结果逻辑完全不同。2. ROWS按行圈窗口RANGE按值圈窗口一张表讲透核心差异2.1 同一份数据两种窗口跑出不同的结果为了把差异讲到明处我用一份非常简单的数据做演示。创建表并插入数据CREATE TABLE sale_record ( id INT PRIMARY KEY, day_seq INT NOT NULL, amount DECIMAL(10,2) NOT NULL ); INSERT INTO sale_record VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150), (4, 3, 300), (5, 4, 120);两条查询一起跑它们唯一的区别就是窗口范围一个是ROWS BETWEEN 1 PRECEDING AND CURRENT ROW另一个是RANGE BETWEEN 1 PRECEDING AND CURRENT ROWSELECT id, day_seq, amount, SUM(amount) OVER (ORDER BY day_seq ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS rows_sum, SUM(amount) OVER (ORDER BY day_seq RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS range_sum FROM sale_record ORDER BY day_seq, id;结果如下idday_seqamountrows_sumrange_sum1110010030021200300300321503504504330045045054120420420注意看id1和id3这两行两个窗口算出来的结果完全不一样这不是BUG而是两种窗口的语义根本不同。id1这行ROWS窗口只包含它自己因为按物理行数往前数一行前面没有行了所以结果就是100。而RANGE窗口呢当前行的day_seq是1往前1个单位也就是值区间[0,1]内的所有行都要进来。id1和id2的day_seq都是1它们两个是并列行所以一起被圈了进来结果是300。id3这行day_seq是2。ROWS窗口包含id2和id3两行物理相邻行结果是200150350。RANGE窗口呢值区间[1,2]内的行也就是day_seq为1和2的所有行一共三行结果是100200150450。2.2 为什么RANGE会把并列行打包处理这两种窗口的底层逻辑用大白话说就是ROWS是物理窗口它只认行号。BETWEEN 1 PRECEDING AND CURRENT ROW的意思是从当前行往上数1行开始到当前行结束。它不关心排序键的值是多少哪怕排序键是日期、价格、得分它一律当成位置来看待。你可以把它想成排队我前面第3个人是谁跟这个人年龄多大、工资多少没有任何关系。RANGE是逻辑窗口它认的是排序键的数值。BETWEEN 1 PRECEDING AND CURRENT ROW的意思是排序键的值落在[当前行的排序键值 - 1, 当前行的排序键值]这个区间内的所有行。它根本不在乎这些行离当前行隔了几行只在乎值在不在区间里。你可以把它想成按年龄找人我要找年龄跟我相差不超过3岁的人这是一个属性条件不是位置条件。理解了底层逻辑就自然理解了一个重要推论**排序键值相等的行peer rows在RANGE模式下永远是一个整体要么一起进窗口要么一起出窗口。**因为它们落在同一个值区间里数据库无法把其中一行圈进来而把另一行排除掉。这也是RANGE处理并列数据时和ROWS产生差异的根本原因。2.3 排序键类型对RANGE边界格式的硬性要求RANGE既然是基于值来划边界的那么边界偏移量就必须跟排序键的类型匹配。这里有很多数据库方言的坑我先说通用规则排序键是数值类型偏移量直接写数字比如RANGE BETWEEN 5 PRECEDING AND CURRENT ROW。排序键是日期时间类型MySQL里必须写INTERVAL表达式比如RANGE BETWEEN INTERVAL 3 DAY PRECEDING AND CURRENT ROW直接写数字会报错。排序键是字符串、字符类型RANGE的数值偏移基本没有意义很多数据库直接禁止。不同数据库的支持程度差异非常大我实际遇到过的限制整理如下数据库RANGE数值偏移RANGE日期偏移RANGE的FOLLOWING边界MySQL 8.0支持支持须用INTERVAL不支持数值FOLLOWING只支持UNBOUNDED FOLLOWINGPostgreSQL支持支持支持且支持EXCLUDESQL Server完全不支持RANGE只允许UNBOUNDED和CURRENT ROW组合同左不支持这一点在写跨数据库兼容的SQL时特别值得留意。我自己的习惯是能用ROWS表达的窗口优先ROWS只有确实需要按值域划窗口才用RANGE并且先用小数据集验证当前数据库的方言限制。3. ROWS模式实操累计、移动平均和窗口边界的正确姿势3.1 累计求和与累计占比的业务SQLROWS模式最典型的应用就是累计计算。还是用sale_record表我把数据扩充到8行方便演示滑动效果INSERT INTO sale_record VALUES (6, 5, 280), (7, 6, 90), (8, 7, 210);累计求和的标准写法是SELECT id, day_seq, amount, SUM(amount) OVER (ORDER BY day_seq, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount, amount / SUM(amount) OVER () AS amount_ratio FROM sale_record ORDER BY day_seq, id;这里用了两个窗口第一个窗口求累计值UNBOUNDED PRECEDING表示从分区起点开始一直加到当前行第二个窗口是空OVER()没有ORDER BY、没有PARTITION BY默认就是整个分区求和返回每一行都是同一个总数。两者一除就得到每一笔订单占总销售额的比例。累计占比这种指标用GROUP BY做不到因为GROUP BY会把明细行合并用自连接又太慢。ROWS加空OVER()的组合是我日常写报表用得最多的一套。3.2 近N行滑动均值的分步推演移动平均是另一个高频场景。计算近3笔订单的平均金额SELECT id, day_seq, amount, AVG(amount) OVER (ORDER BY day_seq, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3 FROM sale_record ORDER BY day_seq, id;逐行推演一下结果你就能彻底理解ROWS的行为idday_seqamount窗口内的行avg_311100{id1}10021200{id1,id2}15032150{id1,id2,id3}15043300{id2,id3,id4}216.6754120{id3,id4,id5}19065280{id4,id5,id6}233.33注意窗口开头那两行id1前面没有足够的行窗口只有1行id2只有2行。这是ROWS窗口的边界自适应行为——窗口不会因为前面行数不够就越界报错而是有多少算多少。这一点在做移动平均时特别重要它意味着前N-1行的均值天然就是不完整窗口的均值如果你的业务要求前面不足N行时返回NULL需要自己用CASE WHEN加ROW_NUMBER()判断行号后再处理。3.3 UNBOUNDED、CURRENT ROW和数字偏移怎么组合才对ROWS窗口的边界可以自由组合我用一个表格把最常用的几种搭配列出来顺便说清各自的应用场景窗口范围写法含义典型场景ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区首行到当前行累计求和、累计计数ROWS BETWEEN 2 PRECEDING AND CURRENT ROW当前行及前面2行近N笔移动平均ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING从当前行到分区末行反向累计剩余额度、剩余库存ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING当前行前后各2行中心化平滑、局部上下文计算最后一个中心化窗口在数据预测和异常检测里很常用比如算某时刻前后各一段时间内的平均值作为基线。这里有个兼容性提示如果你用RANGE BETWEEN CURRENT ROW AND n FOLLOWING很多数据库尤其MySQL 8.0会直接报错或行为异常但改成ROWS模式就完全没问题。所以涉及FOLLOWING边界时我几乎总是优先用ROWS。4. RANGE模式实操并列排名与时间区间场景4.1 数值Range统计每个价格带内的商品数RANGE真正的用武之地是按值域做统计。举一个商品价格的例子CREATE TABLE product_price ( product_id INT PRIMARY KEY, price INT NOT NULL ); INSERT INTO product_price VALUES (1, 50), (2, 80), (3, 80), (4, 120), (5, 150);需求是对于每个商品统计价格在当前商品价格上下浮动50元范围内的商品数量。这个需求用ROWS根本没法写因为行号和价格之间没有换算关系。用RANGE就是一行SQLSELECT product_id, price, COUNT(*) OVER (ORDER BY price RANGE BETWEEN 50 PRECEDING AND CURRENT ROW) AS band_cnt, COUNT(*) OVER (ORDER BY price ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS row_cnt FROM product_price ORDER BY price, product_id;结果如下product_idpriceband_cntrow_cnt150112802238023412033515023对比一下product_id3和product_id5这两行差异一目了然。product_id3的价格是80RANGE窗口的值区间是[30,80]价格50和两个80都落在里面所以band_cnt2ROWS窗口数的是物理行从当前行往上数2行把价格50、80、80都圈进来了row_cnt3。product_id5的价格是150RANGE窗口值区间是[100,150]只有120和150band_cnt2ROWS窗口还是数物理行把80、120、150三行都算进去了row_cnt3。这就是RANGE最大的价值当你的窗口边界需要跟业务上的值挂钩而不是跟物理行数挂钩时RANGE是唯一正确的选择。价格带、分数段、年龄段这类统计天然就是RANGE的主场。4.2 日期Range近7天订单量与INTERVAL写法RANGE处理时间序列数据时更顺手。比如每个交易日统计近7天的总销量用ROWS你必须保证每天只有一行数据一旦某天缺少记录滑动窗口的天数就对不齐了。而RANGE直接按日期值划窗口每天有没有记录都不影响值区间的计算。MySQL里的语法是这样的SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS last_7_days FROM daily_sales ORDER BY sale_date;INTERVAL 6 DAY PRECEDING加上当前日正好凑成7天的窗口。如果某一天没有销售记录ROWS模式会把这个有数据的日期误当成连续的一天来数行数RANGE模式则严格按日期差来不会算错。同理计算本季度累计近30天活跃用户数这类需求RANGE的写法都比ROWS更贴合业务语义。4.3 RANGE与ROWS选型的一张决策清单被问得多了之后我总结了一张简短的选型清单基本能覆盖日常90%以上的场景排序键有重复值且并列行在业务上应该被当成一个整体来对待选RANGE。窗口边界要按照时间、数量、价格这类数值区间来控制选RANGE。只关心物理上相邻的N行比如最近3笔交易选ROWS。要用到FOLLOWING边界且数据库是MySQL或SQL Server优先选ROWS。要跨多个数据库写通用SQL优先选ROWS兼容性最好。纯粹求整个分区汇总或者累计值两者都行我建议显式写ROWS语义更明确。5. 最容易翻车的四个场景与排查思路5.1 WHERE里直接过滤窗口函数结果我见过无数次这种写法SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employee WHERE rn 3;在MySQL里这个SQL直接报Unknown column rn in where clause。原因在于SQL的执行顺序FROM先加载表WHERE先筛行然后才轮到窗口函数计算最后才是SELECT投影。窗口函数的结果是在WHERE之后才产生的你当然没法在WHERE里直接引用它。正确的做法是包一层子查询或者用CTEWITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employee ) SELECT * FROM ranked WHERE rn 3;如果哪天你发现某个取每组分数的TOP N查询结果总是全表数据先检查是不是忘了包子查询。5.2 有ORDER BY却以为窗口是整个分区这个坑比上一个更隐蔽因为它不报错只是结果感觉不对。比如你写了SELECT id, day_seq, amount, SUM(amount) OVER (ORDER BY day_seq) AS total FROM sale_record;你的本意是求全表金额总和参照我的第1.3节这条SQL实际得到的是累计值因为只要有ORDER BY默认窗口就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。第一行输出的是第一个值不是全表总和越到后面越大。如果不小心你甚至可能把当前行的累计值当成总量去做占比算出来的比例跑偏到天边。想求整个分区的总和同时保留每一行明细我建议这样写SELECT id, day_seq, amount, SUM(amount) OVER () AS total FROM sale_record;不写ORDER BY空OVER()的默认窗口就是整个分区。把这条和累计窗口放在同一个查询里对比一眼就能看出差异。5.3 RANGE的数据库方言差异RANGE看起来语法统一实际各数据库的接受度差别很大。我在2.3节列过一个对比表这里再补一个常见的翻车场景在SQL Server里写RANGE BETWEEN 1 PRECEDING AND CURRENT ROW直接报错因为SQL Server的RANGE不允许数字偏移它只支持UNBOUNDED PRECEDING、CURRENT ROW、UNBOUNDED FOLLOWING这三者的组合。换句话说SQL Server的RANGE在绝大多数情况下只能退化成默认窗口你写它基本没有意义。MySQL则相反RANGE支持数字偏移和INTERVAL日期偏移但不支持RANGE的FOLLOWING数字偏移。我曾在MySQL 8.0上写过RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING直接报了语法错误改成ROWS就正常了。这些限制文档里都有但实际踩到的时候还是会让人一愣。排查的方法是把窗口范围换成最朴素的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW如果结果变了说明是RANGE语义导致的问题如果直接报错那就是数据库不支持该边界写法。5.4 NULL排序值怎么影响窗口边界ORDER BY列的NULL值在窗口计算里很容易制造怪结果。MySQL和PostgreSQL默认把NULL排在最后ASC时SQL Server默认把NULL排在最前。当NULL参与RANGE窗口时它的排序键值无法参与数值比较因此NULL行会自成一个独立的peer组。举个例子ORDER BY price RANGE BETWEEN 50 PRECEDING AND CURRENT ROW如果当前行的price是NULL数据库没法判断NULL减50等于多少窗口会退化成只包含和它同组的那些NULL行。如果你本想把NULL当成0或者当成一个无穷大的值来做价格带统计结果一定跟你预期差很远。我的建议是在进入窗口计算之前先通过COALESCE把NULL转成业务上明确的边界值同时用ORDER BY的NULLS FIRST/NULLS LASTPostgreSQL支持或CASE表达式显式控制NULL行的位置避免把不确定性留给数据库。6. 性能观察与调试习惯几年踩坑后的个人心得6.1 窗口函数的执行代价主要花在哪窗口函数不会减少行数所以它的执行代价主要集中在两个环节分区和排序。数据库为了计算窗口函数通常要把每个分区内的数据按ORDER BY排好序如果没有可利用的索引就会发生filesort。在几十万行数据上做一次移动平均排序时间往往远大于计算时间。我有一条预防性优化原则给窗口函数涉及的排序字段建立合适的索引优先考虑(PARTITION BY字段, ORDER BY字段)的复合索引。这样数据库可以直接利用索引顺序完成分区内的排序省掉一次显式排序。另外RANGE模式因为要动态判断值区间执行开销通常比ROWS更大所以同一个需求能用ROWS表达的我会优先用ROWS。有一点需要提醒窗口函数别嵌套窗口函数。类似SUM(SUM(x) OVER (...)) OVER (...)这种写法不仅可读性差还容易导致数据库做多次重复计算。正确的做法是用CTE或子查询一层层拆开每一步算清楚一个中间结果再喂给下一步。6.2 LAG/LEAD不归窗口范围管这是一个流传很广的误解。很多人以为LAG、LEAD也会受ROWS/RANGE窗口限制其实完全不是。LAG和LEAD只依赖OVER子句里的ORDER BY顺序它们按这个顺序往前或往后取指定位移的行跟frame一点关系都没有。也就是说你写LAG(amount, 1) OVER (ORDER BY day_seq)它取的是排序后当前行前面一行的amount改成ROWS BETWEEN 2 PRECEDING AND CURRENT ROW也好改成RANGE ...也好LAG的结果不会变。真正受窗口范围影响的是聚合函数SUM、AVG、COUNT、MIN、MAX以及FIRST_VALUE、LAST_VALUE、NTH_VALUE这些取值函数。搞清楚哪些函数吃frame、哪些不吃排查问题时能少走很多弯路。6.3 如何用最小复现表排查窗口计算错乱窗口函数的结果一旦不对劲我最常用的排查方法不是对着大表反复改SQL而是三步走第一步取5到8行有代表性的数据手动构造成临时表复现问题。第二步在SELECT里同时输出排序键、ROW_NUMBER()、以及目标窗口结果三者并排看SELECT id, day_seq, amount, ROW_NUMBER() OVER (ORDER BY day_seq, id) AS rn, SUM(amount) OVER (ORDER BY day_seq, id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS w_sum FROM tmp_sale_record;先确认rn的排序和你心里预期一致再去对w_sum的每一行手算。通常问题不是出在窗口函数本身而是出在排序键上——要么排序键有重复值导致并列行的归属和你预想不同要么排序键里混进了NULL。第三步把ORDER BY补一个唯一键比如id再跑一次如果结果变规整了那基本就可以判定是并列值或NULL导致的语义差异再决定用ROWS还是RANGE。这个方法帮我排查过不少凭空多算了一行或者某个分组数据串到隔壁组的诡异问题。窗口函数不像普通查询它依赖分区、排序、边界三重状态任何一个环节变了输出就跟着变。把状态拆开看问题就藏不住。最后分享一个我自己的习惯凡是线上核心报表里的窗口计算我都会把frame显式写出来哪怕默认值恰好就是我要的。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW这行字看着啰嗦但三个月后再翻代码它比隐含在ORDER BY里的默认行为可靠得多。窗口函数的语法本身不复杂复杂的是对边界的理解把每个边界的取舍写清楚代码自己会说话。