SQL如何补全缺失日期并向前复制对应字段数值

核心逻辑 #

实现该需求总共分为3个核心步骤，适配绝大多数支持窗口函数的SQL引擎：

• 生成覆盖原数据集最小日期到最大日期的连续完整日期序列
• 将连续日期序列与原数据表左关联，得到包含所有日期、以及原表对应非空值的中间表
• 用窗口函数填充中间表中的空值，空缺日期的value直接复用最近一个非空日期的对应值

代码实现示例 #

通用兼容方案（适配所有支持窗口函数的SQL引擎） #

该方案无需依赖IGNORE NULLS等特殊参数，兼容性最高，原表名默认为original_data：

WITH 
-- 步骤1：生成连续日期序列，MySQL/PostgreSQL可直接用该递归CTE，其他引擎可替换为对应日期生成逻辑
date_range AS (
    SELECT MIN(date) AS full_date FROM original_data
    UNION ALL
    SELECT DATE_ADD(full_date, INTERVAL 1 DAY) 
    FROM date_range
    WHERE full_date < (SELECT MAX(date) FROM original_data)
),
-- 步骤2：左关联原表得到带空值的全日期表
joined_data AS (
    SELECT 
        dr.full_date,
        od.value1,
        od.value2,
        -- 给value1和value2分别打分组标记，同组内所有行共用同一个非空起始值
        SUM(CASE WHEN value1 IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY full_date) AS v1_group,
        SUM(CASE WHEN value2 IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY full_date) AS v2_group
    FROM date_range dr
    LEFT JOIN original_data od ON dr.full_date = od.date
)
-- 步骤3：按组取非空值填充
SELECT 
    full_date AS date,
    MAX(value1) OVER (PARTITION BY v1_group) AS value1,
    MAX(value2) OVER (PARTITION BY v2_group) AS value2
FROM joined_data
ORDER BY full_date;

Hive/Spark SQL简化写法 #

如果使用Hive或Spark SQL，可以直接用内置函数简化日期生成和空值填充逻辑：

WITH date_range AS (
    -- 直接用sequence函数生成连续日期序列
    SELECT explode(sequence(
        (SELECT MIN(date) FROM original_data),
        (SELECT MAX(date) FROM original_data),
        INTERVAL 1 DAY
    )) AS full_date
),
joined_data AS (
    SELECT dr.full_date, od.value1, od.value2
    FROM date_range dr
    LEFT JOIN original_data od ON dr.full_date = od.date
)
-- 用last函数的第二个参数指定忽略空值，直接取最近非空值
SELECT 
    full_date AS date,
    last(value1, true) OVER (ORDER BY full_date) AS value1,
    last(value2, true) OVER (ORDER BY full_date) AS value2
FROM joined_data
ORDER BY full_date;

注意：如果需要补全的日期范围超出原表的最大/最小日期，只需要修改date_range的生成逻辑，把起止日期改成你需要的范围即可。

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