SQL怎样计算移动平均值_利用ROWS BETWEEN控制滑动窗口-SQL-PHP中文网

必须写ORDER BY，因无序时ROWS BETWEEN行为未定义：MySQL报错、PostgreSQL结果随机；且仅用event_time排序在时间相同时不保证行序稳定，需加二级排序如id或created_at确保唯一性。

sql怎样计算移动平均值_利用rows between控制滑动窗口

直接用 AVG() 配合 ROWS BETWEEN 是最主流、最可控的移动平均写法，但必须满足三个硬条件：有确定性 ORDER BY、显式定义窗口边界、理解 PRECEDING 数字的真实含义——它指“往前跳几行”，不是“总共几行”。

为什么必须写 ORDER BY，且不能只靠时间字段

没 ORDER BY 的 ROWS BETWEEN 是未定义行为，MySQL 8.0+ 会报错 Window definition requires ORDER BY，PostgreSQL 可能返回随机结果。更隐蔽的问题是：仅用 ORDER BY event_time，当多条记录时间戳相同时（比如批量导入、日志聚合），数据库不保证这些行的物理顺序，导致同一查询多次执行，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 框选的可能是不同三行，移动平均值漂移。

解决方法：

加二级排序，例如 ORDER BY event_time, id 或 ORDER BY event_time, created_at，确保排序唯一
若时间字段精度不够（如只有天），先用 ROW_NUMBER() OVER (ORDER BY event_time, id) 生成稳定序号，再按该序号开窗
避免用 ORDER BY event_time DESC 做倒序滑动——CURRENT ROW 仍是锚点，但 PRECEDING 变成“逻辑上更晚”的行，极易混淆

ROWS BETWEEN 的数字到底怎么算

ROWS BETWEEN N PRECEDING AND CURRENT ROW 的窗口长度是动态的：第 1 行只有 1 行（自身），第 2 行有 2 行，第 3 行起才稳定为 N+1 行。这不是 bug，是 SQL 标准行为，但业务常误读“最近 3 天”就该强制取满 3 行。

常见错误与对应写法：

想算“含当前行共 3 行” → 正确：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW（2 行往前 + 当前行）
误写成 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW → 实际是 4 行均值
想算“前 3 行，不含当前” → 写 ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING，但注意首 3 行会返回 NULL
中心对称 5 日均值（当前日在正中）→ ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING，但首尾 2 行因越界返回 NULL

分组内移动平均必须加 PARTITION BY

漏掉 PARTITION BY 是线上指标翻车最高发原因。例如按用户计算 7 日订单均值，如果只写 AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)，A 用户第 1 笔订单可能和 B 用户最近 6 笔混在一起计算，结果完全失真。

正确写法必须显式切组：

单维度分组：PARTITION BY user_id ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
多维度分组（如用户+商品类目）：PARTITION BY user_id, category ORDER BY order_date
分区字段类型要一致，比如 user_id 是字符串但部分值带空格，可能导致同用户被拆成多组

注意：窗口函数在视图中定义时，PARTITION BY 也不能省略，否则视图固化后无法通过外层 WHERE 修复逻辑。

NULL 值和开头几行的处理陷阱

AVG() 本身会忽略 NULL，但整窗口若都是 NULL，结果就是 NULL；而开头几行窗口不满时，AVG() 仍会计算实际存在的非 NULL 值——这看似合理，但容易掩盖数据稀疏问题。

典型风险点：

某天无销售数据，按 id 排序时，id=100 的“前 2 行”可能取到 id=97,98，跳过本该存在的 id=99，时间跨度错位
用 AVG(sales) 直接计算，若某行 sales 为 NULL，分母自动减 1，但业务可能期望它算作 0（需先 COALESCE(sales, 0)）
想严格保持分母为 3（即使含 NULL），就得放弃 AVG()，改用 SUM(sales) / NULLIF(COUNT(*), 0)，但此时 NULL 被当 0 参与求和，语义已变

真正稳健的做法是：时序场景优先用时间字段 ORDER BY event_time + RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW（PostgreSQL/Oracle/MySQL 8.0+ 支持），或提前补全日期维表再左连接——而不是硬扛 ROWS 的行号依赖。