SQL如何计算移动平均值_使用ROWS BETWEEN窗口定义

SQL移动平均必须用ROWS BETWEEN而非RANGE，因RANGE按值分组遇重复值会导致窗口边界漂移，而ROWS严格按物理行数滑动，确保“最近N条”的准确平均；如7日均值需ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

SQL移动平均为什么必须用ROWS BETWEEN而不是RANGE

这里有个关键区别，直接决定了计算结果的准确性。RANGE是按数值范围来分组的，一旦遇到重复值，它会把所有相同数值的行都纳入当前窗口。这就会导致窗口边界“漂移”，你原本想计算“最近N行”，结果可能拉进来几十行数据。

而ROWS BETWEEN则严格得多，它只认物理行数，一行就是一行。这才是实现业务上常说的“最近N条记录”移动平均的唯一可靠方法。举个例子，如果你的时间字段在同一秒有多条记录，用RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW，窗口可能会变得不可控。但换成ROWS BETWEEN 6 PRECEDING AND CURRENT ROW，无论数据如何，它都雷打不动地只取当前行及往前数6行，总共7行——这才是一个标准的“7日移动平均”。

ROWS BETWEEN语法中PRECEEDING和FOLLOWING的常见误用

计算移动平均时，FOLLOWING关键字基本用不上。绝大多数场景，我们用的都是ROWS BETWEEN N PRECEDING AND CURRENT ROW。如果不小心写成了BETWEEN CURRENT ROW AND 6 FOLLOWING，那逻辑就完全反了，变成了“向后看”未来数据。这在实时报表里是行不通的，因为未来的数据还没产生呢。

有几个语法细节值得特别注意：

• N PRECEDING里的N必须是一个整数常量，不能是列名或者某个表达式的结果。
• CURRENT ROW这个边界不能省略。如果漏写了，默认等价于UNBOUNDED FOLLOWING，窗口会一直延伸到分区末尾，结果肯定不对。
• 如果想计算一个“前后兼顾”的窗口，比如包含前N行和后M行，语法是ROWS BETWEEN N PRECEDING AND M FOLLOWING。但要注意，这会导致数据开头和结尾几行的计算结果不稳定，因为开头没有足够的前驱行，结尾没有足够的后继行。

不同数据库对ROWS BETWEEN的支持差异

虽然窗口函数是SQL标准，但各家数据库的实现和支持度还是有细微差别。主流的PostgreSQL、SQL Server、Oracle、Snowflake通常都提供完整支持。MySQL是从8.0版本才开始原生支持的。SQLite在3.25版本之后也加入了支持，但不支持UNBOUNDED这种关键字，需要你明确写出具体的数字边界。

另外，在一些大数据生态里，比如老版本的Hive，可能需要手动开启窗口函数支持（设置hive.windowing.enabled=true），并且要求ORDER BY后面必须跟一个唯一键。否则，排序结果不稳定，ROWS的行为也会变得难以预测。

来看一个标准写法示例：

SELECT
  date,
  sales,
  A VG(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM daily_sales;

这里有个要点：ORDER BY子句必须存在，否则数据没有确定的顺序，ROWS BETWEEN也就失去了意义。如果排序字段（如date）可能存在重复值，强烈建议加上一个二级排序字段（比如id），写成ORDER BY date, id，以确保每一行都有绝对确定的位置。

性能陷阱：窗口函数在大数据量下变慢的两个原因

窗口函数用起来方便，但在海量数据面前，性能问题就会凸显出来。主要有两个常见瓶颈：

第一是缺少索引。窗口定义里的ORDER BY字段如果没有合适的索引，数据库每次执行都要对全表或整个分区进行排序，开销巨大。

第二是窗口过宽。比如计算一个365天的移动平均（ROWS BETWEEN 365 PRECEDING AND CURRENT ROW），在亿级数据表上，每一行都需要累加366个值，计算量会呈线性增长，迅速拖慢查询。

如何规避？可以记住这几个原则：

• 优先为ORDER BY的字段建立索引，如果是复合索引，顺序要匹配排序顺序。
• 尽量避免在WHERE条件筛选之前使用窗口函数。因为SQL通常会先计算整个窗口，再过滤，应该想办法把过滤条件下推到窗口计算之前。
• 如果对实时性要求不高，可以考虑用物化视图或者定时更新的聚合表来预先计算好移动平均值，用空间换时间。

总而言之，窗口定义看似简单，但背后涉及到排序的稳定性、索引的覆盖程度，以及不同数据库引擎的具体实现。这三者任何一个环节没处理好，都可能让最终结果偏离预期。