SQL如何为大型数据集补全活动、年月维度缺失的count记录行

• 提取全量活动维度：去重获取数据集内所有存在的活动ID
• 提取全量年月维度：去重获取数据集内所有已出现的年份+月份组合，后续数据新增年月会自动纳入，无需硬编码
• 两个维度做笛卡尔积，得到「活动+月份+年份」的所有应存在的完整组合
• 左关联原始业务表，匹配不到的Count值用COALESCE填充为0，完成补全

WITH 
-- 全量活动集合
all_activities AS (
    SELECT DISTINCT Activity FROM your_table_name
),
-- 全量年月集合
all_year_months AS (
    SELECT DISTINCT Month, year FROM your_table_name
),
-- 三个维度的完整组合全集
full_dimension_combinations AS (
    SELECT 
        a.Activity,
        ym.Month,
        ym.year
    FROM all_activities a
    CROSS JOIN all_year_months ym
)
-- 关联补全得到最终完整数据集
SELECT 
    fc.Activity,
    fc.Month,
    fc.year,
    COALESCE(t.Count, 0) AS Count
FROM full_dimension_combinations fc
LEFT JOIN your_table_name t
    ON fc.Activity = t.Activity
    AND fc.Month = t.Month
    AND fc.year = t.year
ORDER BY Activity, year, Month;

• 如果仅需要提取缺失的待插入记录，在最终查询的WHERE条件中添加WHERE t.Count IS NULL即可，得到的就是所有需要补的0值行
• 方案完全适配数据动态增长场景，后续新增活动、新增年月都不需要修改SQL逻辑
• 性能优化建议：给原表的Activity、Month、year字段添加联合索引，可大幅提升大数量级下的查询效率
• 语法兼容MySQL 8.0+、PostgreSQL、Spark SQL、Hive、Oracle等绝大多数支持CTE的数据库，不支持CTE的低版本数据库可直接将CTE改写为子查询，逻辑完全一致

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