如何利用Date表补全源数据中截至当前的缺失日期记录

需求说明 #

源订单数据存在日期间断，需补全从订单最早日期到当前日期的所有日期记录，缺失日期的Sales、Order_Qunty字段填充为0，需借助包含19000101至20991231所有日期的Date表实现。

源订单表（假设表名为order_data） #

预期输出 #

SQL 实现方案 #

场景1：日期字段为8位数字/字符串类型

SELECT
    COALESCE(o.Sales, 0) AS Sales,
    COALESCE(o.Order_Qunty, 0) AS Order_Qunty,
    d.date_val AS Date
FROM
    -- 筛选目标日期范围：订单最早日期到当前日期
    (SELECT date_val
     FROM Date
     WHERE date_val >= (SELECT MIN(Date) FROM order_data)
       AND date_val <= DATE_FORMAT(CURRENT_DATE(), '%Y%m%d')) d
LEFT JOIN
    order_data o ON d.date_val = o.Date
ORDER BY
    d.date_val;

场景2：日期字段为DATE类型

如果Date表的date_val是DATE类型，订单表的Date是8位字符串，需做格式转换：

SELECT
    COALESCE(o.Sales, 0) AS Sales,
    COALESCE(o.Order_Qunty, 0) AS Order_Qunty,
    DATE_FORMAT(d.date_val, '%Y%m%d') AS Date
FROM
    (SELECT date_val
     FROM Date
     WHERE date_val >= (SELECT STR_TO_DATE(MIN(Date), '%Y%m%d') FROM order_data)
       AND date_val <= CURRENT_DATE()) d
LEFT JOIN
    order_data o ON DATE_FORMAT(d.date_val, '%Y%m%d') = o.Date
ORDER BY
    d.date_val;

关键逻辑说明 #

1. 生成连续日期序列：从Date表中筛选出需要补全的日期范围，确保覆盖订单最早日期到当前日期的所有天数。
2. 关联订单数据：使用LEFT JOIN保留所有连续日期，匹配到订单数据则取原值，未匹配到则返回NULL。
3. 填充默认值：通过COALESCE函数将NULL值替换为0，满足缺失日期字段的填充要求。
4. 排序输出：按日期排序，保证结果的连续性。

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