结论：移动平均必须用rows between n preceding and current row，不可用range；因rows按物理行偏移确保“最近n行”准确，而range在重复或非数值排序键下易漂移、报错或结果错误。
SQL窗口函数中ROWS BETWEEN的语法到底怎么写
直接说结论：移动平均必须用 ROWS BETWEEN n PRECEDING AND CURRENT ROW，不能用 RANGE，否则在时间戳或存在重复排序键时会出错。核心是“行偏移”而非“值范围”。
常见错误现象：AVG(value) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW) 看似合理，但PostgreSQL/MySQL 8.0+不支持非数字类型的 RANGE，而SQL Server压根不支持 RANGE 的时间间隔写法——结果要么报错 "RANGE is not supported with ORDER BY on non-numeric columns"，要么静默返回错误结果。
实操建议：
- 始终用
ROWS定义物理行数，比如过去3行（含当前）：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW - 排序字段必须明确且稳定，若存在并列（如多条记录
order_date = '2024-01-01'），需补上唯一字段防非确定性：ORDER BY order_date, id - 注意
CURRENT ROW是包含当前行的，想算“前N行不含当前”，得写成ROWS BETWEEN N PRECEDING AND 1 PRECEDING
不同数据库对ROWS窗口的兼容性差异
不是所有数据库都支持完整语法。MySQL 8.0+ 和 PostgreSQL 11+ 支持标准 ROWS BETWEEN；SQL Server 2012+ 支持但不支持 UNBOUNDED FOLLOWING 在移动平均常用场景中；SQLite 3.25+ 支持但要求 ORDER BY 必须存在。
容易踩的坑：
- SQL Server 中
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW可用，但ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING在某些版本会报错"The window frame cannot be used with the function 'AVG'" - 旧版 MySQL（"You have an error in your SQL syntax"
- 如果排序字段有NULL，不同数据库处理方式不同：PostgreSQL把NULL排最前，MySQL 8.0默认排最后——会导致同一SQL在不同环境移动平均结果不一致
移动平均值计算的实际SQL写法示例
假设有一张销售表 sales，字段为 sale_date、amount，要计算每个日期向前滚动3天（含当天）的平均销售额：
SELECT
sale_date,
amount,
ROUND(AVG(amount) OVER (
ORDER BY sale_date, id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_3d
FROM sales
ORDER BY sale_date;
说明：
-
id是主键或唯一字段，用于打破sale_date相同时的排序不确定性 - 这里用的是“2 PRECEDING”，因为要覆盖当前 + 前2行 → 共3行数据
-
ROUND(..., 2)避免浮点精度干扰可读性，不是必须但强烈建议 - 如果某天是分组第一条记录（前面不足2行），窗口自动截断，只对可用行求平均——这是预期行为，不是bug
为什么不能直接用GROUP BY配合子查询模拟移动平均
有人试图用自连接或相关子查询实现，例如：
SELECT s1.sale_date,
(SELECT AVG(s2.amount)
FROM sales s2
WHERE s2.sale_date BETWEEN DATE_SUB(s1.sale_date, INTERVAL 2 DAY) AND s1.sale_date) AS avg_3d
FROM sales s1;
问题在于性能和语义偏差：
- 时间范围匹配可能漏掉同一天多笔记录，也可能误吞未来插入但时间戳相同的数据（因未排序去重）
- O(n²) 复杂度，10万行数据可能卡死；而窗口函数是单次扫描，O(n log n) 主要在排序
- 无法处理严格按行序（而非时间）定义的场景，比如“最近3笔订单”，订单时间可能乱序，必须依赖插入顺序字段
真正难的从来不是写对那一行 ROWS BETWEEN，而是确认业务定义的“移动”到底基于时间、序列号，还是物理插入顺序——这个判断错了，后面全白搭。
