BigQuery聚合列减少时查询处理量异常增大求助

核心原因 #

出现这种反常情况，本质是BigQuery的查询优化器在两种场景下选择了完全不同的执行计划：

• 当包含SUM(qty_sold)时，优化器识别到需要处理**商品级（item）**的聚合字段，会自动先对同订单下的多条item记录做预聚合（合并成单条订单记录），再基于订单级数据做后续分组统计，实际扫描的有效数据量是订单级的，所以仅为30GB。
• 当移除SUM(qty_sold)后，优化器认为只需要订单ID去重统计，可能跳过了预聚合步骤，直接扫描所有UNNEST(items)膨胀后的行（每个订单对应N条item行，N为订单内商品数量），导致扫描量暴增到500GB。

具体优化建议 #

1. 显式预聚合订单级数据
   手动在CTE阶段完成订单内的聚合，强制优化器按高效路径执行：

   WITH order_level AS (
     SELECT
       FORMAT_DATE('%Y%m', order_placed_localtime_at) AS Month,
       entity_id,
       country_code,
       order_id,
       SUM(items.qty_sold) AS total_qty_sold
     FROM `my_db.orders`
     LEFT JOIN UNNEST(items) AS items
     WHERE DATE(ordered_at) >= '2022-01-01'
     GROUP BY 1,2,3,4
   ),
   final_agg AS (
     SELECT
       Month,
       entity_id,
       country_code,
       COUNT(DISTINCT order_id) AS totalOrders,
       SUM(total_qty_sold) AS qty_sold -- 按需保留
     FROM order_level
     GROUP BY 1,2,3
   )
   SELECT * FROM final_agg
   

   这种写法先把膨胀后的item行合并回订单级，后续无论是否保留SUM统计，处理量都会维持在低水平。

2. 检查并利用表的分区/簇设置
   如果my_db.orders是按ordered_at分区、按entity_id, country_code, order_id簇化的表，可确保优化器优先利用簇化排序快速聚合同订单数据，避免全量扫描膨胀行。

3. 精简CTE字段
   原CTE中包含了sku、global_product_id、total_price等后续查询未用到的字段，这些字段会额外增加扫描的数据量，建议只保留聚合必需的字段。

4. 对比执行计划
   在BigQuery控制台查看两个查询的执行计划，重点关注“读取”阶段的数据来源和处理步骤，确认预聚合步骤的差异，可精准定位优化器的决策逻辑。

内容的提问来源于stack exchange，提问作者Pak Hang Leung