Redshift使用lag()函数更新subscribers表current_subscribers字段问题

你原有方案失效的核心原因有两个：

• 窗口排序使用ORDER BY time_at desc，行顺序完全反向，即便逻辑正确也无法得到预期结果
• 子查询读取的是更新前的表数据，所有current_subscribers初始值都是0，LAG取到的上一行值自然始终为0，无法实现逐行累加的效果

正确实现方案 #

你需要的累计结果本质是从最早时间点到当前行的净订阅数累计和，不需要依赖更新后的current_subscribers字段，直接基于原始的calc_subscribers和calc_unsubscribers用窗口聚合函数SUM一次性计算即可，SQL如下：

UPDATE subscribers sa
SET current_subscribers = COALESCE(sub.running_total, 0)
FROM (
    SELECT 
        time_at,
        SUM(calc_subscribers - calc_unsubscribers) OVER (
            -- 按时间正序排列，从最早行累加到当前行
            ORDER BY time_at ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            -- 如果需要按其他维度分区（比如不同业务线、不同产品），可补充PARTITION BY 对应字段
        ) AS running_total
    FROM subscribers
) sub
WHERE sa.time_at = sub.time_at;

逻辑验证 #

以你的测试数据为例，计算过程完全匹配需求：

1. 2021-07-02 07:30:00：(0-0) = 0
2. 2021-07-02 07:45:00：(0-0)+(39-8) = 31
3. 2021-07-02 08:00:00：31 + (69-17) = 83
4. 2021-07-02 08:15:00：83 + (67-21) = 129
5. 2021-07-02 08:30:00：129 + (48-95) = 82
   注：你给出的预期结果中最后一行calc_unsubscribers为95，若按原始表的23计算则结果为154，可根据实际数据调整。

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