如何在BigQuery中用数组元素实现类LIKE运算符的匹配？

要实现用keyword_table里的数组关键词，模糊匹配config_table的item字段并关联分类，核心是先把数组拆成单行关键词，再做LIKE匹配。下面是主流SQL引擎的具体实现：

Hive/Spark SQL 写法 #

SELECT DISTINCT
    c.code,
    k.category
FROM
    config_table c
JOIN
    keyword_table k
LATERAL VIEW EXPLODE(k.keywords) exploded AS kw
WHERE
    c.item LIKE CONCAT('%', kw, '%');

• 要点：
  • 用LATERAL VIEW EXPLODE把数组拆成每行一个关键词，比如fruits对应的数组会拆成3行，每行对应一个水果关键词
  • CONCAT('%', kw, '%')做包含式模糊匹配，只要item里包含这个关键词就算匹配
  • 加DISTINCT避免同一个code匹配同分类下多个关键词时重复输出

BigQuery 写法 #

BigQuery用UNNEST展开数组，语法更简洁：

SELECT DISTINCT
    c.code,
    k.category
FROM
    config_table c
JOIN
    keyword_table k,
    UNNEST(k.keywords) AS kw
WHERE
    c.item LIKE CONCAT('%', kw, '%');

PostgreSQL 写法 #

PostgreSQL用unnest函数配合JOIN实现：

SELECT DISTINCT
    c.code,
    k.category
FROM
    config_table c
JOIN keyword_table k
JOIN unnest(k.keywords) AS kw
ON c.item LIKE '%' || kw || '%';

测试效果 #

假设你有这些测试数据：

config_table 数据 #

执行SQL后会得到预期结果：

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