使用GROUP BY ROLLUP实现BigQuery查询获取Excel数据透视表全量结果

问题原因 #

你现有两个ROLLUP拼接的查询覆盖的维度分组不全，缺少(ProductGroup, Year)和(ProductGroup, Year, Quarter)两类分组结果，正好对应缺失的2条记录。

解决方案 #

不需要写冗长的UNION语句，直接使用BigQuery支持的GROUPING SETS语法，明确指定所有需要的维度分组即可，查询仅需扫描一次表，性能更高也不会漏项，正确写法如下：

SELECT 
  Year, 
  Quarter, 
  ProductGroup, 
  Product, 
  SUM(Revenue) AS SumOfRevenue, 
  SUM(Units) AS SumOfUnits 
FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year = 2020 AND Quarter = 'Q1'
GROUP BY GROUPING SETS (
  (), -- 全局总计
  (ProductGroup), -- 按产品组汇总
  (ProductGroup, Product), -- 产品组+产品汇总
  (ProductGroup, Product, Year), -- 产品组+产品+年份汇总
  (ProductGroup, Product, Year, Quarter), -- 最细维度聚合
  (ProductGroup, Year), -- 产品组+年份汇总
  (ProductGroup, Year, Quarter), -- 产品组+年份+季度汇总
  (Year), -- 按年份汇总
  (Year, Quarter) -- 年份+季度汇总
)

该查询返回结果与你提供的长格式UNION写法完全一致，可得到所需的36条全量维度组合数据。

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