SQL如何计算移动平均值_利用滑动窗口函数实现数据平滑-SQL-PHP中文网

现代sql用avg() over(order by col rows between n preceding and current row)实现移动平均，rows比range更兼容、行为可预测，avg自动忽略null，加partition by和索引可提升性能。

sql如何计算移动平均值_利用滑动窗口函数实现数据平滑

SQL里怎么写移动平均？直接用`AVG()`配合`OVER()`

移动平均不是靠子查询或自连接硬凑出来的，现代SQL（PostgreSQL 8.4+、SQL Server 2005+、Oracle、BigQuery、Snowflake、MySQL 8.0+）都支持窗口函数，AVG()加OVER()就是最简洁可靠的解法。

关键在ROWS BETWEEN的定义方式——它决定“窗口往回看几行”，而不是按时间字段自动对齐。容易误以为ORDER BY date就能按天滑动，其实不然：如果某天缺数据，窗口仍按行数滑，不会跳过空缺。

AVG(sales) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)：算当前行 + 前两行共3行的均值
想按“最近7天”而非“最近7条记录”，得先用GENERATE_SERIES补全日期，或在外层用LEFT JOIN对齐日粒度
MySQL 5.7及更早不支持窗口函数，强行用会报错This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'这类误导信息，实际是语法不识别OVER

为什么`ROWS`比`RANGE`更常用？

RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW看起来更符合“7天移动平均”的直觉，但只有PostgreSQL和SQL Server（2012+）部分支持RANGE带时间间隔，MySQL和BigQuery根本不认INTERVAL在RANGE里。

更麻烦的是语义差异：RANGE会把同一天内多条记录全纳入窗口，而ROWS只数行数，不关心值是否重复。比如一天有5笔订单，ROWS BETWEEN 6 PRECEDING AND CURRENT ROW可能只包含当天2条+前两天各2条，而RANGE可能一把抓进当天全部5条，导致均值突然拉高。

生产环境优先选ROWS，兼容性强，行为可预测
真要用RANGE，先确认数据库版本和文档是否明确写了“supports RANGE with temporal bounds”
PostgreSQL中RANGE对TIMESTAMP有效，但对DATE类型可能隐式转成TIMESTAMP再计算，导致边界偏移半天

NULL值怎么处理？`AVG()`默认就跳过它们

这是个常被忽略的友好设计：AVG()窗口函数天然忽略NULL，不需要额外WHERE过滤或CASE WHEN兜底。但要注意，它只跳过参与计算的列（比如AVG(revenue)里的revenue IS NULL行），不影响窗口行数本身。

如果某行revenue为NULL，它仍占一个“位置”，ROWS BETWEEN 1 PRECEDING AND CURRENT ROW还是取两行，只是其中一行贡献0值
想彻底排除NULL行再滑动，得先用CTE或子查询过滤：SELECT * FROM t WHERE revenue IS NOT NULL
某些BI工具（如Tableau生成的SQL）会把AVG包装成COALESCE(AVG(...), 0)，这反而掩盖了数据缺失问题，慎用

性能差得离谱？检查`PARTITION BY`和索引

没加PARTITION BY时，窗口会在整个结果集上排序计算，数据量一过百万行，执行计划里常出现WindowAgg (cost=... rows=... width=...)后面跟着巨大数字。加上分区能立竿见影。

按用户ID平滑点击率？加PARTITION BY user_id ORDER BY event_time
按商品类目算销量趋势？加PARTITION BY category ORDER BY sale_date
确保ORDER BY字段有索引，特别是组合索引要匹配PARTITION BY + ORDER BY顺序，例如(category, sale_date)
SQLite不支持窗口函数，别试；MariaDB 10.2+支持但默认关闭WINDOW功能，需启动时加--window-functions