SQL窗口函数：ROWS与RANGE，一字之差，结果天壤之别

在数据分析或报表开发中，你有没有遇到过这样的困惑：明明用了同样的窗口函数语法，计算出的移动平均值或累积和，却和业务直觉对不上？问题往往就出在窗口帧定义的两个关键字上：ROWS 和 RANGE。

简单来说，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 严格按物理顺序取当前行及前2行共3行，不依赖值；而 RANGE 则按排序列的值范围匹配所有满足条件的行，极易因重复值导致窗口“意外膨胀”。

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：它到底怎么“数数”？

这个子句的逻辑非常“机械”：它严格按照查询结果集的物理顺序，从当前行开始，往前数两行。数够三行（当前行+前两行）就停，不多不少。哪怕这三行的ORDER BY列值完全一样（比如score都是85），它也照数不误，只取这三条物理记录。

所以，当你看到计算结果出现“跳变”时，比如第一行是100，第二行是180，第三行突然变成250，先别急着怀疑代码。这很可能不是Bug，而是ROWS机制在逐行滑动窗口时的真实表现——每一行计算时纳入的“样本”都在物理上精确移动。

• 适用场景：需要固定样本数量的统计，比如“最近3笔订单的金额总和”、“过去3天的日活用户数之和”。业务关心的是“条数”或“次数”。
• 一个关键细节：ORDER BY的列必须稳定。如果你用了RANDOM()或NOW()这类非确定性函数，每次执行时行的顺序都可能不同，同一行在不同查询中被纳入或排除窗口的概率也就不一样了。
• 性能优势：由于只需根据物理偏移定位行，无需进行复杂的值比较或分组，ROWS对数据库的性能影响通常较小。

RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW：为什么它总爱“多算”？

与ROWS的“数行”逻辑不同，RANGE的核心是“看值”。它以当前行的sale_date值为锚点，向前回溯一个值域范围（比如7天），把所有落在这个时间区间内的行，全部纳入当前窗口。

这就解释了那个经典现象：假设某一天发生了50笔交易，RANGE窗口会把这50行全部打包进来计算。而如果用ROWS BETWEEN 7 PRECEDING AND CURRENT ROW，最多只取8行（含当前行），哪怕这8条记录可能横跨了30天。

• 适用场景：业务逻辑基于值域而非行数时。例如，“过去7天内所有订单的总额”、“价格在当前商品±50元范围内的竞品平均售价”。
• 关键限制：它通常只支持能进行范围加减计算的类型，比如DATE、TIMESTAMP、NUMERIC。对于STRING类型，一般不支持INTERVAL这种形式。
• 最容易踩的坑：当ORDER BY列存在大量重复值时（比如按小时聚合的时间戳），RANGE会把整组重复值一次性全部拉进窗口，导致窗口大小远超你的预期，计算结果自然也就“膨胀”了。

当ORDER BY列有重复值：ROWS和RANGE的累积和为何天差地别？

这里的本质区别可以用一句话概括：ROWS把每一行都视为独立的个体，而RANGE把具有相同排序值的所有行视为一个逻辑单元。

来看一组示例数据（按score升序排列）：

id | score
---|------
1  | 80
2  | 85
3  | 85
4  | 85
5  | 90

如果使用ROWS模式计算从开头到当前的累积和（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），结果是逐行累加的：
第1行：80
第2行：80+85 = 165
第3行：80+85+85 = 250
第4行：80+85+85+85 = 335
第5行：全部相加 = 425

但如果换成RANGE模式（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），逻辑就变了：
第1行（score=80）：只看到自己，所以是80。
第2–4行（score=85）：对于这三行中的任意一行，窗口都会包含“所有score ≤ 85”的行。因此，它们三行的计算结果都是 80+85+85+85 = 335。
第5行（score=90）：看到所有行，结果为425。

• 背后的逻辑映射：你可以把ROWS的行为想象成ROW_NUMBER()的编号逻辑（每行独立编号），而RANGE则对应RANK()的排名逻辑（相同值共享名次）。
• 一个危险的默认行为：在许多数据库引擎（如PostgreSQL、SQL Server）中，如果你在窗口函数中不显式指定ROWS或RANGE，默认会采用RANGE UNBOUNDED PRECEDING。这常常导致计算结果与基于“行数”的直觉严重不符。
• 调试建议：当对窗口范围不确定时，可以先分别运行ROW_NUMBER() OVER (ORDER BY ...)和RANK() OVER (ORDER BY ...)，观察数据的排序和分组行为，再套用到窗口帧的定义上，思路会清晰很多。

哪些场景下，必须用RANGE，ROWS无法替代？

当你的业务问题本质上关心的是“一个数值区间”，而不是“具体多少条记录”时，ROWS就完全无法准确表达了。

典型的不可替代场景包括：

• 时间窗口分析：比如“过去30天的销售总额”。你需要的是自然日维度下的所有交易，而不是“最近30条交易记录”——因为一天内可能产生成百上千条交易。
• 数值区间匹配：在金融风控中，计算“当前用户授信额度±10%范围内的所有客户平均逾期率”。这里的关键是额度值的浮动区间，与客户数量无关。
• 基于分组的业务语义：例如，“找出同城市所有门店的上月GMV中位数”。虽然城市名是字符串，但部分数据库引擎的RANGE可以配合CURRENT ROW实现隐式的等值匹配，从而完成分组内的计算。

当然，选用RANGE也需谨慎。它在Hive或旧版MySQL中支持可能有限，且对高基数的重复值非常敏感。如果只是想实现“跳过重复值的排名”，采用DENSE_RANK()配合ROWS的组合往往更可控。

最后提一个极易被忽略的要点：RANGE的边界判断完全依赖于排序列的可比较性和确定性。一旦该列包含NULL值，或者值来自非确定性函数（如UUID()），整个窗口的行为就会变得难以预测，甚至连调试都无从下手。