如何用regexp检测表中文本？解决标量子查询多元素错误

错误原因 #

你的子查询SELECT shop_domain FROM data返回了多条店铺域名记录，但regexp_contains函数的第二个参数只能接受单个字符串，因此触发了“Scalar subquery produced more than one element”错误。

可行解决方案 #

方案一：使用EXISTS子查询（推荐，性能更优）

通过EXISTS直接判断当前流量的入口页面是否匹配任意一条已注册店铺域名，逻辑清晰且避免多值问题：

SELECT 
  entrance_page_name,
  Visitor,
  CASE 
    WHEN EXISTS (
      SELECT 1 
      FROM `data` d
      WHERE regexp_contains(t1.entrance_page_name, d.shop_domain)
    ) THEN TRUE 
    ELSE FALSE 
  END AS is_registered_shop
FROM 表1 t1

方案二：聚合域名生成正则匹配模式

如果你的SQL引擎支持字符串聚合（比如BigQuery、PostgreSQL），可以将所有已注册域名合并成一个用|分隔的正则表达式，一次性匹配所有域名：

WITH domain_list AS (
  -- 聚合所有域名，若域名包含正则特殊字符（如.、*）需先转义
  SELECT STRING_AGG(REGEXP_REPLACE(shop_domain, r'([\.\\\*\+\?\|\(\)\[\]\{\}\^$])', r'\\\1'), '|') AS domain_pattern
  FROM `data`
)
SELECT 
  entrance_page_name,
  Visitor,
  CASE 
    WHEN regexp_contains(t1.entrance_page_name, dl.domain_pattern) THEN TRUE 
    ELSE FALSE 
  END AS is_registered_shop
FROM 表1 t1
CROSS JOIN domain_list dl

方案三：LEFT JOIN结合分组判断

通过左连接关联两张表，再分组判断是否存在匹配的注册店铺：

SELECT 
  t1.entrance_page_name,
  t1.Visitor,
  MAX(CASE WHEN d.shop_id IS NOT NULL THEN TRUE ELSE FALSE END) AS is_registered_shop
FROM 表1 t1
LEFT JOIN `data` d ON regexp_contains(t1.entrance_page_name, d.shop_domain)
GROUP BY t1.entrance_page_name, t1.Visitor

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