咨询BigQuery中如何基于黑名单表提取产品描述中的匹配术语并聚合结果

你完全不需要把黑名单转成数组，用正则表达式结合分组字符串聚合就能完美实现你的需求，还能轻松处理例子里的大小写混合匹配场景（比如whatsapp WEB、INSTAGRAM这类情况）。

这里直接给你可运行的SQL代码（以BigQuery为例，其他方言我会补充适配方案）：

WITH blocklist AS (
  SELECT 'instagram' AS blocklist UNION ALL
  SELECT 'facebook' AS blocklist UNION ALL
  SELECT 'whatsapp web' AS blocklist
),
products AS (
  SELECT 'seller1' AS seller, 'Tenis Nike 43 call me on instagram or facebook' AS product UNION ALL
  SELECT 'seller1' AS seller, 'TV 42 sansung link whatsapp WEB or INSTAGRAM' AS product UNION ALL
  SELECT 'seller2' AS seller, 'TV 42 sansung link' AS product
)
SELECT
  p.seller,
  p.product,
  STRING_AGG(DISTINCT b.blocklist, ',') AS blocklists
FROM products p
LEFT JOIN blocklist b
  ON REGEXP_CONTAINS(p.product, CONCAT(r'\b', REGEXP_ESCAPE(b.blocklist), r'\b'), 'i')
GROUP BY p.seller, p.product
ORDER BY p.seller;

关键逻辑说明： #

• 大小写不敏感的精准匹配：用REGEXP_CONTAINS的'i'参数忽略大小写，\b是单词边界，确保只匹配完整的黑名单术语（不会把instagramabc误判为匹配instagram）；REGEXP_ESCAPE用来转义黑名单里可能存在的正则特殊字符（比如.、*这类），避免匹配出错。
• 聚合匹配结果：STRING_AGG(DISTINCT ...)把每个产品匹配到的所有黑名单术语用逗号拼接，DISTINCT防止同一个术语因多次出现在描述里而重复显示。
• 全量返回产品：用LEFT JOIN保证即使没有匹配到任何黑名单的产品（比如seller2的那条）也会被返回，此时blocklists自然为null，完全符合你的期望输出。

适配其他SQL方言（以MySQL为例）： #

如果你的数据库是MySQL，需要调整函数名称和正则语法：

-- MySQL 8.0+版本
WITH blocklist AS (
  SELECT 'instagram' AS blocklist UNION ALL
  SELECT 'facebook' AS blocklist UNION ALL
  SELECT 'whatsapp web' AS blocklist
),
products AS (
  SELECT 'seller1' AS seller, 'Tenis Nike 43 call me on instagram or facebook' AS product UNION ALL
  SELECT 'seller1' AS seller, 'TV 42 sansung link whatsapp WEB or INSTAGRAM' AS product UNION ALL
  SELECT 'seller2' AS seller, 'TV 42 sansung link' AS product
)
SELECT
  p.seller,
  p.product,
  GROUP_CONCAT(DISTINCT b.blocklist SEPARATOR ',') AS blocklists
FROM products p
LEFT JOIN blocklist b
  ON REGEXP_INSTR(p.product, CONCAT('[[:<:]]', REGEXP_REPLACE(b.blocklist, '([\\.\\*\\+\\?\\|\\(\\)\\[\\]\\{\\}\\-\\\\])', '\\\\$1'), '[[:>:]]'), 1, 1, 0, 'i') > 0
GROUP BY p.seller, p.product
ORDER BY p.seller;

这种方案比转数组的方式更直观，也更容易维护——后续要修改黑名单的话，直接调整blocklist CTE里的内容就行，不需要改动聚合逻辑。

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