BigQuery中基于数组列与逗号分隔列匹配添加标识列的需求

场景说明 #

现有两张表结构如下：

• Table A
  • column1：单个字符串值（示例：a）
  • column2：逗号分隔的字符串（示例：aa,ab,ac）
  • column3：其他业务字段
• Table B
  • column1：字符串数组类型（示例：[a,b,c,d]）
  • column2：字符串数组类型（示例：[aa,ad]）
  • column4：其他业务字段

需要实现：当Table A满足以下两个条件时，在结果表末尾新增一列标记为yes：

1. column1的值存在于Table B的column1数组中
2. column2拆分后的任意值存在于Table B的column2数组中

解决方案SQL #

SELECT
  a.*,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM `your-project.dataset.TableB` b
      -- 判断Table A的column1是否在Table B的column1数组中
      WHERE ARRAY_CONTAINS(b.column1, a.column1)
        -- 判断Table A的column2拆分后是否有值在Table B的column2数组中
        AND EXISTS (
          SELECT 1
          FROM UNNEST(SPLIT(a.column2, ',')) AS split_val
          WHERE ARRAY_CONTAINS(b.column2, TRIM(split_val)) -- 处理拆分后可能存在的空格
        )
    ) THEN 'yes'
    ELSE NULL -- 可根据需求改为空字符串或其他默认值
  END AS match_flag
FROM `your-project.dataset.TableA` a

代码解释 #

1. 拆分逗号分隔字符串：使用SPLIT(a.column2, ',')将Table A的column2拆分为字符串列表，再通过UNNEST展开为行；TRIM(split_val)用于处理字符串中可能存在的空格（比如aa, ab这种带空格的情况）。
2. 数组包含判断：ARRAY_CONTAINS(array, value)是BigQuery内置函数，用于判断单个值是否存在于目标数组中。
3. 条件校验：通过两层EXISTS子查询，分别校验两个匹配条件，只要存在任意一条符合条件的Table B记录，就标记为yes。
4. 自定义默认值：如果不需要标记的行可以返回NULL，也可以改为空字符串''或其他自定义值。

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