SQL窗口函数进阶：滑动窗口与帧子句详解

上周讲了窗口函数与子查询、CTE的性能对比，有读者问：窗口函数的帧子句（ROWS/RANGE）到底怎么用？为什么有时候用ROWS有时候用RANGE？今天就把这个坑填上，专门讲讲窗口函数的进阶能力——滑动窗口与帧子句。

先解释两个核心术语

什么是“滑动窗口”？

想象你站在一列数据的长队里，眼前有一个固定宽度的“窗口”，这个窗口每次向右移动一格，每次只统计窗口内的数据。比如计算最近3天的移动平均：第一天看第1-3天，第二天看第2-4天，第三天看第3-5天……窗口在“滑动”。这就是滑动窗口的核心思想：窗口位置随着当前行移动，每次计算一个范围内的数据。

什么是“帧子句”？

帧子句就是用来定义这个“窗口范围”的规则。它告诉数据库：当前行的窗口应该从哪里开始、到哪里结束。比如“从当前行的前2行到当前行的后2行”“从分区第一行到当前行”。帧子句是窗口函数实现滑动窗口的关键语法。

窗口函数的核心语法是：函数() OVER (PARTITION BY ... ORDER BY ... 帧子句)。帧子句定义了相对于当前行，窗口的起止范围。用好帧子句，可以实现移动平均、累计求和、同比环比、滑动聚合等复杂逻辑，否则窗口函数就只是带排序的分组聚合而已。

一、帧子句的基本语法

帧子句的完整写法：

ROWS | RANGE BETWEEN 起点 AND 终点

其中起点和终点可以是：

• UNBOUNDED PRECEDING：从分区第一行开始
• n PRECEDING：当前行之前的n行
• CURRENT ROW：当前行
• n FOLLOWING：当前行之后的n行
• UNBOUNDED FOLLOWING：直到分区最后一行

如果不显式指定帧子句，默认行为是：有ORDER BY时默认RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW；无ORDER BY时默认ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。这一点经常被误解，导致计算结果与预期不符。

二、ROWS vs RANGE 的核心区别

这是最容易踩的坑。用一个比喻帮助你理解：

• ROWS：像用“行号”画窗口。窗口按行数严格划分，不管ORDER BY列的值是否相同，每一行都独立计算。类似于“前5个人、后5个人”。
• RANGE：像用“值”画窗口。窗口按ORDER BY列的值划分，相同值的数据必须同时出现在窗口内或被排除在外。类似于“所有年龄相同的人放在一起统计”。

用一个具体例子说明。表sales：日期和销售额

执行：

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rows_cum,
       SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as range_cum
FROM sales;

结果：

• ROWS：严格按行顺序累加，第一行100，第二行100+50=150，每行都变。
• RANGE：按sale_date的值分组。2026-01-01的两行属于同一个值，窗口把这两行作为一个整体累计，所以两行的累计值都是150（100+50），直到2026-01-02才增加到350。

实际业务中：

• 需要严格逐行计算（如移动平均、每笔交易独立累计）→ 用ROWS
• 需要按逻辑分组聚合（如按日期统计，同一天的数据应同时计入）→ 用RANGE

三、典型滑动窗口场景

场景1：3日移动平均（滑动窗口经典案例）

计算每个日期前后各1天（包含当天）的平均销售额。这里的“窗口”就是当前行、前1行、后1行。随着当前行向下移动，窗口也跟着“滑动”。

SELECT sale_date, amount,
       A VG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_a vg_3
FROM sales;

注意边界处理：第一行没有1 PRECEDING，窗口只包含当前行和1 FOLLOWING。这就是滑动窗口最常用的形式。

场景2：从当前行到分区末尾的累计

计算每个部门内，按工资从低到高排序，从当前员工到工资最高者的工资总和。

SELECT dept, salary,
       SUM(salary) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as sum_from_curr
FROM emp;

这里窗口的起点是“当前行”，终点是“分区末尾”，随着当前行下移，窗口越来越小。适合计算“比我工资高的人的总和”等需求。

场景3：排除当前行的滑动窗口

计算当前行之前2行到当前行之后2行，但排除当前行本身。例如分析整体趋势时去掉自身的波动。

SELECT sale_date, amount,
       A VG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) as moving_a vg_exclude_self
FROM sales;

EXCLUDE CURRENT ROW是SQL标准支持但MySQL尚未实现的语法，PostgreSQL等数据库已支持。如果MySQL需要实现类似效果，可以自行计算总窗口值再减去当前值。

四、ROWS与RANGE在滑动窗口中的选择建议

五、实际运用：计算同比环比

假设有每月销售表monthly_sales（year, month, amount）。计算环比（与上月比较）：

SELECT year, month, amount,
       LAG(amount, 1) OVER (ORDER BY year, month) as prev_amount,
       (amount - LAG(amount, 1) OVER (ORDER BY year, month)) / LAG(amount, 1) OVER (ORDER BY year, month) as growth_rate
FROM monthly_sales;

LAG/LEAD函数配合帧子句可以更灵活地定义偏移量。计算同比（去年同期）则需要更复杂的窗口定义或自连接。

六、注意事项与性能建议

• 帧子句只对聚合窗口函数（SUM、A VG、COUNT、MIN、MAX）有意义；排名函数（ROW_NUMBER、RANK等）和偏移函数（LAG、LEAD）忽略帧子句，始终基于整个分区。
• RANGE模式要求ORDER BY列是数值或日期类型，且通常会产生比ROWS更多的内存消耗，因为需要识别“相同值”的组边界。
• 超大窗口滑动时（如UNBOUNDED PRECEDING），相当于全分区扫描，性能开销大。可考虑使用索引和物化视图预计算。

七、总结

窗口函数的高级能力——帧子句，是实现复杂滑动分析的关键。区分ROWS与RANGE、正确设置边界，能写出更简洁高效的SQL，避免使用自连接或游标。掌握这些技巧，是SQL从“能写”到“会优化”的重要一步。