PostgreSQL如何将0值myval更新为按myid排序的上一个非零值

方案1：CTE+窗口函数实现（推荐，性能更优） #

核心思路是先通过分组窗口函数给所有行打分组标签，每遇到一个非零值就切换分组，同一分组内的所有0值都会填充该分组的第一个非零值。

WITH grouped AS (
    SELECT 
        myid,
        myval,
        SUM(CASE WHEN myval != 0 THEN 1 ELSE 0 END) OVER (ORDER BY myid) AS val_group
    FROM mytable
),
filled_values AS (
    SELECT 
        myid,
        MAX(myval) OVER (PARTITION BY val_group) AS filled_val
    FROM grouped
    WHERE myval = 0
)
UPDATE mytable t
SET myval = f.filled_val
FROM filled_values f
WHERE t.myid = f.myid
RETURNING t.*;

方案2：LATERAL JOIN 实现（符合你提到的写法思路） #

逐行匹配当前行之前最近的非零值，逻辑更直观，适合小数据量场景：

UPDATE mytable t1
SET myval = t2.prev_non_zero
FROM LATERAL (
    SELECT myval AS prev_non_zero
    FROM mytable t2
    WHERE t2.myid < t1.myid 
      AND t2.myval != 0
    ORDER BY t2.myid DESC
    LIMIT 1
) t2
WHERE t1.myval = 0
RETURNING t1.*;

注意事项 #

• 若最小myid对应的myval为0，两种方案都不会更新首行的0值，如有需要可额外加逻辑赋值默认值。
• 数据量较大时优先选方案1，仅需两次全表扫描即可完成计算，方案2对每个需要更新的0值都会执行一次子查询，性能更低。
• 正式执行更新前可单独运行CTE或LATERAL的SELECT部分，确认填充值符合预期后再执行更新操作。

内容的提问来源于stack exchange，提问作者Héctor