如何在BigQuery中合并两个查询以正确获取活动内容量与参与率

问题原因 #

你直接关联多表后统一聚合的写法会触发笛卡尔积重复计数问题：多个关联表的一对多关系会让同一条内容记录被重复展开多次，最终sum统计的TotalContent数值远高于真实值。

正确实现方案 #

将两个已经验证过逻辑的独立查询作为公共表表达式（CTE）预先完成聚合，再通过campaign_id关联获取最终结果，不会出现统计偏差：

WITH content_stats AS (
  -- 原查询1：统计单活动内容条数，逻辑完全复用
  Select 
    c.campaign_id, 
    sum(case when medium in ('photo', 'story', 'video', 'album') then 1 else 0 end) as totalcontent
  From `public_collaboration_contents` as cc 
  Left join `public_collaborations` as c ON cc.collaboration_id = c.id
  Left join `public_influencers` as i on c.influencer_id = i.id
  Left join `public_collaboration_tasks` as ct on cc.id = ct.collaboration_content_id
  Where cc.state = 'delivered'
    And ct.state = 'delivered'
  Group by c.campaign_id
),
er_stats AS (
  -- 原查询2：统计活动名称与参与率，逻辑完全复用
  select 
    c.campaign_id, 
    brand_name,
    pc.title, 
    (sum(psip.likes + psip.comments + psip.video_views)/sum(psip.influencer_starting_followers)) as ER 
  FROM `production.public_campaigns` as pc
  LEFT JOIN `public_sponsored_instagram_posts` as psip
    ON pc.id = psip.campaign_id
  LEFT JOIN `public_instagram_post_performances` pipp
    ON pipp.id=pc.id
  LEFT JOIN `public_collaboration_contents`pcc 
    on pcc.id=pc.id
  LEFT JOIN `public_collaborations` as c 
    ON c.id=pcc.collaboration_id
  GROUP BY brand_name, title, campaign_id, pc.id
  having (sum(psip.likes + psip.comments + psip.video_views)/sum(psip.influencer_starting_followers)) > 0
)
-- 关联两个预聚合结果，得到最终需要的字段
SELECT 
  er.brand_name,
  er.title,
  er.ER,
  cs.totalcontent as TotalContent
FROM er_stats er
LEFT JOIN content_stats cs 
  ON er.campaign_id = cs.campaign_id

说明 #

• 如果不需要保留无内容统计的活动，把LEFT JOIN改为INNER JOIN即可
• 两个子查询完全复用你已验证的逻辑，统计结果和单独跑两个查询的输出完全一致

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