PostgreSQL使用lag()窗口函数填充周结存字段技术咨询

没问题，这场景太常见了！咱们用窗口函数结合UPDATE语句就能轻松搞定，我给你拆解清楚步骤：

核心逻辑先理清楚 #

周结存的计算规则肯定是：当前周结存 = 上一周结存 + 当前周周转额，而第0周的初始结存已经有值了，所以只需要基于这个初始值，按产品分组逐周递推计算就行。

具体实现方案 #

假设你的表名叫product_weekly_turnover，字段分别是：

• product_id：产品唯一标识
• week_num：周数（从0开始）
• weekly_turnover：周周转额（正负值对应采购/销售）
• week_end_inventory：周结存（第0周有值，其余为NULL）

我推荐用累加窗口函数的方式来计算，比逐行用lag()更高效，尤其是数据量较大的时候：

第一步：先计算出所有周的正确结存值

用CTE（公共表表达式）来预计算每个产品每周的应存结存：

WITH calculated_inventory AS (
    SELECT 
        product_id,
        week_num,
        -- 从第0周开始累加周转额，加上初始结存就是当前周结存
        FIRST_VALUE(week_end_inventory) OVER (
            PARTITION BY product_id 
            ORDER BY week_num
        ) + SUM(weekly_turnover) OVER (
            PARTITION BY product_id 
            ORDER BY week_num 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS correct_inventory
    FROM product_weekly_turnover
)

这里的FIRST_VALUE会取出每个产品第0周的初始结存，SUM窗口函数则会累加从第0周到当前周的所有周转额，两者相加就是当前周的正确结存。

第二步：用计算结果更新原表的NULL值

把CTE的结果和原表关联，只更新结存为NULL的行：

UPDATE product_weekly_turnover t
SET week_end_inventory = ci.correct_inventory
FROM calculated_inventory ci
WHERE t.product_id = ci.product_id
  AND t.week_num = ci.week_num
  AND t.week_end_inventory IS NULL;

如果你更倾向用lag()逐行计算 #

也可以用lag()窗口函数结合递归逻辑实现递推，适合需要逐行验证的场景：

WITH recursive_inventory AS (
    SELECT 
        product_id,
        week_num,
        weekly_turnover,
        week_end_inventory AS correct_inventory
    FROM product_weekly_turnover
    WHERE week_num = 0 -- 先取第0周的初始值
    UNION ALL
    SELECT 
        p.product_id,
        p.week_num,
        p.weekly_turnover,
        ri.correct_inventory + p.weekly_turnover AS correct_inventory
    FROM product_weekly_turnover p
    JOIN recursive_inventory ri 
        ON p.product_id = ri.product_id 
        AND p.week_num = ri.week_num + 1
)
UPDATE product_weekly_turnover t
SET week_end_inventory = ri.correct_inventory
FROM recursive_inventory ri
WHERE t.product_id = ri.product_id
  AND t.week_num = ri.week_num
  AND t.week_end_inventory IS NULL;

这个递归CTE的方式是从第0周开始，逐周关联计算下一周的结存，逻辑更直观，适合数据量不大的情况。

重要提示 #

• 执行UPDATE前，一定要先运行CTE的SELECT语句验证计算结果是否正确，比如：SELECT * FROM calculated_inventory ORDER BY product_id, week_num LIMIT 20;
• 如果数据比较重要，建议开启事务操作，避免误修改：

  BEGIN;
  -- 执行UPDATE语句
  SELECT * FROM product_weekly_turnover WHERE week_num > 0 LIMIT 10; -- 检查结果
  COMMIT; -- 确认正确再提交，错误的话用ROLLBACK;回滚
  

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