SQL怎么计算分组内的移动平均值_利用ROWS BETWEEN窗口范围

首页

数据库

热心网友

转载

2026-04-24

SQL窗口函数：如何精准计算分组内的移动平均值

SQL怎么计算分组内的移动平均值_利用ROWS BETWEEN窗口范围

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

说到数据分析，移动平均是个绕不开的指标。它能平滑短期波动，揭示长期趋势，在销售预测、库存管理、用户行为分析等场景下应用广泛。但你知道吗？在SQL里实现它，尤其是用窗口函数，看似简单，实则暗藏玄机。不少开发者直接套用A VG() OVER (...)，结果却发现数据对不上，或者每次查询结果都飘忽不定。

问题的核心，往往出在对窗口范围ROWS BETWEEN的理解和排序字段ORDER BY的确定性上。今天，我们就来把这几个关键点彻底讲透。

SQL窗口函数中ROWS BETWEEN怎么定义移动平均的计算范围

先说结论：想计算“向前包含自身共n+1行”的移动平均，语法是ROWS BETWEEN n PRECEDING AND CURRENT ROW。如果想做中心滑动窗口（比如前后各看一行），那就得用ROWS BETWEEN n PRECEDING AND n FOLLOWING。

这里有个常见的思维误区：千万别把Excel里“最近3期”的概念直接搬过来。SQL里的ROWS BETWEEN，其边界是基于行偏移量的，而不是基于时间或具体的数值。它数的是“行数”，不是“天数”。

一个典型的翻车现场是这样的：A VG(x) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)。当时间戳ts字段存在重复值时，计算结果可能每次都不一样。为什么？因为ORDER BY对相同值的行顺序不做保证，数据库可以随意排列它们。这就导致ROWS BETWEEN框选的具体行，可能每次查询都不同。

所以，记住这几个要点：

必须显式指定ORDER BY：没有ORDER BY，ROWS BETWEEN就失去了意义，因为“当前行”在无序集合里是不确定的。
排序必须唯一：如果业务要求按时间对齐，但时间字段有重复，务必加上一个二级排序字段，比如ORDER BY ts, id，来确保顺序的绝对确定性。
理解“行数”与“时间跨度”的区别：PRECEDING和FOLLOWING后面的数字，指的是物理行数。如果你想按“过去7天”这样的时间跨度计算，要么先预处理生成连续的时间序列，要么考虑使用RANGE BETWEEN（注意：PostgreSQL支持时间类型的RANGE，而MySQL 8.0+的RANGE通常只支持数值类型）。

MySQL 8.0 和 PostgreSQL 中移动平均的写法差异

语法上，MySQL 8.0和PostgreSQL都支持标准的ROWS BETWEEN，写起来几乎一样。真正的差异，往往藏在细节里，比如对空值（NULL）和边界行的处理逻辑。

来看一个通用写法，计算每行及其前两行的销售均值：

SELECT
  day,
  sales,
  A VG(sales) OVER (
    ORDER BY day
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_a vg_3
FROM daily_sales;

虽然代码相同，但引擎底层行为有细微差别：

MySQL 8.0：A VG()函数会自动忽略NULL值参与计算，这是标准行为。但窗口帧的范围仍然会包含这些NULL值所在的行位置。在开头几行，如果可用行数不足，它依然会基于帧内存在的非NULL值进行计算。
PostgreSQL：同样，A VG()会跳过NULL。但如果整个窗口帧里全是NULL，它会直接返回NULL。相比之下，MySQL在首行可能表现得“更努力”一些。
性能提醒：窗口越大，排序和累计计算的开销就越明显。面对千万级大表时，一个最佳实践是：尽量先通过WHERE子句过滤数据，再进行窗口计算，避免无谓的性能损耗。

如何避免移动平均结果出现“开头几行全为NULL”

很多人第一次写移动平均，都会遇到这个问题：前两行的结果怎么是NULL？这其实不是bug，而是逻辑的必然。当你定义ROWS BETWEEN 2 PRECEDING AND CURRENT ROW时，第一行前面根本没有“前两行”，窗口帧不完整，数据库按标准返回NULL。

但业务上往往不接受NULL，希望“能算尽算”，比如第一行至少显示它自己的值。怎么办？

错误示范：改用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这确实能避免NULL，但它计算的是累积平均，从第一行累加到当前行，完全不是移动平均的概念了。
理想但复杂的方法：用CASE判断当前帧内实际有多少非空行，然后动态控制分母。可惜，大多数数据库引擎不支持在窗口函数内动态获取帧内的有效行数。
务实且高效的解法：使用COALESCE(A VG(...) OVER (...), sales)。这个技巧能把开头几行因帧不足产生的NULL，替换为原始的sales值。当然，这相当于接受了“首行等于自身值，第二行等于自身与前一行均值”的业务逻辑，在多数场景下是合理且可接受的。
最后的备选方案：如果业务要求必须严格满足N行滚动且首尾都要用特定方式补值（比如用边缘值填充），那就只能求助于自连接、LATERAL（PostgreSQL）或子查询了。这种方法逻辑清晰但性能很差，只适用于数据量很小的场景。

当 ORDER BY 字段不是主键时，移动平均为什么每次执行结果不一样

这是最隐蔽、也最容易导致生产事故的一个坑。现象就是：查询语句没变，数据没变，但今天跑和明天跑，移动平均的结果居然不一样。

根本原因在于：窗口帧依赖ORDER BY定义的逻辑顺序。当排序字段的值不唯一（存在并列）时，这个顺序在SQL标准中就是未完全确定的。数据库只要保证“具有相同ORDER BY值的行，其相对顺序可以是任意的”，就算合规。

典型场景：按date（日期）排序，但同一天内有多条交易记录，且没有指定第二排序字段。
导致的后果：两次查询中，同一天内的多行数据，被纳入窗口帧的顺序可能不同。那么，2 PRECEDING所指向的“前两行”具体是哪两行就可能发生变化，最终计算出的移动平均值自然会产生波动。
一劳永逸的修复方式：强制排序唯一化。最直接的就是加上主键或唯一字段，例如ORDER BY date, id。如果不存在这样的字段，可以构造一个，比如ORDER BY date, ROW_NUMBER() OVER (PARTITION BY date ORDER BY created_at)。
重要提醒：不要依赖数据库的默认物理顺序或索引顺序来“碰运气”。在SQL语义层面，这不提供任何稳定性保证。

说到底，使用移动平均窗口函数时，最容易被忽略的就是排序字段的确定性。它不仅仅关乎结果能否重现，更直接决定了你的移动窗口到底“框”住了哪几行数据。一个黄金法则是：宁可多写一个id字段确保唯一排序，也绝不要让ROWS BETWEEN在一个模糊的排序环境下运行。确定性，才是可靠分析的基石。

来源:https://www.php.cn/faq/2324867.html

免责声明：游乐网为非赢利性网站，所展示的游戏/软件/文章内容均来自于互联网或第三方用户上传分享，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系youleyoucom@outlook.com。

上一篇：如何实现SQL分表数据同步_通过触发器映射写入目标表下一篇：如何实现SQL多条件筛选_逻辑运算符AND与OR实战技巧