PostgreSQL 12.3补全缺失日期并按前后行平均值填充值

实现思路 #

要完成需求，核心分为三个步骤：

1. 生成目标ID对应的完整日期序列（覆盖该ID的最小到最大日期）
2. 关联原数据表，定位缺失的日期记录
3. 利用窗口函数获取缺失日期前后的有效Value值，计算平均值填充

完整SQL实现 #

假设你的数据表名为data_table，字段为id、value、record_date（替换为你实际的表名和字段名），目标ID为8445，SQL代码如下：

WITH date_range AS (
    -- 生成目标ID的完整日期序列
    SELECT generate_series(
        (SELECT MIN(record_date) FROM data_table WHERE id = 8445),
        (SELECT MAX(record_date) FROM data_table WHERE id = 8445),
        INTERVAL '1 day'
    )::date AS full_date
),
joined_data AS (
    -- 关联原数据，标记缺失记录
    SELECT
        8445 AS id,
        d.full_date,
        t.value
    FROM date_range d
    LEFT JOIN data_table t ON d.full_date = t.record_date AND t.id = 8445
)
-- 填充缺失值，计算前后行平均值
SELECT
    id,
    CASE
        WHEN value IS NOT NULL THEN value
        ELSE (LAG(value) OVER (ORDER BY full_date DESC) + LEAD(value) OVER (ORDER BY full_date DESC)) / 2
    END AS value,
    full_date AS "Date"
FROM joined_data
ORDER BY full_date DESC;

代码说明 #

1. date_range CTE：通过generate_series生成从目标ID最早日期到最晚日期的所有连续日期，确保无遗漏。
2. joined_data CTE：将完整日期序列与原表左关联，原表存在的日期保留Value，缺失日期的Value为NULL。
3. 最终查询：用CASE判断，若Value非空直接保留；若为空，通过LAG()取前一行（日期更大的行）的Value，LEAD()取后一行（日期更小的行）的Value，二者取平均作为填充值。

测试示例 #

输入数据（简化版） #

id     value       record_date
8445    0.0000      "2023-01-25"
8445    0.0000      "2023-01-24"
8445    0.0000      "2023-01-22"
8445    0.0000      "2023-01-20"

输出结果（简化版） #

id     value       Date
8445    0.0000      "2023-01-25"
8445    0.0000      "2023-01-24"
8445    0.0000      "2023-01-23"  -- 填充值为2023-01-24和2023-01-22的平均值
8445    0.0000      "2023-01-22"
8445    0.0000      "2023-01-21"  -- 填充值为2023-01-22和2023-01-20的平均值
8445    0.0000      "2023-01-20"

注意事项 #

• 若目标ID存在连续多天缺失，LAG()和LEAD()会自动取最近的非空值计算平均值，保证填充逻辑有效。
• 确保record_date为date类型，若存储为字符串，需通过::date转换为日期类型。
• 如需处理多个ID，可调整CTE中的过滤条件，或按ID分组生成对应日期序列。

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