基于现有列值新增列的PostgreSQL实现问题

你的问题在于LAG(VALUE)只能获取前一行的值，当遇到连续NULL时，前一行也是NULL，因此COALESCE无法得到有效的前置非NULL值。要实现「向前填充最近的非NULL值」，可以使用以下两种方案：

方案1：使用LAST_VALUE + IGNORE NULLS（PostgreSQL 13及以上版本） #

PostgreSQL 13开始支持窗口函数的IGNORE NULLS选项，可直接忽略NULL值，取当前行及之前最近的非NULL值：

SELECT
  ID,
  VALUE,
  LAST_VALUE(VALUE) OVER (
    ORDER BY ID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    IGNORE NULLS
  ) AS VALUE_NOW
FROM my_table;

方案2：兼容低版本PostgreSQL的分组填充法 #

如果你的PostgreSQL版本低于13，可以通过标记非NULL值的分组，再取组内的非NULL值来实现：

WITH grouped_data AS (
  SELECT
    ID,
    VALUE,
    -- 每遇到非NULL值，分组编号+1，将连续NULL值归到最近的非NULL值分组中
    SUM(CASE WHEN VALUE IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY ID) AS group_id
  FROM my_table
)
SELECT
  ID,
  VALUE,
  MAX(VALUE) OVER (PARTITION BY group_id) AS VALUE_NOW
FROM grouped_data
ORDER BY ID;

验证结果 #

两种方案均可得到预期输出：

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