BigQuery中按月份分组统计多表行数的SELECT语句实现方法

问题根源 #

你原SQL卡顿的核心原因是多表直接关联时，同月份的多条记录会生成笛卡尔积，数据量指数级膨胀，不仅查询效率极低，统计得到的COUNT结果也不符合预期。

最优解决方案 #

先单独统计每张表每个月的行数，再按月份关联，避免笛卡尔积，性能提升非常明显，同时保证结果准确：

WITH t1 AS (
    SELECT month, COUNT(one) AS one FROM dataset1 GROUP BY month
),
t2 AS (
    SELECT month, COUNT(two) AS two FROM dataset2 GROUP BY month
),
t3 AS (
    SELECT month, COUNT(three) AS three FROM dataset3 GROUP BY month
),
t4 AS (
    SELECT month, COUNT(four) AS four FROM dataset4 GROUP BY month
),
t5 AS (
    SELECT month, COUNT(five) AS five FROM dataset5 GROUP BY month
)
SELECT 
    COALESCE(t1.month, t2.month, t3.month, t4.month, t5.month) AS month,
    COALESCE(t1.one, 0) AS one,
    COALESCE(t2.two, 0) AS two,
    COALESCE(t3.three, 0) AS three,
    COALESCE(t4.four, 0) AS four,
    COALESCE(t5.five, 0) AS five
FROM t1
FULL OUTER JOIN t2 ON t1.month = t2.month
FULL OUTER JOIN t3 ON COALESCE(t1.month, t2.month) = t3.month
FULL OUTER JOIN t4 ON COALESCE(t1.month, t2.month, t3.month) = t4.month
FULL OUTER JOIN t5 ON COALESCE(t1.month, t2.month, t3.month, t4.month) = t5.month
ORDER BY month DESC;

• 用CTE单独统计每张表每月的行数，每个CTE中同一个月份仅会返回1条记录，关联时不会产生冗余数据
• 用FULL OUTER JOIN避免遗漏只在部分表中存在的月份
• 用COALESCE函数把空值转为0，完全匹配你给出的输出样例要求

兼容低版本数据库的写法 #

如果你的数据库不支持FULL OUTER JOIN，可以用UNION ALL的写法，兼容性更高，性能同样远优于原写法：

SELECT 
    month,
    SUM(one) AS one,
    SUM(two) AS two,
    SUM(three) AS three,
    SUM(four) AS four,
    SUM(five) AS five
FROM (
    SELECT month, COUNT(one) AS one, 0 AS two, 0 AS three, 0 AS four, 0 AS five FROM dataset1 GROUP BY month
    UNION ALL
    SELECT month, 0 AS one, COUNT(two) AS two, 0 AS three, 0 AS four, 0 AS five FROM dataset2 GROUP BY month
    UNION ALL
    SELECT month, 0 AS one, 0 AS two, COUNT(three) AS three, 0 AS four, 0 AS five FROM dataset3 GROUP BY month
    UNION ALL
    SELECT month, 0 AS one, 0 AS two, 0 AS three, COUNT(four) AS four, 0 AS five FROM dataset4 GROUP BY month
    UNION ALL
    SELECT month, 0 AS one, 0 AS two, 0 AS three, 0 AS four, COUNT(five) AS five FROM dataset5 GROUP BY month
) t
GROUP BY month
ORDER BY month DESC;

内容的提问来源于stack exchange，提问作者Levi H.