BigQuery SQL：按唯一ID分组时条件统计指定代码列值

需求说明 #

针对给定的ID和Code表，需要为每个唯一ID生成Count列：当该ID在指定代码列表['323F','327F','328F','333F','357F']中仅存在1条匹配记录时，Count为1；其余情况（匹配数为0或≥2）均为0。

实现方案 #

简洁版SQL（推荐）

直接通过分组+条件计数实现，无需额外中间表，执行效率更高：

SELECT 
  ID,
  CASE 
    WHEN COUNT(CASE WHEN Code IN ('323F', '327F', '328F', '333F', '357F') THEN 1 END) = 1 THEN 1
    ELSE 0
  END AS Count
FROM 
  `your-project.your-dataset.your-table` -- 替换为你的实际表路径
GROUP BY 
  ID
ORDER BY 
  ID;

分步版SQL（逻辑更直观）

通过CTE拆分步骤，便于理解每一步的处理逻辑：

-- 1. 筛选出属于指定代码列表的记录
WITH filtered_codes AS (
  SELECT ID, Code
  FROM `your-project.your-dataset.your-table`
  WHERE Code IN ('323F', '327F', '328F', '333F', '357F')
),
-- 2. 统计每个ID在指定列表中的匹配数量
id_match_stats AS (
  SELECT ID, COUNT(*) AS match_count
  FROM filtered_codes
  GROUP BY ID
)
-- 3. 生成最终结果：覆盖所有唯一ID，判断匹配数量是否为1
SELECT 
  original.ID,
  CASE 
    WHEN COALESCE(stats.match_count, 0) = 1 THEN 1
    ELSE 0
  END AS Count
FROM (SELECT DISTINCT ID FROM `your-project.your-dataset.your-table`) original
LEFT JOIN id_match_stats stats ON original.ID = stats.ID
ORDER BY original.ID;

核心逻辑解释 #

• 条件计数：COUNT(CASE WHEN Code IN (...) THEN 1 END)会自动忽略不符合条件的记录（返回NULL），仅统计指定列表内的Code数量。
• 分支判断：通过CASE语句判断统计出的匹配数是否等于1，对应设置Count的取值。
• 全ID覆盖：分步版通过左连接确保原表中所有唯一ID都被包含，即使该ID在指定列表中没有匹配记录（此时匹配数为0，Count为0）。

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