如何加速GCP BigQuery中SOH库存表基于门店商品组合的关联查询

你当前两种写法性能不好的核心原因：

• 原始写法：Site IN (子查询)和Article IN (子查询)是独立过滤条件，会生成所有符合条件的Site和Article的笛卡尔积组合再过滤，即使你实际只有少量有效售卖组合，也会扫描大量无意义的门店+商品交叉行
• 拼接ID过滤写法：WHERE子句中对库存表字段使用CONCAT函数，会导致BigQuery无法下推过滤条件，也无法利用Site、Article字段的聚类优化，需要为每一行计算拼接值后再匹配，额外开销很高，性能反而更差

1. 优先使用元组匹配过滤（无额外计算，可利用存储优化） #

BigQuery原生支持多字段元组的IN匹配，不需要拼接字符串，直接基于门店+商品组合过滤，完全避免交叉冗余扫描：

Q3_SOH AS (
  SELECT
    CONCAT(Article,"|",Site,"|",Calendar_Day) AS ID, 
    Site, 
    Article, 
    SOH_Cost, 
    SOH_Qty_EA, 
    Calendar_Day
  FROM
    `gcp-table`
  WHERE
    Calendar_Day BETWEEN DATE_ADD(startDate, INTERVAL -1 day) AND endDate
    -- 直接匹配Q1中的有效门店+商品组合，无笛卡尔积
    AND (Site, Article) IN (SELECT DISTINCT Site, Article FROM Q1)
    -- 可选：过滤无库存的无效行，大幅减少扫描量（你提到大部分库存为0）
    AND SOH_Qty_EA > 0
)

2. 预生成唯一组合降低重复计算 #

如果Q1逻辑复杂、数据量大，可以提前在WITH子句中生成唯一的门店+商品组合，避免多次重复计算DISTINCT：

WITH Q1 AS (
  -- 原有Q1销售逻辑
),
-- 新增：预生成唯一售卖组合，仅计算一次
Q1_VALID_COMBOS AS (
  SELECT DISTINCT Site, Article FROM Q1
),
Q3_SOH AS (
  SELECT
    CONCAT(s.Article,"|",s.Site,"|",s.Calendar_Day) AS ID, 
    s.Site, 
    s.Article, 
    s.SOH_Cost, 
    s.SOH_Qty_EA, 
    s.Calendar_Day
  FROM `gcp-table` s
  -- 用JOIN替代IN，执行计划更稳定，适合组合量较大的场景
  INNER JOIN Q1_VALID_COMBOS c
    ON s.Site = c.Site AND s.Article = c.Article
  WHERE
    s.Calendar_Day BETWEEN DATE_ADD(startDate, INTERVAL -1 day) AND endDate
    AND s.SOH_Qty_EA > 0
)

3. 存储层优化（长期生效，性能提升最明显） #

如果库存表是高频查询的表，建议调整表结构：

• 按Calendar_Day设置分区键，你当前已经按日期过滤，分区后会直接剪枝掉所有不在查询范围内的分区，扫描数据量会大幅下降
• 按Site、Article设置聚类键，同一门店+商品的库存数据会存在相邻的存储块中，查询时只会扫描包含有效组合的存储块，扫描量可以降低数倍到数十倍

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