BigQuery关联clients与stats表后结果行数超出预期求助

核心原因 #

关联后结果行数远超预期，本质是关联键ExternalCustomerId在单表或两表中存在重复值，触发了1对多/多对多的笛卡尔积匹配，而非你预期的1对1关联。你更换JOIN类型后行数不变，是因为stats表的所有关联键都能在clients表中匹配到对应值，此时INNER JOIN、LEFT JOIN（stats为左表）、RIGHT JOIN（clients为左表）的返回行数无差异。

第一步：定位重复的关联键 #

依次执行以下两个查询，确认重复值出现在哪张表：

• 检查clients表的重复ExternalCustomerId：

SELECT ExternalCustomerId, COUNT(*) as cnt
FROM `298114322003.google_ads1.p_Customer_2670156874`
GROUP BY ExternalCustomerId
HAVING cnt > 1

• 检查stats表的重复ExternalCustomerId：

SELECT ExternalCustomerId, COUNT(*) as cnt
FROM `298114322003.google_ads1.p_AccountStats_2670156874`
GROUP BY ExternalCustomerId
HAVING cnt > 1

第二步：根据场景修复问题 #

场景1：clients表存在重复ID（最常见） #

如果是脏数据导致的重复，先对clients表按ID去重后再关联即可，示例如下（可根据实际业务规则调整去重逻辑）：

SELECT 
  clients.AccountDescriptiveName AS client_name,
  stats.ExternalCustomerId AS client_id,
  AverageCost,
  AverageCpc,
  AverageCpm,
  AveragePosition
FROM (
  SELECT 
    ExternalCustomerId, 
    AccountDescriptiveName,
    -- 按分区时间倒序取最新的一条客户名称记录
    ROW_NUMBER() OVER(PARTITION BY ExternalCustomerId ORDER BY _PARTITIONTIME DESC) as rn
  FROM `298114322003.google_ads1.p_Customer_2670156874`
) AS clients
JOIN `298114322003.google_ads1.p_AccountStats_2670156874` AS stats
ON clients.ExternalCustomerId = stats.ExternalCustomerId
WHERE clients.rn = 1

场景2：需要按客户ID聚合统计结果 #

如果你的预期是每个客户仅返回一行汇总指标，先对stats表按ID聚合后再关联即可：

SELECT 
  clients.AccountDescriptiveName AS client_name,
  stats.ExternalCustomerId AS client_id,
  SUM(AverageCost) as total_cost,
  AVG(AverageCpc) as avg_cpc,
  AVG(AverageCpm) as avg_cpm,
  AVG(AveragePosition) as avg_position
FROM `298114322003.google_ads1.p_Customer_2670156874` AS clients
JOIN (
  SELECT 
    ExternalCustomerId,
    AverageCost,
    AverageCpc,
    AverageCpm,
    AveragePosition
  FROM `298114322003.google_ads1.p_AccountStats_2670156874`
  GROUP BY ExternalCustomerId
) AS stats
ON clients.ExternalCustomerId = stats.ExternalCustomerId

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