BigQuery左连接疑似执行交叉连接问题排查求助

我之前在处理类似的广告与业务数据关联统计时，也踩过直接左连接的坑——核心问题是PPC广告数据和咨询数据是一对多的关系，直接左连接会导致PPC的每条记录被重复匹配多次咨询记录，进而让统计的支出、行数出现偏差（比如支出被重复计算，总行数虚高）。

你的原查询的问题点 #

你当前的左连接写法会让PPC表的单条记录对应多条咨询记录，这就导致：

• COUNT(1)统计的是连接后的总行数（包含重复的PPC行），不是实际的业务行数
• 如果你后续要统计PPC支出（比如SUM(ppc.spend)），会因为重复行导致支出被多算
• 虽然COUNT(DISTINCT ppc.ID)能得到正确的PPC行数，但这只是治标不治本，聚合效率也会更低

最优解决方案：先分别聚合，再关联 #

正确的做法是先对两张表按日期单独做聚合统计，再将聚合后的结果按日期关联。这样既避免了重复行问题，又能保证统计数据的准确性，还提升了查询效率。

示例SQL如下（请根据你的实际表名、字段名调整）：

#standardSQL
-- 先聚合PPC表的每日数据：行数、支出
WITH ppc_daily AS (
  SELECT
    CAST(Date AS DATE) AS date,
    COUNT(DISTINCT ID) AS ppc_rows,
    SUM(spend) AS ppc_spend  -- 替换成你实际的PPC支出字段名
  FROM `your-project.your-ppc-table`  -- 替换成你的PPC表路径
  GROUP BY date
),
-- 再聚合咨询表的每日数据：行数、营收
enquiries_daily AS (
  SELECT
    CAST(enquiry_date AS DATE) AS date,  -- 替换成咨询表的日期字段名
    COUNT(DISTINCT EnquiryId) AS enquiry_rows,
    SUM(revenue) AS enquiry_revenue  -- 替换成你实际的咨询营收字段名
  FROM `your-project.your-enquiries-table`  -- 替换成你的咨询表路径
  GROUP BY date
)
-- 最后关联两个聚合结果，确保所有日期都被覆盖
SELECT
  COALESCE(p.date, e.date) AS date,
  COALESCE(p.ppc_rows, 0) AS ppc_rows,
  COALESCE(p.ppc_spend, 0) AS ppc_spend,
  COALESCE(e.enquiry_rows, 0) AS enquiry_rows,
  COALESCE(e.enquiry_revenue, 0) AS enquiry_revenue
FROM ppc_daily p
FULL OUTER JOIN enquiries_daily e
ON p.date = e.date
ORDER BY date DESC;

为什么这种写法更好？ #

1. 避免重复计算：先聚合后连接，每张表的日期维度数据都是唯一的，不会出现一对多导致的重复行
2. 效率更高：提前聚合减少了连接时的数据量，BigQuery的执行计划会更高效
3. 数据准确：统计的支出、营收、行数都是真实的每日汇总值，不会出现虚高或重复

如果你的业务逻辑需要更细粒度的关联（比如按广告ID关联咨询），那可以先在子查询中按广告ID+日期聚合，再关联，但核心思路还是先聚合，再连接，而不是先连接再聚合。

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