如何判断字符串型ID是否存在于同表其他行的字符串列并返回T/F

问题分析 #

你需要判断表中每个key1的ID是否存在于其他行的string列（分号分隔的ID列表）中，但之前的方法存在两个核心问题：

• 使用IN/EXISTS时是严格匹配整个string值，无法识别其中单个分号分隔的ID，逻辑不符合需求
• 用regexp_contains做关联查询会产生大量笛卡尔积，直接导致CPU资源超限

解决方案 #

以下两种方法可以实现正确逻辑并有效控制资源消耗：

方法一：拆分字符串后精确匹配（推荐，资源消耗更低）

先将分号分隔的string拆成单独的ID行，再通过精确匹配判断存在性，彻底避免正则表达式的性能开销：

WITH split_strings AS (
  -- 筛选非空string并按分号拆分为ID列表
  SELECT SPLIT(string, ';') AS id_list
  FROM table1
  WHERE string IS NOT NULL
),
flattened_ids AS (
  -- 展开拆分后的ID列表，同时去除可能存在的空格
  SELECT TRIM(id) AS referenced_id
  FROM split_strings, UNNEST(id_list) AS id
)
-- 对每个key1判断是否存在被其他行引用的记录
SELECT 
  key1,
  EXISTS(SELECT 1 FROM flattened_ids WHERE referenced_id = key1) AS is_referenced
FROM table1

方法二：优化正则匹配的半连接查询

如果无法拆分字符串，可通过EXISTS半连接替代LEFT JOIN减少数据扫描量，同时优化正则表达式避免部分匹配问题：

SELECT 
  key1,
  EXISTS(
    SELECT 1 
    FROM table1 AS t2
    WHERE t2.string IS NOT NULL
      -- 正则匹配完整ID，确保是独立的分号分隔项（避免001被0011误匹配）
      AND REGEXP_CONTAINS(t2.string, r'(^|;)(' || key1 || ')(;|$)')
      -- 排除当前行，只检查其他行的引用关系
      AND t2.key1 != table1.key1
  ) AS is_referenced
FROM table1

额外优化建议 #

• 给key1和string列添加索引，大幅加速查询的过滤和匹配环节
• 优先选择拆分字符串的方案，云数仓（如BigQuery、Snowflake）的SPLIT/UNNEST原生函数性能远高于正则匹配
• 提前过滤string为空的行，减少后续处理的数据量级

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