如何在Google BigQuery中关联多表并均分指标至输出行？

问题分析 #

你的原查询在多品牌匹配时，会为每个匹配品牌生成一行，但直接SUM(clicks)会重复累加原始值，且缺少每个Campaign的匹配品牌计数，导致无法实现均分需求。要解决这个问题，核心是先计算每个Campaign对应的匹配品牌数量，再用总Clicks除以该数量得到均分后的值。

修正后的查询语句 #

WITH brands AS (
  SELECT 
    brand,
    CONCAT('%', searchterm, '%') AS searchterm
  FROM `Table A` -- BigQuery中表名带空格需用反引号包裹
),

labels AS (
  SELECT 
    label,
    CONCAT('%', searchterm, '%') AS searchterm
  FROM `Table B`
),

-- 先关联品牌，计算每个Campaign的匹配品牌数
campaign_brand_matches AS (
  SELECT 
    c.date,
    c.campaign_name,
    c.clicks,
    COALESCE(b.brand, 'generic') AS brand,
    -- 窗口函数统计当前Campaign匹配到的品牌总数
    COUNT(b.brand) OVER(PARTITION BY c.date, c.campaign_name) AS match_count
  FROM `Table C` c
  LEFT JOIN brands b
    ON LOWER(c.campaign_name) LIKE LOWER(b.searchterm) -- 统一小写避免大小写问题
),

-- 关联标签信息
final AS (
  SELECT 
    cbm.date,
    cbm.campaign_name,
    cbm.brand,
    -- 处理标签匹配，无匹配时显示'no label'
    COALESCE(l.label, 'no label') AS label,
    -- 均分clicks：无匹配品牌时用原始值，否则除以匹配数
    CASE 
      WHEN cbm.match_count = 0 THEN cbm.clicks 
      ELSE cbm.clicks / cbm.match_count 
    END AS clicks
  FROM campaign_brand_matches cbm
  LEFT JOIN labels l
    ON LOWER(cbm.campaign_name) LIKE LOWER(l.searchterm)
  -- 去重：如果一个Campaign匹配多个标签，每个品牌会对应多个标签行，按需调整
  GROUP BY cbm.date, cbm.campaign_name, cbm.brand, cbm.clicks, cbm.match_count, l.label
)

SELECT * FROM final

关键说明 #

1. 匹配品牌计数：使用COUNT(b.brand) OVER(PARTITION BY c.date, c.campaign_name)窗口函数，统计每个Campaign下匹配到的品牌数量，作为均分的除数。
2. 均分计算：通过CASE语句处理无匹配品牌的场景（此时match_count为0，直接使用原始clicks值），否则用总clicks除以匹配数得到均分结果。
3. 大小写兼容：关联时统一转换为LOWER，避免因大小写差异导致匹配失败。
4. 表名格式：BigQuery中如果表名包含空格或特殊字符，需要用反引号`包裹，比如`Table A`。

预期输出 #

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