SQL怎样实现带权重的移动平均_窗口函数自定义系数-SQL-PHP中文网

sql中无内置加权移动平均函数，需用sum(value*weight) over w / sum(weight) over w手动构造，注意权重预计算、窗口对齐及除零保护。

sql怎样实现带权重的移动平均_窗口函数自定义系数

SQL里没有内置带权重的移动平均函数，得自己构造

标准 SQL 的 AVG() 窗口函数只支持等权平均，没法直接指定系数序列。想实现指数衰减、线性递减或自定义权重（比如最近3天权重为 [0.5, 0.3, 0.2]），必须用 SUM() + 手动加权组合，配合 ROWS BETWEEN 显式控制窗口范围。

用 SUM(值 * 权重) + ROW_NUMBER() 模拟动态权重

核心思路：在窗口内给每一行打序号，再根据序号查表或计算对应权重，最后加权求和。常见写法是用 ROW_NUMBER() OVER (ORDER BY ts) 得到相对位置，然后用 CASE 或数值表达式映射权重。

假设按时间降序排列，当前行为第1行，前一行是第2行，再前是第3行——那么 ROW_NUMBER() OVER (ORDER BY event_time DESC) 在当前行返回 1，上一行返回 2，依此类推
线性衰减权重（最近3期）可写成：CASE WHEN rn = 1 THEN 0.5 WHEN rn = 2 THEN 0.3 WHEN rn = 3 THEN 0.2 ELSE 0 END
指数衰减（α=0.8）可写成：POWER(0.8, rn - 1)，但注意不同数据库对 POWER() 的空值/负指数处理不一致（PostgreSQL 支持，MySQL 8.0+ 支持，SQLite 不支持）
必须搭配 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 限制窗口大小，否则 rn 会跨整个分区，失去“移动”意义

不同数据库对窗口内行号的处理差异很关键

ROW_NUMBER() 在窗口函数中不能直接用于定义权重顺序——它默认按整个 PARTITION BY 排序，而你需要的是“相对于当前行的偏移位置”。正确做法是先用子查询或 CTE 固定排序和窗口，再在外层计算相对序号。

PostgreSQL / Snowflake / BigQuery：支持 GENERATE_SERIES() 或数组下标，但更稳妥是用 LAG() 多次取值后手动加权，例如：0.5 * value + 0.3 * LAG(value, 1) OVER (...) + 0.2 * LAG(value, 2) OVER (...)
MySQL 8.0+：不支持在窗口定义里嵌套窗口函数，所以不能在 SUM() 内直接调 LAG()；必须用 CTE 先拉出前N行，再聚合
SQL Server：可用 OFFSET 风格的 LEAD()/LAG() 链式调用，但超过5期就变得冗长难维护
所有数据库都需注意：当窗口开头不足N行时（如首行无前驱），LAG() 返回 NULL，乘以权重后参与 SUM() 会令结果为 NULL——要用 COALESCE(LAG(...), 0) 填零，而非忽略

性能和精度陷阱：别在大数据量上硬算高阶多项式权重

如果权重公式涉及 SQRT()、LOG() 或多层 POWER()，在百万级窗口上执行会显著拖慢。更糟的是浮点误差累积——比如用 POWER(0.999, n) 算1000期衰减，第1000项理论值≈0.367，但某些引擎因舍入可能变成0.366999999999999，累加后偏差放大。