BigQuery中基于ROLLUP汇总行关联两张表的问题

问题场景 #

创建两个带ROLLUP的临时表，分别按year_month汇总支付金额和储蓄金额，但ROLLUP生成的汇总行（year_month为NULL）无法正确关联，尝试用IFNULL(year_month, 'Total')或COALESCE(year_month, 'Total')替换NULL后结果不符合预期，需要实现汇总行正确关联并展示储蓄金额及占比。

原查询语句：

WITH PAYMENT
(SELECT
year_month
,SUM(AMOUNT) PAYMENT_AMOUNT
FROM PAYMENTS
GROUP BY ROLLUP (year_month))

,SAVING
(SELECT
year_month
,SUM(AMOUNT) SAVING_AMOUNT
FROM SAVING
GROUP BY ROLLUP (year_month))

SELECT payment.year_month, PAYMENT_AMOUNT, SAVING_AMOUNT,SAVING_AMOUNT/PAYMENT_AMOUNT PERC_SAVED FROM PAYMENT
LEFT JOIN SAVINGS ON PAYMENT.YEAR_MONTH=SAVINGS.YEAR_MONTH

问题分析 #

1. 临时表SAVING与原表名SAVING重名，导致逻辑冲突；JOIN时使用的SAVINGS并非定义的临时表，属于笔误。
2. 直接在子查询外处理NULL无法保证两边汇总行的匹配，需在子查询内统一处理ROLLUP生成的NULL值，确保关联条件一致。

修正后的查询语句 #

方案一：统一替换NULL为标识值 #

WITH PAYMENT_SUMMARY AS (
    SELECT
        COALESCE(year_month, 'Total') AS year_month,
        SUM(AMOUNT) AS PAYMENT_AMOUNT
    FROM PAYMENTS
    GROUP BY ROLLUP (year_month)
),
SAVING_SUMMARY AS (
    SELECT
        COALESCE(year_month, 'Total') AS year_month,
        SUM(AMOUNT) AS SAVING_AMOUNT
    FROM SAVING
    GROUP BY ROLLUP (year_month)
)
SELECT
    ps.year_month,
    ps.PAYMENT_AMOUNT,
    ss.SAVING_AMOUNT,
    CASE WHEN ps.PAYMENT_AMOUNT = 0 THEN NULL ELSE ss.SAVING_AMOUNT / ps.PAYMENT_AMOUNT END AS PERC_SAVED
FROM PAYMENT_SUMMARY ps
LEFT JOIN SAVING_SUMMARY ss ON ps.year_month = ss.year_month;

关键说明

• 重命名临时表为PAYMENT_SUMMARY和SAVING_SUMMARY，避免与原表名冲突，提升可读性。
• 在两个子查询中均用COALESCE(year_month, 'Total')将ROLLUP生成的NULL替换为统一标识Total，确保汇总行能通过该字段正确关联。
• 添加CASE语句处理支付金额为0的情况，避免出现除以0的错误。

方案二：保留原始NULL值，通过关联条件匹配 #

WITH PAYMENT_SUMMARY AS (
    SELECT
        year_month,
        SUM(AMOUNT) AS PAYMENT_AMOUNT
    FROM PAYMENTS
    GROUP BY ROLLUP (year_month)
),
SAVING_SUMMARY AS (
    SELECT
        year_month,
        SUM(AMOUNT) AS SAVING_AMOUNT
    FROM SAVING
    GROUP BY ROLLUP (year_month)
)
SELECT
    ps.year_month,
    ps.PAYMENT_AMOUNT,
    ss.SAVING_AMOUNT,
    CASE WHEN ps.PAYMENT_AMOUNT = 0 THEN NULL ELSE ss.SAVING_AMOUNT / ps.PAYMENT_AMOUNT END AS PERC_SAVED
FROM PAYMENT_SUMMARY ps
LEFT JOIN SAVING_SUMMARY ss 
    ON (ps.year_month = ss.year_month) 
    OR (ps.year_month IS NULL AND ss.year_month IS NULL);

关键说明

• 保留year_month的原始NULL值，通过关联条件明确匹配汇总行的NULL情况，无需修改字段值即可实现正确关联。
• 同样添加CASE语句避免除以0的错误。

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