能否用GROUPING SETS替代UNION ALL实现多关联聚合？

问题原因 #

多表关联（如TABLE_1关联TABLE_2、TABLE_3）会产生笛卡尔积，导致同一个T1_ID的VAL被重复输出多次。GROUPING SETS基于全关联后的重复数据集聚合时，会多次累加同一个VAL，最终出现数值翻倍（或倍数放大）的问题。而UNION ALL是分别针对单个/组合维度关联聚合，避免了跨维度关联带来的重复行，因此结果正确。

解决方案 #

核心思路是消除重复行对聚合的影响：先计算每个T1_ID在关联后的重复次数，将VAL按重复次数拆分后再求和，确保每个T1_ID的VAL仅被累计一次。

具体实现SQL #

WITH TABLE_1 AS ( --data
SELECT 1 T1_ID, 2 VAL UNION
SELECT 2 T1_ID, 4 VAL UNION
SELECT 3 T1_ID, 6 VAL UNION
SELECT 4 T1_ID, 8 VAL UNION
SELECT 5 T1_ID, 10 VAL
),
TABLE_2 AS ( --first level join
SELECT 1 T1_ID, 'AA' T2_ID UNION
SELECT 2 T1_ID, 'AA' T2_ID UNION
SELECT 3 T1_ID, 'BB' T2_ID UNION
SELECT 4 T1_ID, 'BB' T2_ID UNION
SELECT 5 T1_ID, 'BB' T2_ID
),
TABLE_3 AS ( --second level join
SELECT 1 T1_ID, 'CCC' T3_ID UNION
SELECT 2 T1_ID, 'CCC' T3_ID UNION
SELECT 3 T1_ID, 'CCC' T3_ID UNION
SELECT 4 T1_ID, 'CCC' T3_ID UNION
SELECT 5 T1_ID, 'CCC' T3_ID UNION
SELECT 1 T1_ID, 'DDD' T3_ID UNION
SELECT 2 T1_ID, 'DDD' T3_ID UNION
SELECT 3 T1_ID, 'DDD' T3_ID UNION
SELECT 4 T1_ID, 'DDD' T3_ID UNION
SELECT 5 T1_ID, 'DDD' T3_ID 
),
-- 计算每个T1_ID的重复次数（由多表关联的笛卡尔积导致）
base_data AS (
    SELECT 
        t1.T1_ID,
        t1.VAL,
        t2.T2_ID,
        t3.T3_ID,
        -- 统计当前T1_ID在关联后的总重复行数
        COUNT(*) OVER (PARTITION BY t1.T1_ID) AS repeat_count
    FROM TABLE_1 t1
    LEFT JOIN TABLE_2 t2 ON t2.T1_ID = t1.T1_ID
    LEFT JOIN TABLE_3 t3 ON t3.T1_ID = t1.T1_ID
)
SELECT 
    GROUPING_ID(T2_ID, T3_ID) GRP_ID,
    T2_ID,
    T3_ID,
    -- 将VAL按重复次数拆分后求和，恢复正确数值
    SUM(VAL / repeat_count) AS TOTAL
FROM base_data
GROUP BY 
    GROUPING SETS (
        (),          -- 总计
        T2_ID,       -- 按T2_ID分组
        T3_ID,       -- 按T3_ID分组
        (T2_ID, T3_ID) -- 按T2_ID+T3_ID分组
    )
ORDER BY GRP_ID, T2_ID, T3_ID;

验证结果 #

执行上述SQL后，将得到与UNION ALL完全一致的正确结果：

GRP_ID  T2_ID   T3_ID   TOTAL
0       AA      CCC     6
0       AA      DDD     6
0       BB      CCC     24
0       BB      DDD     24
1       AA      NULL    6
1       BB      NULL    24
2       NULL    CCC     30
2       NULL    DDD     30
3       NULL    NULL    30

通用扩展 #

如果存在更多关联表，只需调整repeat_count的计算逻辑：统计每个T1_ID在所有关联表组合后的总重复行数（或计算各关联表对应T1_ID的记录数乘积），再用VAL除以该数值即可。

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