怎样在SQL中通过窗口函数实现动态的权重平均计算

动态权重平均是通过SUM(value*weight) OVER(...)/SUM(weight) OVER(...)手动计算的加权平均，因AVG()不支持权重参数且窗口函数无内置加权平均函数，必须用两个独立SUM窗口表达式确保分子分母范围严格对齐，并用NULLIF防除零错误。

什么是动态权重平均，为什么不能直接用 AVG()

静态平均（比如 AVG(price)）对所有行一视同仁，但业务中常需要“越近的记录越重要”——比如按时间加权、按销量加权、或按置信度加权。窗口函数本身不提供加权平均聚合函数，AVG() 无法接收权重参数，硬套会丢失权重逻辑。

真正可行的路只有一条：用窗口函数算出分子（加权和）和分母（权重和），再手动相除。

SUM(value * weight) OVER (...) / SUM(weight) OVER (...) 是唯一可靠写法

MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持这个模式，兼容性好，且能配合任意窗口定义（如 PARTITION BY category ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW）。

• 必须用两个独立的 SUM() 窗口表达式，不能试图在单个 AVG() 里塞权重
• 注意 weight 列不能为 NULL，否则整行参与计算时分子分母都会变 NULL；建议提前用 COALESCE(weight, 0) 或 WHERE weight IS NOT NULL
• 如果权重含小数（如 0.8、1.2），结果类型可能变成 DOUBLE 或 DECIMAL，必要时用 ROUND(..., 2) 控制精度

示例（按品类滚动加权平均价格，权重为销量）：

SELECT  category,  date,  price,  sales,  ROUND(    SUM(price * sales) OVER (      PARTITION BY category       ORDER BY date       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW    )::DECIMAL /    NULLIF(SUM(sales) OVER (      PARTITION BY category       ORDER BY date       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW    ), 0),    2  ) AS weighted_avg_priceFROM products;

NULLIF(..., 0) 不是可选项，是防错必需

当某组窗口内所有 sales = 0（或全为 NULL 后被 COALESCE 成 0），分母为 0 会导致除零错误。不同数据库行为不一：PostgreSQL 报错，MySQL 返回 NULL，但不可靠。

• 一律用 NULLIF(SUM(weight) ..., 0) 替代裸 SUM(weight)
• 不要依赖 WHERE weight > 0 预过滤——窗口范围可能跨多行，局部为 0 不代表全局为 0
• 若业务允许权重为负（极少见），需额外判断 SUM(weight) = 0 而非仅 > 0

ORDER BY 和 frame clause 决定“动态”范围，别漏写

没写 ORDER BY 的窗口，ROWS BETWEEN ... 无效（默认等价于 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，但语义模糊）；不写 frame clause 时，PostgreSQL 默认是 RANGE，MySQL 默认是 ROWS，行为不一致。

• 时间序列加权必须显式写 ORDER BY time_col，否则顺序不确定
• 明确用 ROWS BETWEEN N PRECEDING AND CURRENT ROW 表示“最近 N 条”，用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示“累计到当前”
• 避免用 RANGE 帧（尤其含时间字段时），它会合并相同排序值的行，导致权重重复计入

权重平均的核心不是函数有多炫，而是分子分母必须严格对齐窗口范围——差一行，结果就偏了。