BigQuery中基于行相似度过滤搜索分析表部分查询的实现方法

核心逻辑：我们要保留的最终搜索词，是不存在任何其他更长的搜索词包含它作为子串的查询。

实现SQL #

替换SQL中的表路径为你自己的BigQuery表地址即可使用：

WITH unique_queries AS (
  -- 先对原始query去重，避免重复数据干扰判断
  SELECT DISTINCT query
  FROM `你的项目ID.你的数据集名.你的搜索词表名`
)
SELECT q1.query
FROM unique_queries q1
-- 自连接查找所有包含当前query的更长的其他query
LEFT JOIN unique_queries q2
  ON LENGTH(q2.query) > LENGTH(q1.query)
  AND q2.query LIKE CONCAT('%', q1.query, '%')
-- 没有找到匹配的q2，说明当前query不是任何其他query的子串，符合要求
WHERE q2.query IS NULL
ORDER BY q1.query

逻辑说明 #

• 先做去重处理，避免同一个query多次出现导致的匹配异常
• 加LENGTH(q2.query) > LENGTH(q1.query)条件既可以大幅减少连接计算量，也能避免自身和自身匹配的问题
• 拼写错误的独立query因为没有其他更长的query包含它，会被正常保留，符合需求
• 短的有效查询（比如单独的addition）只要没有更长的查询包含它，就不会被误筛，完全符合需求

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