BigQuery中如何高效计算指定产品各门店周边10km门店每日销售额总和

现有查询性能瓶颈分析 #

• 第一层CROSS JOIN生成全量门店笛卡尔积，复杂度是O(n²)，门店数量越大开销越高，且后续重复关联了两次距离表，属于冗余计算
• 用字符串拼接存储周边门店再拆分的操作额外增加了序列化/反序列化开销
• 最终层使用行级关联子查询计算总和，每一行都要执行一次子查询，数据量大时性能极差
• CTE内不必要的ORDER BY、DISTINCT操作额外增加了计算开销

优化后查询方案 #

WITH nearby_stores AS (
  -- 预计算每个门店的10km范围内周边门店列表，ST_DWithin可自动走空间索引
  SELECT
    t1.store_code AS store_code,
    ARRAY_AGG(t2.store_code) AS nearby_store_codes
  FROM stores t1
  INNER JOIN stores t2
    ON ST_DWithin(
      ST_GEOGPOINT(t1.longitude, t1.latitude),
      ST_GEOGPOINT(t2.longitude, t2.latitude),
      10000 -- 10km阈值，单位为米
    )
  WHERE t1.store_code != t2.store_code -- 排除门店自身
  GROUP BY t1.store_code
),
target_product_sales AS (
  -- 提前过滤目标产品的日销数据，减少后续关联数据量
  SELECT
    order_date,
    store_code,
    sales
  FROM daily_summary
  WHERE product_id = '1234'
)
-- 关联计算周边门店日销总和
SELECT
  ns.store_code,
  tps.order_date,
  SUM(tps.sales) AS sales_at_nearby_stores
FROM nearby_stores ns
CROSS JOIN UNNEST(ns.nearby_store_codes) AS nearby_store_code
INNER JOIN target_product_sales tps
  ON tps.store_code = nearby_store_code
GROUP BY ns.store_code, tps.order_date
-- 可选：补充没有周边门店/当日周边无销售额的记录，返回0
UNION ALL
SELECT
  tps.store_code,
  tps.order_date,
  0 AS sales_at_nearby_stores
FROM target_product_sales tps
LEFT JOIN nearby_stores ns
  ON tps.store_code = ns.store_code
WHERE ns.store_code IS NULL 
  OR ARRAY_LENGTH(ns.nearby_store_codes) = 0
ORDER BY store_code, order_date

核心优化点说明 #

• 用BigQuery原生空间函数ST_DWithin替代手动计算距离后过滤，该函数会自动调用空间索引优化，避免全量笛卡尔积计算，门店数量越多性能提升越明显
• 去掉冗余的字符串拼接、拆分逻辑，直接用数组存储周边门店编码，大幅降低序列化开销
• 用批量JOIN聚合替代行级关联子查询，执行效率提升数倍甚至数十倍
• 提前过滤目标产品的销售数据，减少后续环节参与计算的数据量
• 去掉所有CTE中无意义的排序、去重操作，减少不必要的计算开销

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