SQL Server 产品表缺失财月行的默认值插入实现方案咨询

核心逻辑 #

• 生成全量「产品+财月」的基准映射：取产品表所有去重产品值，和财年日历表的所有财月维度字段做关联，得到每个产品理论上应该存在的所有财月行
• 左匹配现有产品表数据，过滤出现有表中不存在的「产品+财年月」组合，即为待补全的缺失行
• 缺失行的非财月字段（COL1/COL2/COL3/COL4/COL5）按业务规则填充默认值，财月字段直接取自财年日历表，天然适配不同财年的财月起止日期差异
• 将构造完成的缺失行插入原产品表即可

参考SQL实现 #

版本1：补全所有财年的所有财月 #

适配Hive/Spark SQL/MySQL 8.0+等支持CROSS JOIN的SQL引擎，可按需调整默认值和财年过滤范围：

INSERT INTO product_data (
    COL1, COL2, COL3, PRODUCT, COL4, COL5,
    FM_START, FM_END, FM, FM_NAME, FY
)
SELECT
    -- 非财月字段默认值可根据业务需求修改，支持替换为0、空字符串等
    NULL AS COL1,
    NULL AS COL2,
    NULL AS COL3,
    full_map.PRODUCT,
    NULL AS COL4,
    NULL AS COL5,
    full_map.FM_START,
    full_map.FM_END,
    full_map.FM,
    full_map.FM_NAME,
    full_map.FY
FROM (
    -- 生成全量产品+财月映射
    SELECT p.PRODUCT, c.FM_START, c.FM_END, c.FM, c.FM_NAME, c.FY
    FROM (SELECT DISTINCT PRODUCT FROM product_data) p
    CROSS JOIN (
        SELECT DISTINCT FM_START, FM_END, FM, FM_NAME, FY 
        FROM fiscal_calendar
        -- 可选：限定财年范围减少计算量，示例：WHERE FY BETWEEN 2020 AND 2025
    ) c
) full_map
-- 匹配现有数据过滤缺失行
LEFT JOIN product_data exist
    ON full_map.PRODUCT = exist.PRODUCT
    AND full_map.FY = exist.FY
    AND full_map.FM = exist.FM
WHERE exist.PRODUCT IS NULL;

版本2：仅补全产品生命周期内的财月 #

如果不需要补产品上线前/下线后的财月，可以使用此优化版本，避免生成无效行：

INSERT INTO product_data (
    COL1, COL2, COL3, PRODUCT, COL4, COL5,
    FM_START, FM_END, FM, FM_NAME, FY
)
SELECT
    NULL AS COL1,
    NULL AS COL2,
    NULL AS COL3,
    full_map.PRODUCT,
    NULL AS COL4,
    NULL AS COL5,
    full_map.FM_START,
    full_map.FM_END,
    full_map.FM,
    full_map.FM_NAME,
    full_map.FY
FROM (
    SELECT p.PRODUCT, c.FM_START, c.FM_END, c.FM, c.FM_NAME, c.FY
    FROM (
        -- 计算每个产品的首末次出现财月范围
        SELECT 
            PRODUCT,
            MIN(FY*100 + FM) AS min_fm_code,
            MAX(FY*100 + FM) AS max_fm_code
        FROM product_data
        GROUP BY PRODUCT
    ) p
    JOIN (
        SELECT 
            FM_START, FM_END, FM, FM_NAME, FY,
            FY*100 + FM AS fm_code
        FROM fiscal_calendar
    ) c ON c.fm_code BETWEEN p.min_fm_code AND p.max_fm_code
) full_map
LEFT JOIN product_data exist
    ON full_map.PRODUCT = exist.PRODUCT
    AND full_map.FY = exist.FY
    AND full_map.FM = exist.FM
WHERE exist.PRODUCT IS NULL;

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