使用OVER函数与generate_series补全无数据日期的前一日数据

嘿，我来帮你搞定这个问题！你说的是当数据库里某天没有数据时，要自动取前一天的数值填充对吧？我之前也处理过类似的需求，给你分享一个可行的实现思路和具体代码：

核心逻辑其实分三步：先把所有需要的日期都列出来（哪怕原表没数据），再关联原表的数据，最后用窗口函数把前一个非空的数值“带”到缺失的日期上。

1. 先生成连续的日期范围 #

首先得确保我们有目标时间段内的每一天，不然缺失的日期根本不会出现在结果里。用PostgreSQL的generate_series就能轻松生成连续日期，比如要查2023-01-01到2023-01-05的日期：

SELECT generate_series('2023-01-01'::date, '2023-01-05'::date, '1 day'::interval) AS date

这个语句会输出这段时间的每一天，不管原表有没有对应数据。

2. 关联原表的数据 #

把生成的连续日期和你的数据表做左连接，这样缺失数据的日期对应的数值就会是NULL，方便后续填充：

WITH date_range AS (
    SELECT generate_series('2023-01-01'::date, '2023-01-05'::date, '1 day'::interval)::date AS date
),
raw_combined AS (
    SELECT dr.date, dd.your_value_column AS value
    FROM date_range dr
    LEFT JOIN your_table_name dd ON dr.date = dd.date_column
)
SELECT * FROM raw_combined;

这里记得把your_value_column、your_table_name和date_column换成你实际的列名和表名哦。

3. 用窗口函数填充缺失值 #

这一步是关键，用LAST_VALUE窗口函数配合IGNORE NULLS（PostgreSQL 11及以上版本支持），就能自动取前一个非空的数值填充当前的NULL：

WITH date_range AS (
    SELECT generate_series('2023-01-01'::date, '2023-01-05'::date, '1 day'::interval)::date AS date
),
raw_combined AS (
    SELECT dr.date, dd.your_value_column AS value
    FROM date_range dr
    LEFT JOIN your_table_name dd ON dr.date = dd.date_column
)
SELECT 
    date,
    LAST_VALUE(value) OVER (
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS filled_value
FROM raw_combined;

如果你的PostgreSQL版本比较旧，不支持IGNORE NULLS，可以用递归CTE的方式来实现：

WITH date_range AS (
    SELECT generate_series('2023-01-01'::date, '2023-01-05'::date, '1 day'::interval)::date AS date
),
raw_combined AS (
    SELECT dr.date, dd.your_value_column AS value
    FROM date_range dr
    LEFT JOIN your_table_name dd ON dr.date = dd.date_column
),
filled_data AS (
    -- 先取所有有数据的行
    SELECT 
        date, 
        value,
        value AS filled_value
    FROM raw_combined
    WHERE value IS NOT NULL
    UNION ALL
    -- 递归填充缺失的日期，取前一天的filled_value
    SELECT 
        rc.date, 
        rc.value,
        fd.filled_value
    FROM raw_combined rc
    JOIN filled_data fd ON rc.date = fd.date + INTERVAL '1 day'
    WHERE rc.value IS NULL
)
SELECT date, filled_value
FROM filled_data
ORDER BY date;

为啥你之前用OVER函数没成功？ #

大概率是没先生成连续的日期序列！直接在原表的非连续日期上用窗口函数的话，缺失的日期根本不会出现在结果里，自然没法填充。另外如果没加IGNORE NULLS，LAST_VALUE会把NULL也算进去，导致填充失效。

内容的提问来源于stack exchange，提问作者Mateusz Urbański