如何在BigQuery中基于广告状态生成营销活动状态列（非分组）

需求说明 #

需要为每个营销活动新增campaign_status字段：

• 若该活动下存在至少一个状态为ACTIVE的广告，所有行的campaign_status统一为ACTIVE
• 若活动下无活跃广告，则统一标记为INACTIVE
• 要求不使用GROUP BY分组实现

原始数据表 #

期望输出 #

可行解决方案 #

在BigQuery中，使用窗口函数可以完美满足需求，无需分组：

方法1：利用字符串字典序的MAX窗口函数

由于'ACTIVE'的字典序大于'INACTIVE'，对每个campaign_id取ad_status的最大值，就能直接得到该活动的整体状态：

SELECT
  campaign_id,
  ad_id,
  ad_status,
  MAX(ad_status) OVER (PARTITION BY campaign_id) AS campaign_status
FROM
  `你的项目ID.你的数据集.你的表名`

方法2：用COUNTIF窗口函数明确判断活跃广告存在性

如果觉得字典序的方式不够直观，可以用COUNTIF统计每个活动下活跃广告的数量，再通过CASE逻辑返回状态：

SELECT
  campaign_id,
  ad_id,
  ad_status,
  CASE
    WHEN COUNTIF(ad_status = 'ACTIVE') OVER (PARTITION BY campaign_id) > 0 THEN 'ACTIVE'
    ELSE 'INACTIVE'
  END AS campaign_status
FROM
  `你的项目ID.你的数据集.你的表名`

关于之前的CASE+ANY子查询 #

如果之前尝试的关联子查询写法有问题，比如：

-- 示例写法（可能因语法或性能问题失败）
SELECT
  *,
  CASE
    WHEN EXISTS (SELECT 1 FROM 表 t2 WHERE t2.campaign_id = t1.campaign_id AND t2.ad_status = 'ACTIVE') THEN 'ACTIVE'
    ELSE 'INACTIVE'
  END AS campaign_status
FROM 表 t1

这种写法其实在BigQuery中是可行的，但窗口函数的性能更优，尤其是数据量较大时，写法也更简洁。

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