BigQuery中按ID分组筛选含特殊编码或常规编码的行

问题描述 #

现有BigQuery表格数据如下：

ID  CODE     
aa  code-r           
aa  code-k           
aa  code-s
aa  code-special-r
aa  code-special-k
aa  code-t          
bb  code-r           
bb  code-k
bb  code-special-k
bb  code-t          
cc  code-r            
cc  code-k           
cc  code-t

需求：针对每个ID，若存在code-special-*类型的编码，则：

• 保留所有code-special-*行
• 保留没有对应code-special-*版本的常规编码行（比如code-s没有code-special-s则保留；code-r有code-special-r则丢弃）
  若ID不存在任何code-special-*编码，则保留所有常规编码行。

期望结果：

ID  CODE               
aa  code-s
aa  code-special-r
aa  code-special-k
aa  code-t          
bb  code-r           
bb  code-special-k
bb  code-t          
cc  code-r            
cc  code-k           
cc  code-t

解决方案 #

可以通过窗口函数标记ID是否包含特殊编码，再结合子查询判断常规编码是否有对应特殊版本来实现：

WITH original_data AS (
  SELECT 'aa' AS ID, 'code-r' AS CODE UNION ALL
  SELECT 'aa' AS ID, 'code-k' AS CODE UNION ALL
  SELECT 'aa' AS ID, 'code-s' AS CODE UNION ALL
  SELECT 'aa' AS ID, 'code-special-r' AS CODE UNION ALL
  SELECT 'aa' AS ID, 'code-special-k' AS CODE UNION ALL
  SELECT 'aa' AS ID, 'code-t' AS CODE UNION ALL
  SELECT 'bb' AS ID, 'code-r' AS CODE UNION ALL
  SELECT 'bb' AS ID, 'code-k' AS CODE UNION ALL
  SELECT 'bb' AS ID, 'code-special-k' AS CODE UNION ALL
  SELECT 'bb' AS ID, 'code-t' AS CODE UNION ALL
  SELECT 'cc' AS ID, 'code-r' AS CODE UNION ALL
  SELECT 'cc' AS ID, 'code-k' AS CODE UNION ALL
  SELECT 'cc' AS ID, 'code-t' AS CODE
),
id_with_special_flag AS (
  SELECT 
    *,
    -- 标记当前ID是否存在特殊编码
    MAX(CASE WHEN CODE LIKE 'code-special-%' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_special
  FROM original_data
)
SELECT ID, CODE
FROM id_with_special_flag
WHERE 
  -- 情况1：ID没有特殊编码，直接保留所有行
  has_special = 0
  OR
  -- 情况2：ID有特殊编码，保留特殊编码行
  CODE LIKE 'code-special-%'
  OR
  -- 情况3：ID有特殊编码，常规编码无对应特殊版本则保留
  (
    CODE NOT LIKE 'code-special-%'
    AND NOT EXISTS (
      SELECT 1
      FROM original_data od
      WHERE od.ID = id_with_special_flag.ID
        AND od.CODE = CONCAT('code-special-', SPLIT(id_with_special_flag.CODE, '-')[OFFSET(1)])
    )
  )
ORDER BY ID, CODE;

代码说明 #

1. CTE original_data：模拟原始表格数据，实际使用时替换为你的真实表名即可。
2. CTE id_with_special_flag：通过窗口函数MAX() OVER (PARTITION BY ID)给每个ID标记是否包含特殊编码，has_special=1表示存在特殊编码，0表示不存在。
3. 主查询筛选逻辑：
   • 无特殊编码的ID，直接保留所有行；
   • 有特殊编码的ID，优先保留所有特殊编码行；
   • 有特殊编码的ID，常规编码行仅在不存在对应特殊版本时保留，通过EXISTS子查询检查是否存在匹配的code-special-xxx编码。

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