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

窗口函数中的帧子句(ROWS/RANGE)是实现滑动窗口分析的核心语法，本文将深入解析其使用场景与选择策略，帮助开发者掌握这一高阶SQL技巧。

先解释两个核心术语

什么是“滑动窗口”？

数据队列中的固定宽度窗口会随着当前行移动，每次只统计窗口范围内的数据。例如计算3日移动平均时，窗口会从第1-3天滑动到第2-4天。这种动态范围的计算方式正是滑动窗口的本质特征。

什么是“帧子句”？

帧子句作为定义窗口范围的关键语法，明确指定了窗口的起始和结束位置。在函数() OVER (PARTITION BY ... ORDER BY ... 帧子句)结构中，帧子句决定了窗口函数是进行简单分组还是实现复杂滑动分析。

一、帧子句的基本语法

完整语法结构如下：

ROWS | RANGE BETWEEN 起点 AND 终点

起止点支持五种定义方式：

1. UNBOUNDED PRECEDING：分区首行
2. n PRECEDING：前n行
3. CURRENT ROW：当前行
4. n FOLLOWING：后n行
5. UNBOUNDED FOLLOWING：分区末行

需特别注意：当存在ORDER BY时默认使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，否则默认ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，这个差异常导致计算结果与预期不符。

二、ROWS vs RANGE 的核心区别

两者的本质差异在于窗口划分依据：

1. ROWS基于行号划分窗口，每行独立计算，适合需要精确控制行数的场景。
2. 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_cumFROM sales;

结果对比：

业务场景选择建议：

1. 严格逐行计算用ROWS
2. 逻辑分组聚合用RANGE

三、典型滑动窗口场景

场景1：3日移动平均

计算包含当前行及前后各1天的平均值：

SELECT sale_date, amount,  AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg_3FROM sales;

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

计算部门内从当前员工到最高工资者的总和：

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

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

PostgreSQL等数据库支持以下语法：

SELECT sale_date, amount,  AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) as moving_avg_exclude_selfFROM sales;

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

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

计算月度环比增长率：

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_rateFROM monthly_sales;

六、注意事项与性能建议

1. 帧子句仅对聚合窗口函数有效
2. RANGE模式需要数值/日期类型ORDER BY列
3. 超大窗口滑动会导致性能问题

七、总结

掌握帧子句的ROWS与RANGE选择策略，能显著提升SQL处理复杂分析任务的能力，避免不必要的自连接操作，使查询既简洁又高效。

1. MySQL官方文档：《Window Function Frame Specification》
2. PostgreSQL官方文档：《Window Functions: ROWS vs RANGE》
3. 《SQL进阶教程》第7章：窗口函数