BigQuery：如何高效实现多条件统计并扁平化结果至新表？

需求背景 #

需要从table_a中按不同筛选条件多次统计COUNT值，最终将结果扁平化（按ID聚合，每个统计值作为单独列）并持久化到另一张表。期望输出格式如下：

ID|Cnt_A|Cnt_B|Cnt_C
1|10|20|40
2|2|12|55
...

table_a的原始数据结构示例：

ID|Field_1|Field_2|Field_3|Field_...
1|True|Something|Else|10|...
1|False|Else|Something|20|...
2|True|Possibly|Positive|30|...
2|False|Might|Negative|40|...

具体统计逻辑：

• Cnt_A：统计Field_1 = True的记录数
• Cnt_B：统计Field_2 != 'Possibly'的记录数
• 以此类推其他统计项

当前已通过创建三个含ID和对应统计值的临时表，再关联查询得到结果，想找更优方案，询问以下JOIN子查询写法是否更优：

SELECT t1.Cnt_A, t2.Cnt_B
FROM (SELECT ID, COUNT(Field_A) as Cnt_A 
      FROM table_a
      GROUP BY ID 
      HAVING Field_1 = True) t1
JOIN (SELECT ID, COUNT(Field_B) as Cnt_B 
      FROM table_a
      GROUP BY ID 
      HAVING Field_2 != 'Possibly') t2
ON t1.ID=t2.ID

方案评价与优化建议 #

关于你提到的JOIN子查询方案 #

这个方案比临时表写法简洁，但不是最优：

• 每个子查询都会单独扫描一次table_a，统计项越多，重复扫描次数越多，数据量大时性能损耗明显。
• 用HAVING筛选条件是错误的：HAVING用于过滤聚合后的结果，而你需要的是对原始行的筛选，应该把条件放到WHERE子句中，否则统计结果会出错。比如第一个子查询应改为WHERE Field_1 = True再执行GROUP BY ID。

最优方案：单表扫描+条件聚合 #

只需扫描一次table_a，用CASE WHEN结合聚合函数实现多条件统计，性能远高于多次扫描的方案。示例SQL如下：

SELECT 
    ID,
    COUNT(CASE WHEN Field_1 = True THEN 1 END) AS Cnt_A,
    COUNT(CASE WHEN Field_2 != 'Possibly' THEN 1 END) AS Cnt_B,
    -- 其他统计项按此格式扩展
    COUNT(CASE WHEN Field_3 = 'Something' THEN 1 END) AS Cnt_C
FROM table_a
GROUP BY ID

如果要直接持久化到目标表，用INSERT INTO包裹即可：

INSERT INTO target_table (ID, Cnt_A, Cnt_B, Cnt_C)
SELECT 
    ID,
    COUNT(CASE WHEN Field_1 = True THEN 1 END) AS Cnt_A,
    COUNT(CASE WHEN Field_2 != 'Possibly' THEN 1 END) AS Cnt_B,
    COUNT(CASE WHEN Field_3 = 'Something' THEN 1 END) AS Cnt_C
FROM table_a
GROUP BY ID

说明：

• CASE WHEN会逐行判断是否满足条件，满足则返回1，否则返回NULL
• COUNT()会忽略NULL值，最终统计的就是满足条件的行数
• 也可以用SUM(CASE WHEN ... THEN 1 ELSE 0 END)，效果与COUNT一致，可根据习惯选择

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