SQL窗口函数详解：ROWS与RANGE的区别与应用

1. 为什么说窗口函数是"保留明细的GROUP BY"

1.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 ROW：

SELECT 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;

结果如下：

注意看id=1和id=3这两行，两个窗口算出来的结果完全不一样，这不是BUG，而是两种窗口的语义根本不同。

id=1这行，ROWS窗口只包含它自己，因为按物理行数往前数一行，前面没有行了，所以结果就是100。而RANGE窗口呢？当前行的day_seq是1，往前1个单位，也就是值区间[0,1]内的所有行都要进来。id=1和id=2的day_seq都是1，它们两个是"并列行"，所以一起被圈了进来，结果是300。

id=3这行，day_seq是2。ROWS窗口包含id=2和id=3两行物理相邻行，结果是200+150=350。RANGE窗口呢？值区间[1,2]内的行，也就是day_seq为1和2的所有行，一共三行，结果是100+200+150=450。

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的数值偏移基本没有意义，很多数据库直接禁止。

不同数据库的支持程度差异非常大，我实际遇到过的限制整理如下：

这一点在写跨数据库兼容的SQL时特别值得留意。我自己的习惯是：能用ROWS表达的窗口，优先ROWS；只有确实需要按值域划窗口才用RANGE，并且先用小数据集验证当前数据库的方言限制。

3. ROWS模式实操：累计、移动平均和窗口边界的正确姿势

3.1 累计求和与累计占比的业务SQL

ROWS模式最典型的应用就是累计计算。还是用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的行为：

注意窗口开头那两行，id=1前面没有足够的行，窗口只有1行；id=2只有2行。这是ROWS窗口的边界自适应行为——窗口不会因为前面行数不够就越界报错，而是有多少算多少。这一点在做移动平均时特别重要，它意味着前N-1行的均值天然就是"不完整窗口"的均值，如果你的业务要求前面不足N行时返回NULL，需要自己用CASE WHEN加ROW_NUMBER()判断行号后再处理。

3.3 UNBOUNDED、CURRENT ROW和数字偏移怎么组合才对

ROWS窗口的边界可以自由组合，我用一个表格把最常用的几种搭配列出来，顺便说清各自的应用场景：

最后一个"中心化窗口"在数据预测和异常检测里很常用，比如算"某时刻前后各一段时间内的平均值"作为基线。这里有个兼容性提示：如果你用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就是一行SQL：

SELECT 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_id=3和product_id=5这两行，差异一目了然。

product_id=3的价格是80，RANGE窗口的值区间是[30,80]，价格50和两个80都落在里面，所以band_cnt=2；ROWS窗口数的是物理行，从当前行往上数2行，把价格50、80、80都圈进来了，row_cnt=3。

product_id=5的价格是150，RANGE窗口值区间是[100,150]，只有120和150，band_cnt=2；ROWS窗口还是数物理行，把80、120、150三行都算进去了，row_cnt=3。

这就是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里直接引用它。

正确的做法是包一层子查询，或者用CTE：

WITH 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 LAST（PostgreSQL支持）或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里的默认行为"可靠得多。窗口函数的语法本身不复杂，复杂的是对边界的理解；把每个边界的取舍写清楚，代码自己会说话。