基于BigQuery分组集的多层级住房类型聚合问题排查与优化

问题排查 #

原查询存在以下问题导致聚合结果异常：

• 语法错误：使用了中文单引号（’），BigQuery无法解析，直接引发语法报错。
• 层级逻辑错位：用户要求的聚合层级为product（父）→ sub_product（子）→ industry_category（父）→ industry_sub_category（子），但原查询的分组集合将product_group（对应sub_product）置于product之前，且分组递减顺序不符合层级逻辑，导致聚合维度混乱。
• 维度匹配偏差：分组集合的层级递减未对应业务逻辑，使得GROUPING函数返回的聚合状态与预期的层级汇总不匹配。

优化后的查询 #

WITH test_table AS (
  SELECT
    -- 用DATE_TRUNC简化月份提取，避免边界判断错误
    FORMAT_DATE('%b', DATE_TRUNC(date_column, MONTH)) AS target_month,
    -- 按层级判断聚合状态，替换为'all'表示该层级汇总
    IF(GROUPING(product) = 1, 'all', product) AS product,
    IF(GROUPING(product_group) = 1, 'all', product_group) AS sub_product,
    IF(GROUPING(category) = 1, 'all', category) AS industry_category,
    IF(GROUPING(sub_category) = 1, 'all', sub_category) AS industry_sub_category,
    'destination' AS dimension_type,
    destination AS dimension_value,
    'housing_type' AS related_type,
    type AS related_value,
    SUM(type_count) AS counter
  FROM
    table_a
  WHERE
    -- 过滤目标月份，减少无效NA值输出
    date_column BETWEEN '2025-01-01' AND '2025-02-28'
  GROUP BY
    GROUPING SETS(
      -- 1. 最细粒度：全层级+目的地+住房类型
      (target_month, product, product_group, category, sub_category, destination, type),
      -- 2. 去掉子类别：类别层级汇总
      (target_month, product, product_group, category, destination, type),
      -- 3. 去掉类别+子类别：产品子层级汇总
      (target_month, product, product_group, destination, type),
      -- 4. 去掉产品子级+类别+子类别：产品层级汇总
      (target_month, product, destination, type),
      -- 5. 最粗粒度：仅月份+目的地+住房类型
      (target_month, destination, type)
    )
)
SELECT * FROM test_table;

优化说明 #

1. 日期处理优化：使用DATE_TRUNC+FORMAT_DATE提取月份，比原BETWEEN判断更简洁，避免手动边界计算错误。
2. 层级逻辑修正：分组集合严格按照用户要求的product→sub_product→category→sub_category层级递减，确保每一层聚合对应正确业务维度。
3. 语法修正：替换所有中文单引号为英文单引号，解决语法报错问题。
4. 过滤条件补充：新增WHERE子句过滤目标月份，减少不必要的NA值输出。
5. 字段命名清晰：明确标注sub_product对应原product_group，避免层级混淆。

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