如何在BigQuery中基于字符串列的数组元素过滤匹配事件数据

先做表命名约定：

• 存储特征组与关键词映射的第一张表命名为 feature_mapping，其中array_words为ARRAY数组类型字段
• 存储事件数据的第二张表命名为 event_table

可直接运行的SQL代码 #

SELECT DISTINCT
  e.events,
  f.feature_groups
FROM
  -- 替换为你自己的事件表实际路径
  `your_project.your_dataset.event_table` e
CROSS JOIN
  -- 替换为你自己的特征映射表实际路径
  `your_project.your_dataset.feature_mapping` f,
  -- 拆解关键词数组为单个关键词行
  UNNEST(f.array_words) AS keyword
WHERE
  -- 判断事件文本是否包含关键词，返回位置大于0即存在匹配
  STRPOS(e.events, keyword) > 0

逻辑说明 #

• UNNEST 函数会将array_words的数组元素拆分为独立的单行数据，每条数据对应一个关键词，解决数组无法直接匹配的问题
• STRPOS 函数用于检索关键词在事件文本中的起始位置，返回值大于0即代表事件包含该关键词
• 加入DISTINCT是为了避免同一事件匹配到同个特征组下的多个关键词时，出现重复的结果行
• 如果需要忽略大小写匹配，可将WHERE条件修改为 STRPOS(LOWER(e.events), LOWER(keyword)) > 0 即可

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