BigQuery中实现多个ROLLUP交叉聚合的替代方案咨询

核心实现代码 #

你可以通过动态生成分组组合的方式替代冗余的UNION ALL写法，代码如下：

WITH sales AS (
    -- 你的原始业务数据定义保持不变
    SELECT 2010 Year, 'Jan' Month, 'Electronics' ProductGroup, 'Phone' Product, 100 Revenue UNION ALL
    SELECT 2010,    'Jan',  'Electronics',  'Laptop',   200 UNION ALL
    SELECT 2010,    'Jan',  'Cars', 'Jeep', 250 UNION ALL
    SELECT 2010,    'Jan',  'Cars', 'Hummer',   105 UNION ALL
    SELECT 2010,    'Feb',  'Electronics',  'Phone',    110 UNION ALL
    SELECT 2010,    'Feb',  'Electronics',  'Laptop',   300 UNION ALL
    SELECT 2010,    'Feb',  'Cars', 'Jeep', 50 UNION ALL
    SELECT 2010,    'Feb',  'Cars', 'Hummer',   75 UNION ALL
    SELECT 2010,    'Mar',  'Electronics',  'Phone',    80 UNION ALL
    SELECT 2010,    'Mar',  'Electronics',  'Laptop',   200 UNION ALL
    SELECT 2010,    'Mar',  'Cars', 'Jeep', 100 UNION ALL
    SELECT 2010,    'Mar',  'Cars', 'Hummer',   50 UNION ALL
    SELECT 2011,    'Jan',  'Electronics',  'Phone',    200 UNION ALL
    SELECT 2011,    'Jan',  'Electronics',  'Laptop',   300 UNION ALL
    SELECT 2011,    'Jan',  'Cars', 'Jeep', 100 UNION ALL
    SELECT 2011,    'Jan',  'Cars', 'Hummer',   200 UNION ALL
    SELECT 2011,    'Feb',  'Electronics',  'Phone',    300 UNION ALL
    SELECT 2011,    'Feb',  'Electronics',  'Laptop',   900 UNION ALL
    SELECT 2011,    'Feb',  'Cars', 'Jeep', 100 UNION ALL
    SELECT 2011,    'Feb',  'Cars', 'Hummer',   200 UNION ALL
    SELECT 2011,    'Mar',  'Electronics',  'Phone',    400 UNION ALL
    SELECT 2011,    'Mar',  'Electronics',  'Laptop',   350 UNION ALL
    SELECT 2011,    'Mar',  'Cars', 'Jeep', 240 UNION ALL
    SELECT 2011,    'Mar',  'Cars', 'Hummer',   130
),
-- 定义产品维度的ROLLUP分组级别
product_groupings AS (
  SELECT * FROM UNNEST([
    STRUCT([] AS product_fields),
    (['ProductGroup']),
    (['ProductGroup', 'Product'])
  ])
),
-- 定义时间维度的ROLLUP分组级别
time_groupings AS (
  SELECT * FROM UNNEST([
    STRUCT([] AS time_fields),
    (['Year']),
    (['Year', 'Month'])
  ])
)
SELECT
  IF('ProductGroup' IN UNNEST(all_fields), ProductGroup, NULL) AS ProductGroup,
  IF('Product' IN UNNEST(all_fields), Product, NULL) AS Product,
  IF('Year' IN UNNEST(all_fields), Year, NULL) AS Year,
  IF('Month' IN UNNEST(all_fields), Month, NULL) AS Month,
  AVG(Revenue) AS avg_revenue
FROM sales
-- 笛卡尔积生成所有分组组合
CROSS JOIN product_groupings
CROSS JOIN time_groupings
CROSS JOIN UNNEST([ARRAY_CONCAT(product_fields, time_fields)]) AS all_fields
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4

实现逻辑说明 #

• 首先将两组ROLLUP对应的分组层级分别定义为数组列表，每一项对应ROLLUP的一个分组级别
• 通过CROSS JOIN对两组分组层级做笛卡尔积，刚好得到你需要的所有分组组合
• 按照最终的分组维度列表判断每个字段是否需要参与分组，不需要参与的返回NULL，最后按返回的四个维度值分组聚合即可

方案优势 #

• 代码量不会随维度数量指数级增长，新增维度只需要修改对应的分组配置数组即可
• 原始业务逻辑只需要写一次，不需要重复编写多段UNION ALL子句
• 扩展性强，支持自定义任意分组组合，也可以实现CUBE、自定义GROUPING SETS的效果

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