PostgreSQL中生成连续日期并填充列NULL值的SQL实现方法

需求回顾 #

现有含date（日期）和value（数值）两列的表，需完成以下操作：

1. 生成2023-04-01至2023-04-10的所有日期记录
2. 关联原表，无对应日期的value设为NULL
3. 填充NULL：优先取最近的前一个非NULL值；若为起始段无前置非NULL值，则取最近的后一个非NULL值

完整SQL实现 #

假设原表名为original_table，执行以下SQL即可得到目标结果：

WITH date_series AS (
    -- 生成指定日期范围内的所有日期
    SELECT generate_series(
        '2023-04-01'::DATE,
        '2023-04-10'::DATE,
        '1 day'::INTERVAL
    ) AS date
),
joined_data AS (
    -- 关联原表，保留所有日期，无匹配则value为NULL
    SELECT ds.date, ot.value
    FROM date_series ds
    LEFT JOIN original_table ot ON ds.date = ot.date
),
forward_filled AS (
    -- 向前填充：取当前行之前最近的非NULL值
    SELECT 
        date,
        last_value(value) OVER (
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        ) AS filled_value
    FROM joined_data
)
-- 处理开头的NULL：向前填充后仍为NULL的行，取后续最近的非NULL值
SELECT 
    date,
    coalesce(
        filled_value,
        first_value(filled_value) OVER (
            ORDER BY date 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        )
    ) AS value
FROM forward_filled
ORDER BY date;

分步解释 #

1. 生成日期序列
   用generate_series函数直接生成指定起止日期的连续日期，这是PostgreSQL生成日期范围的高效方式，替换参数可调整日期区间。

2. 关联原表
   通过左连接（LEFT JOIN）将日期序列与原表关联，确保所有日期都被保留，原表无对应日期的value自动设为NULL，完全匹配需求第二步。

3. 双向填充NULL

   • 先通过last_value窗口函数结合IGNORE NULLS实现向前填充：每一行取从起始行到当前行的最后一个非NULL值，覆盖中间和末尾的NULL。
   • 再用coalesce配合first_value窗口函数处理开头的NULL：对向前填充后仍为NULL的行，取从当前行到末尾的第一个非NULL值，完成起始段的填充。

注意事项 #

• 需使用PostgreSQL 11及以上版本，因为IGNORE NULLS在窗口函数中的支持从11版本开始。
• 请将original_table替换为你的实际表名，若日期列或数值列名称不同，也需对应修改。

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