PostgreSQL：按分组及日期顺序用前序非NULL值填充NULL

要实现按id分组、按date排序后，用分组内前序非NULL值填充各列的NULL（保持原数据类型），可以用窗口函数高效处理。

测试表结构与数据 #

CREATE TABLE IF NOT EXISTS test (
   id VARCHAR,
   date DATE,
   value_1 INT,
   value_2 VARCHAR
);

INSERT INTO test VALUES 
(1, '2022-01-04', 5, 'asdf'),
(1, '2022-01-03', NULL, NULL),
(1, '2022-01-02', NULL, 'def'),
(1, '2022-01-01', 4, NULL),
(2, '2022-01-04', 1, 'a'),
(2, '2022-01-03', NULL, NULL),
(2, '2022-01-02', 2, 'b'),
(2, '2022-01-01', NULL, NULL);

解决方案（PostgreSQL 11+） #

利用LAST_VALUE窗口函数结合IGNORE NULLS参数，直接取分组内当前行之前的最后一个非NULL值：

SELECT 
    id,
    date,
    LAST_VALUE(value_1) OVER (
        PARTITION BY id 
        ORDER BY date ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS value_1,
    LAST_VALUE(value_2) OVER (
        PARTITION BY id 
        ORDER BY date ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS value_2
FROM test
ORDER BY id, date DESC;

关键参数说明 #

• PARTITION BY id：将数据按id拆分，确保只在同一分组内填充
• ORDER BY date ASC：分组内按日期升序排列，保证"前序"是更早的记录
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：窗口范围包含当前行及所有之前的行
• IGNORE NULLS：让LAST_VALUE跳过NULL值，只取最近的有效非NULL值

兼容低版本PostgreSQL（<11） #

如果你的PostgreSQL版本不支持IGNORE NULLS，可以通过分组计数的方式间接实现：

SELECT 
    id,
    date,
    MAX(value_1) OVER (PARTITION BY id, grp1) AS value_1,
    MAX(value_2) OVER (PARTITION BY id, grp2) AS value_2
FROM (
    SELECT 
        id,
        date,
        value_1,
        value_2,
        -- 每遇到非NULL的value_1，计数加1，将连续NULL划到同一组
        COUNT(value_1) OVER (PARTITION BY id ORDER BY date ASC) AS grp1,
        COUNT(value_2) OVER (PARTITION BY id ORDER BY date ASC) AS grp2
    FROM test
) t
ORDER BY id, date DESC;

期望结果 #

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