BigQuery基于LIKE匹配含重复行映射表的更新问题求解

核心思路是先给原始数据表的每一行添加唯一标识，避免重复行被分组合并，同时提前对映射表去重减少无效匹配。

方案1：单匹配结果输出（匹配到任意值即可，符合最初的预期） #

验证查询语句 #

WITH dedup_mapping AS (
  -- 映射表去重，避免重复项产生多余匹配
  SELECT DISTINCT mappingItem FROM table2
),
table1_with_rowid AS (
  -- 给table1所有行添加唯一行号，区分完全重复的行
  SELECT *, ROW_NUMBER() OVER() AS row_id FROM table1
)
SELECT
  t.textWithFoundItemInIt,
  -- 取任意一个匹配值，用MAX或MIN都可
  MAX(m.mappingItem) AS foundItem
FROM table1_with_rowid t
LEFT JOIN dedup_mapping m
  ON t.textWithFoundItemInIt LIKE CONCAT('%', m.mappingItem, '%')
-- 分组时带上行号，保证每一行独立保留
GROUP BY t.row_id, t.textWithFoundItemInIt
ORDER BY t.row_id

原表更新语句 #

UPDATE table1 t1
SET foundItem = t2.foundItem
FROM (
  WITH dedup_mapping AS (
    SELECT DISTINCT mappingItem FROM table2
  ),
  table1_with_rowid AS (
    SELECT *, ROW_NUMBER() OVER() AS row_id FROM table1
  )
  SELECT
    row_id,
    textWithFoundItemInIt,
    MAX(m.mappingItem) AS foundItem
  FROM table1_with_rowid t
  LEFT JOIN dedup_mapping m
    ON t.textWithFoundItemInIt LIKE CONCAT('%', m.mappingItem, '%')
  GROUP BY row_id, textWithFoundItemInIt
) t2
-- 用文本内容+行号联合定位到原表的每一行
WHERE t1.textWithFoundItemInIt = t2.textWithFoundItemInIt
AND ROW_NUMBER() OVER(PARTITION BY t1.textWithFoundItemInIt) = t2.row_id

方案2：多匹配结果输出（所有匹配到的项拼接输出） #

如果需要输出一行文本中所有匹配到的映射值，用以下语句，结果会用逗号分隔多个匹配项：

WITH dedup_mapping AS (
  SELECT DISTINCT mappingItem FROM table2
),
table1_with_rowid AS (
  SELECT *, ROW_NUMBER() OVER() AS row_id FROM table1
)
SELECT
  t.textWithFoundItemInIt,
  STRING_AGG(DISTINCT m.mappingItem, ', ') AS foundItem
FROM table1_with_rowid t
LEFT JOIN dedup_mapping m
  ON t.textWithFoundItemInIt LIKE CONCAT('%', m.mappingItem, '%')
GROUP BY t.row_id, t.textWithFoundItemInIt
ORDER BY t.row_id

如果需要拆成多列输出，可在上述结果基础上用SPLIT函数拆分，或者用PIVOT语法实现，不过需要提前确认最多匹配的项数。

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