BigQuery分组聚合问题：保留全量数据并统计mobile_app_install指标

修正后的查询可同时满足保留所有campaign数据、汇总全量指标、精准统计mobile_app_install总和的需求，每个campaign对应一行结果：

WITH base_metrics AS (
  SELECT 
    campaign_id,
    campaign_name,
    start_time,
    end_time,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    SUM(reach) AS reach,
    SUM(spend) AS cost,
    AVG(cpc) AS cpc
  FROM dataexploration-193817.marketing_data.facebook_ads_data
  WHERE start_time >= '{date_start}' AND start_time <= '{date_end}'
  GROUP BY campaign_id, campaign_name, start_time, end_time
),
app_install_stats AS (
  SELECT 
    campaign_id,
    SUM(PARSE_NUMERIC(a.value)) AS mobile_app_install
  FROM dataexploration-193817.marketing_data.facebook_ads_data
  LEFT JOIN UNNEST(actions) AS a
  WHERE a.action_type = 'mobile_app_install' 
    AND start_time >= '{date_start}' AND start_time <= '{date_end}'
  GROUP BY campaign_id
)
SELECT 
  b.*,
  COALESCE(ai.mobile_app_install, 0) AS mobile_app_install
FROM base_metrics b
LEFT JOIN app_install_stats ai ON b.campaign_id = ai.campaign_id;

核心调整说明： #

• 拆分统计逻辑：将基础指标汇总与app安装统计拆分为两个子查询，避免因UNNEST(actions)拆分数组导致的基础指标重复计算问题。
• 保留全量campaign：基础指标子查询直接从原表聚合，不过滤action类型，确保所有符合时间范围的campaign都被纳入结果。
• 兼容无安装数据的campaign：通过LEFT JOIN关联安装统计结果，用COALESCE将无安装数据的campaign的安装数设为0，保证结果行的完整性。
• 精准统计安装数：在安装统计子查询中仅筛选action_type = 'mobile_app_install'的数据，确保统计结果准确。

如果需要每个campaign_id唯一一行（忽略同一campaign的不同时间分段），可修改基础指标子查询的时间字段聚合方式：

WITH base_metrics AS (
  SELECT 
    campaign_id,
    campaign_name,
    MIN(start_time) AS start_time,
    MAX(end_time) AS end_time,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    SUM(reach) AS reach,
    SUM(spend) AS cost,
    AVG(cpc) AS cpc
  FROM dataexploration-193817.marketing_data.facebook_ads_data
  WHERE start_time >= '{date_start}' AND start_time <= '{date_end}'
  GROUP BY campaign_id, campaign_name
),
app_install_stats AS (
  SELECT 
    campaign_id,
    SUM(PARSE_NUMERIC(a.value)) AS mobile_app_install
  FROM dataexploration-193817.marketing_data.facebook_ads_data
  LEFT JOIN UNNEST(actions) AS a
  WHERE a.action_type = 'mobile_app_install' 
    AND start_time >= '{date_start}' AND start_time <= '{date_end}'
  GROUP BY campaign_id
)
SELECT 
  b.*,
  COALESCE(ai.mobile_app_install, 0) AS mobile_app_install
FROM base_metrics b
LEFT JOIN app_install_stats ai ON b.campaign_id = ai.campaign_id;

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