BigQuery多表多键FULL JOIN不丢失数据的实现方案

问题原因 #

你当前的查询之所以丢失德国、波兰这类仅在表2/表3存在的国家数据，是因为：

• FULL JOIN table2 后，仅存在于表2的记录（如德国）会导致 t1.Date 和 t1.Country 为 NULL
• 后续 FULL JOIN table3 时，你用 t1.Date = t3.Date AND t1.Country = t3.Country 作为关联条件，NULL 值无法匹配
• 最终 SELECT 和 GROUP BY 都依赖 t1.Date 和 t1.Country，导致这些仅存在于其他表的记录无法正常显示

方法一：基于全量维度的左连接 #

先从三个表中提取所有唯一的 Date 和 Country 组合，再以此为基础左连接三个表，确保所有维度都被保留：

WITH all_dimensions AS (
  SELECT Date, Country FROM `table1`
  UNION DISTINCT
  SELECT Date, Country FROM `table2`
  UNION DISTINCT
  SELECT Date, Country FROM `table3`
)
SELECT
  ad.Date,
  ad.Country,
  IFNULL(SUM(t1.CostsCampaignA), 0) AS `Costs Campaign A`,
  IFNULL(SUM(t2.CostsCampaignB), 0) AS `Costs Campaign B`,
  IFNULL(SUM(t3.CostsCampaignC), 0) AS `Costs Campaign C`
FROM all_dimensions ad
LEFT JOIN `table1` t1 ON ad.Date = t1.Date AND ad.Country = t1.Country
LEFT JOIN `table2` t2 ON ad.Date = t2.Date AND ad.Country = t2.Country
LEFT JOIN `table3` t3 ON ad.Date = t3.Date AND ad.Country = t3.Country
GROUP BY ad.Date, ad.Country
ORDER BY ad.Date, ad.Country

方法二：UNION ALL + PIVOT（更简洁） #

先将三个表的结构统一，合并为长表，再通过 PIVOT 转成你需要的宽表格式：

WITH combined_data AS (
  SELECT Date, Country, 'Campaign A' AS campaign, `Costs Campaign A` AS cost FROM `table1`
  UNION ALL
  SELECT Date, Country, 'Campaign B' AS campaign, `Costs Campaign B` AS cost FROM `table2`
  UNION ALL
  SELECT Date, Country, 'Campaign C' AS campaign, `Costs Campaign C` AS cost FROM `table3`
)
SELECT
  Date,
  Country,
  IFNULL(`Campaign A`, 0) AS `Costs Campaign A`,
  IFNULL(`Campaign B`, 0) AS `Costs Campaign B`,
  IFNULL(`Campaign C`, 0) AS `Costs Campaign C`
FROM combined_data
PIVOT (
  SUM(cost)
  FOR campaign IN ('Campaign A', 'Campaign B', 'Campaign C')
)
ORDER BY Date, Country

两种方法都能完整保留所有国家和日期的组合，且将无数据的字段填充为0，符合你的期望输出。

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