在Oracle SQL中按月度生成缺失日期数据的实现需求

原表结构与数据 #

CREATE TABLE table_name (a, b, c, ym) AS
SELECT 1, 2,  1, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 1, 2,  7, DATE '2024-09-01' FROM DUAL UNION ALL
SELECT 2, 2,  8, DATE '2024-04-01' FROM DUAL;

需求 #

为每个唯一的a、b组合，仅在其有数据的年份范围内生成所有缺失月份，缺失行的c列值设为0。

解决方案 #

通过生成日期维度、匹配分组时间范围再左连接的方式实现：

WITH ab_year_range AS (
    -- 获取每个a,b组合的最小和最大年份，确定补全范围
    SELECT 
        a,
        b,
        EXTRACT(YEAR FROM MIN(ym)) AS min_year,
        EXTRACT(YEAR FROM MAX(ym)) AS max_year
    FROM table_name
    GROUP BY a, b
),
all_months AS (
    -- 生成每个a,b组合对应年份内的所有月度第一天日期
    SELECT 
        ab.a,
        ab.b,
        ADD_MONTHS(TO_DATE(ab.min_year || '-01-01', 'YYYY-MM-DD'), LEVEL - 1) AS ym
    FROM ab_year_range ab
    CONNECT BY 
        LEVEL <= (ab.max_year - ab.min_year + 1)*12
        AND PRIOR ab.a = ab.a
        AND PRIOR ab.b = ab.b
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免循环连接
)
-- 左连接原表，填充缺失的c值为0
SELECT 
    am.a,
    am.b,
    NVL(tn.c, 0) AS c,
    am.ym
FROM all_months am
LEFT JOIN table_name tn 
    ON am.a = tn.a 
    AND am.b = tn.b 
    AND am.ym = tn.ym
ORDER BY am.a, am.b, am.ym;

思路说明 #

1. ab_year_range：按a、b分组，锁定每个组合有数据的年份区间，避免生成无关年份的日期。
2. all_months：借助Oracle的CONNECT BY递归语法，生成区间内的所有月度日期。
3. 最终查询：将全量月度数据与原表左关联，用NVL把缺失的c值替换为0，最后按分组和日期排序输出。

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