如何补全各ID连续日期行？SQL基于日历表填充0值实现

需求背景 #

业务表部分数据（以ID=1为例）：

每个ID的日期不连续，需要补全该ID最小到最大日期之间的所有缺失日期，对应value填充为0，最终效果如下：

现有calendar表存储连续日期（示例数据）：

解决方案 #

方案1：查询补全后的结果（不修改原表） #

通过CTE生成每个ID的完整日期序列，左连接业务表填充0值：

-- 统计每个ID的日期范围
WITH id_date_ranges AS (
    SELECT 
        id,
        MIN(date) AS min_date,
        MAX(date) AS max_date
    FROM business_table  -- 替换成你的业务表名
    GROUP BY id
),
-- 生成每个ID在其范围内的所有日期
id_full_calendar AS (
    SELECT 
        dr.id,
        c.date
    FROM id_date_ranges dr
    CROSS JOIN calendar c
    WHERE c.date BETWEEN dr.min_date AND dr.max_date
)
-- 左连接业务表，填充缺失的value为0
SELECT 
    fc.date,
    fc.id,
    COALESCE(bt.value, 0) AS value
FROM id_full_calendar fc
LEFT JOIN business_table bt 
    ON fc.id = bt.id AND fc.date = bt.date
ORDER BY fc.id, fc.date;

方案2：将缺失行插入业务表（修改原表） #

如果需要把补全的行直接插入业务表，用以下语句：

WITH id_date_ranges AS (
    SELECT 
        id,
        MIN(date) AS min_date,
        MAX(date) AS max_date
    FROM business_table  -- 替换成你的业务表名
    GROUP BY id
),
id_full_calendar AS (
    SELECT 
        dr.id,
        c.date
    FROM id_date_ranges dr
    CROSS JOIN calendar c
    WHERE c.date BETWEEN dr.min_date AND dr.max_date
)
-- 只插入业务表中不存在的日期行
INSERT INTO business_table (date, id, value)
SELECT 
    fc.date,
    fc.id,
    0 AS value
FROM id_full_calendar fc
LEFT JOIN business_table bt 
    ON fc.id = bt.id AND fc.date = bt.date
WHERE bt.id IS NULL;

说明 #

• 请将business_table替换为你实际的业务表名称
• calendar表需要包含足够覆盖所有ID日期范围的连续日期，如果现有calendar表日期不足，需要先扩展该表的日期数据
• 方案1仅返回补全后的查询结果，不会修改原表；方案2会向业务表插入缺失行，执行前建议先备份数据

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