PostgreSQL如何从源表取值并计算移动平均插入新表

正确实现代码 #

方案1：一次性插入（推荐，效率更高） #

先通过子查询或LATERAL JOIN完成数组拆分为单行的操作，再在外层使用窗口函数计算移动平均，完全符合报错提示的优化建议：

INSERT INTO destination (value1, value2, avg_val1, avg_val2)
SELECT
  t.v1,
  t.v2,
  -- 移动窗口大小可按需调整，如需要50条preceding就修改为50
  AVG(t.v1) OVER (ORDER BY t.sort_seq ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),
  AVG(t.v2) OVER (ORDER BY t.sort_seq ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
FROM (
  SELECT
    u.v1,
    u.v2,
    -- 生成严格按source表行顺序、数组元素顺序排列的序列号，保证移动平均计算顺序正确
    ROW_NUMBER() OVER (ORDER BY s.m_id, u.ord) AS sort_seq
  FROM source s
  -- LATERAL 关联unnest结果，同时返回数组元素的原始序号
  CROSS JOIN LATERAL UNNEST(s.value1, s.value2) WITH ORDINALITY AS u(v1, v2, ord)
  WHERE s.a_id = 1
) t;

方案2：先插入再更新（适用于需要拆分操作的场景） #

如果确实需要先插入拆分后的值再计算移动平均，需要把窗口函数放在子查询中，通过主键关联更新，避免直接在UPDATE子句中使用窗口函数：

-- 第一步：插入拆分后的value1、value2
INSERT INTO destination (value1, value2)
SELECT u.v1, u.v2
FROM source s
CROSS JOIN LATERAL UNNEST(s.value1, s.value2) AS u(v1, v2)
WHERE s.a_id = 1;

-- 第二步：关联子查询计算的移动平均结果更新
UPDATE destination d
SET avg_val1 = t.avg1, avg_val2 = t.avg2
FROM (
  SELECT
    n_id,
    AVG(value1) OVER (ORDER BY n_id ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS avg1,
    AVG(value2) OVER (ORDER BY n_id ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS avg2
  FROM destination
) t
WHERE d.n_id = t.n_id;

原写法报错原因 #

• 第一次报错是因为窗口函数的参数不能直接使用返回多行结果的集合函数（unnest），必须先把数组拆分为单行标量值后再调用窗口函数
• 第二次报错是因为PostgreSQL不允许在UPDATE的SET子句中直接使用窗口函数，需要将窗口函数的计算逻辑放在子查询中，通过主键关联完成更新

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