SQL表中日期间隙填充的最优实现方案

核心思路 #

先通过窗口函数LEAD获取每条状态记录的下一次状态变更日期，以此确定当前状态的生效时间范围（从当前记录日期到下一次变更前一天）；再将这个时间范围与你的日期维度表关联，筛选出范围内的所有日期，就能生成每日的客户状态记录。

假设表结构 #

• 客户状态变更表：命名为customer_status_log，字段ID（客户ID）、logdate（状态变更日期）、status（客户状态）
• 已有日期维度表：命名为date_dim，包含连续日期的字段date

实现SQL #

WITH status_with_end_date AS (
    SELECT
        ID,
        logdate AS start_date,
        status,
        -- 获取下一次状态变更的日期，最后一条记录设为当前日期（或你需要的截止日期）
        LEAD(logdate, 1, CURRENT_DATE()) OVER (PARTITION BY ID ORDER BY logdate) AS end_date
    FROM customer_status_log
)
SELECT
    s.ID,
    d.date AS logdate,
    s.status
FROM status_with_end_date s
JOIN date_dim d
    ON d.date >= s.start_date
    -- 最后一条记录的end_date是当前日期，所以用<=；非最后一条用<，因为下一次变更当天的状态属于新记录
    AND (d.date < s.end_date OR (s.end_date = CURRENT_DATE() AND d.date <= s.end_date))
ORDER BY s.ID, d.date;

代码说明 #

1. CTE部分：用LEAD窗口函数按客户ID分组、日期排序，获取每条记录的下一次变更日期。如果是该客户的最后一条状态记录，LEAD的默认值设为CURRENT_DATE()，确保能覆盖到当前日期的所有记录。
2. 关联日期维度表：筛选日期维度表中落在当前状态生效区间内的日期。对于非最后一条记录，日期要小于下一次变更日期（因为下一次变更当天的状态已经在原表中有记录）；对于最后一条记录，日期可以等于当前日期，保证最新状态持续到当天。

适配不同SQL方言的小调整 #

• MySQL 8.0+：上述代码直接可用，CURRENT_DATE()可以替换为CURDATE()
• PostgreSQL：CURRENT_DATE()直接可用，逻辑完全一致
• SQL Server：CURRENT_DATE()替换为CAST(GETDATE() AS DATE)

测试结果验证 #

用你提供的测试数据执行后，会生成符合需求的每日记录：

• 2021-07-06至2021-11-11：状态为active
• 2021-11-12至2022-05-19：状态为inactive
• 2022-05-20至当前日期：状态为active

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