BigQuery SQL：按唯一ID分组实现符合特定代码条件的计数

问题背景 #

现有如下输入表（表名假设为your_table）：

需要为每个唯一ID生成Count列，满足以下条件时Count为1，否则为0：

• 该ID的Code列必须包含121A；
• 除121A外，至少包含333B或334B中的一个（即使同时有其他非指定代码也符合条件）。

期望输出表：

不熟悉BigQuery SQL，求对应的查询语句。

解决方案 #

可以通过分组聚合结合条件判断实现，以下是BigQuery SQL语句：

SELECT
  ID,
  CASE
    WHEN MAX(Code = '121A') = 1 
         AND MAX(Code IN ('333B', '334B')) = 1
    THEN 1
    ELSE 0
  END AS Count
FROM your_table
GROUP BY ID

语句解释 #

• GROUP BY ID：按ID分组，对每个ID的所有Code记录进行聚合计算；
• MAX(Code = '121A')：如果该ID包含121A，表达式返回1，否则返回0，取最大值判断是否存在该Code；
• MAX(Code IN ('333B', '334B'))：同理，判断该ID是否包含333B或334B中的任意一个；
• CASE语句：当两个条件同时满足时返回1，否则返回0，生成目标Count列。

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