在BigQuery中从相似模式字符串提取指定内容

核心思路 #

要从合并评论字段中提取以"ET"开头、且在"dc viewed"之前出现的首个用户名，用BigQuery的正则表达式函数REGEXP_EXTRACT是最简洁高效的方式，它能直接匹配符合条件的字符串片段。

基础SQL示例 #

假设你的表是your_project.your_dataset.complaint_tracker，合并评论字段名为combined_comments，可以用以下查询：

SELECT
  complaint_id, -- 替换为你的主键字段
  REGEXP_EXTRACT(combined_comments, r'(ET\S+)(?=.*dc viewed)') AS dc_first_confirm_user
FROM
  `your_project.your_dataset.complaint_tracker`
WHERE
  combined_comments LIKE '%ET%' -- 过滤掉无目标用户的记录
  AND combined_comments LIKE '%dc viewed%'

正则表达式说明 #

• ET\S+：匹配以"ET"开头的连续非空白字符（适用于用户名无空格的场景）
• (?=.*dc viewed)：正向预查规则，确保匹配到的用户名出现在"dc viewed"内容之前
• REGEXP_EXTRACT默认返回第一个匹配到的结果，正好符合你"首个确认请求用户"的需求

适配含空格的用户名 #

如果用户名包含空格（比如ET Jane Doe），调整正则为非贪婪匹配模式即可：

SELECT
  complaint_id,
  REGEXP_EXTRACT(combined_comments, r'(ET[\w\s]+?)(?=.*dc viewed)') AS dc_first_confirm_user
FROM
  `your_project.your_dataset.complaint_tracker`
WHERE
  combined_comments LIKE '%ET%'
  AND combined_comments LIKE '%dc viewed%'

精准匹配带格式的用户名 #

如果用户名被特定符号包裹（比如[ET Jane Doe]），修改正则匹配符号内的内容：

SELECT
  complaint_id,
  REGEXP_EXTRACT(combined_comments, r'\[(ET[\w\s]+?)\](?=.*dc viewed)') AS dc_first_confirm_user
FROM
  `your_project.your_dataset.complaint_tracker`
WHERE
  combined_comments LIKE '%ET%'
  AND combined_comments LIKE '%dc viewed%'

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