构建含缺失值的数据网格：多表聚合补全方案咨询

背景与已知条件 #

源表结构如下：

• 两张表均包含dim_1、dim_2和metric_1，仅Table B包含dim_3和dim_4；
• 可访问维度表dim_3_4，包含dim_3和dim_4的所有可能组合；
• Table A包含dim_1和dim_2的完整组合，Table B的dim_1、dim_2组合是Table A的子集。

需求 #

1. 对Table B的metric_1按两组维度组合求和：(dim_1, dim_2)、(dim_1, dim_2, dim_3, dim_4)，同时生成"All products"汇总行；
2. 对Table A的metric_1按dim_1、dim_2求和得到总计值；
3. 关联上述两个结果，补全Table B缺失的dim_1、dim_2组合：
   • 缺失组合的b_metric_1填充为0；
   • 为缺失组合生成所有dim_3、dim_4的组合行，以及对应的"All products"汇总行。

解决方案 #

核心思路是先构建完整的维度网格，再通过左关联补全缺失的聚合值，最后关联Table A的总计。以下是完整SQL实现：

WITH a_totals AS (
    -- 预计算Table A的dim1+dim2总计
    SELECT
        dim_1,
        dim_2,
        SUM(metric_1) AS a_metric_1
    FROM table_a
    GROUP BY dim_1, dim_2
),
b_aggregations AS (
    -- 预计算Table B的分组汇总，标记聚合层级
    SELECT
        dim_1,
        dim_2,
        dim_3,
        dim_4,
        SUM(metric_1) AS b_metric_1,
        CASE 
            WHEN GROUPING(dim_3) = 1 THEN 'All products'
            ELSE 'Single product'
        END AS aggregation_level
    FROM table_b
    GROUP BY GROUPING SETS (
        (dim_1, dim_2),
        (dim_1, dim_2, dim_3, dim_4)
    )
),
full_dim_grid AS (
    -- 构建完整的维度组合网格：包含所有dim1+dim2，以及两种聚合层级
    SELECT
        'Single product' AS aggregation_level,
        a.dim_1,
        a.dim_2,
        d.dim_3,
        d.dim_4
    FROM a_totals a
    CROSS JOIN dim_3_4 d
    UNION ALL
    SELECT
        'All products' AS aggregation_level,
        dim_1,
        dim_2,
        'All' AS dim_3,
        'All' AS dim_4
    FROM a_totals
)
-- 关联所有数据，补全缺失值
SELECT
    f.aggregation_level,
    f.dim_1,
    f.dim_2,
    f.dim_3,
    f.dim_4,
    COALESCE(b.b_metric_1, 0) AS b_metric_1,
    a.a_metric_1
FROM full_dim_grid f
LEFT JOIN b_aggregations b
    ON f.dim_1 = b.dim_1
    AND f.dim_2 = b.dim_2
    AND f.aggregation_level = b.aggregation_level
    AND (
        (f.aggregation_level = 'Single product' AND f.dim_3 = b.dim_3 AND f.dim_4 = b.dim_4)
        OR f.aggregation_level = 'All products'
    )
JOIN a_totals a
    ON f.dim_1 = a.dim_1
    AND f.dim_2 = a.dim_2
ORDER BY f.dim_1, f.dim_2, f.aggregation_level, f.dim_3, f.dim_4;

方案说明 #

1. a_totals：提前计算Table A的维度总计，避免重复计算；
2. b_aggregations：用GROUPING SETS生成Table B的两种聚合结果，并标记层级，方便后续匹配；
3. full_dim_grid：通过CROSS JOIN和UNION ALL生成所有需要的维度组合，确保覆盖Table A的全部dim_1、dim_2，以及两种聚合层级的所有可能；
4. 最终关联：用LEFT JOIN关联Table B的聚合结果，缺失的组合用COALESCE填充0，再直接关联a_totals获取对应总计值。

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