如何在SQL中生成带自定义权重的移动加权平均值？

硬编码权重方案 #

使用LAG()窗口函数获取当前行的前1行和前2行value值，分别乘以对应权重后相加。通过PARTITION BY idx, key_val确保不同分组（如key_val为'a'和'b'）的计算独立，避免跨分组取数。COALESCE()用于处理行数不足的情况（如前两行数据无足够前置行时，用0替代NULL，防止结果为NULL）。

SELECT *,
    1.5 * value + 
    COALESCE(0.5 * LAG(value, 1) OVER (PARTITION BY idx, key_val ORDER BY ts), 0) + 
    COALESCE(0.25 * LAG(value, 2) OVER (PARTITION BY idx, key_val ORDER BY ts), 0) 
    AS weighted_moving_average
FROM T;

验证示例：

• 第3行（idx=1, key_val='a', ts=300, value=30）计算：30*1.5 + 30*0.5 + 20*0.25 = 65
• 第4行（idx=1, key_val='a', ts=300, value=40）计算：40*1.5 + 30*0.5 + 30*0.25 = 82.5

权重来自外部表方案 #

若权重需动态维护，可创建权重表存储各位置权重，再结合窗口函数计算：

1. 创建权重表 #

CREATE TABLE weights (position INT, weight NUMERIC);
INSERT INTO weights VALUES
(0, 1.5),  -- 当前行权重
(1, 0.5),  -- 前一行权重
(2, 0.25); -- 前两行权重

2. 关联权重表计算加权平均值 #

SELECT t.*,
    SUM(w.weight * COALESCE(
        CASE w.position
            WHEN 0 THEN t.value
            WHEN 1 THEN LAG(t.value, 1) OVER (PARTITION BY t.idx, t.key_val ORDER BY t.ts)
            WHEN 2 THEN LAG(t.value, 2) OVER (PARTITION BY t.idx, t.key_val ORDER BY t.ts)
        END, 0
    )) AS weighted_moving_average
FROM T t
CROSS JOIN weights w
GROUP BY t.idx, t.key_val, t.ts, t.value;

注意事项 #

• 空值处理函数需适配数据库：MySQL用IFNULL()，Oracle用NVL()，PostgreSQL用COALESCE()。
• 必须添加PARTITION BY idx, key_val，否则会跨分组取前置行数据，导致计算结果错误。

内容的提问来源于stack exchange，提问作者manoos