BigQuery标量子查询多行错误解决：搜索词与分类匹配统计

数据表说明 #

• searches表：记录用户搜索行为，字段包括search_id（搜索ID）、search_name（搜索内容）
• categories表：记录商品分类，字段包括category_id（分类ID）、category_name（分类名称）

需求 #

获取出现次数≥3的高频搜索词，统计各商品分类在这些搜索词中的出现次数，预期结果：

错误原因与修正方案 #

原SQL触发Scalar subquery produced more than one element错误，是因为标量子查询返回了多行结果，同时存在CTE语法错误、关联逻辑错误。以下是修正后的SQL：

WITH search_results AS (
  -- 筛选出现次数≥3的高频搜索词
  SELECT search_name
  FROM searches
  GROUP BY search_name
  HAVING COUNT(*) >= 3
)
-- 关联统计各分类在高频搜索词中的出现次数
SELECT 
  COUNT(s.search_name) AS f_,
  c.category_name
FROM categories c
JOIN search_results s
ON REGEXP_CONTAINS(s.search_name, c.category_name) -- 判断搜索词是否包含分类名
GROUP BY c.category_name
ORDER BY f_ DESC;

关键修改点 #

1. 修正CTE语法：将错误的WITH AS serach_results改为WITH search_results AS (...)，移除CTE中多余的count(*)（仅需搜索词），子句结尾用逗号而非句号；
2. 替换关联方式：用JOIN替代WHERE中的标量子查询，避免多行结果报错，通过REGEXP_CONTAINS建立搜索词与分类名的匹配关系；
3. 调整统计逻辑：统计匹配到的高频搜索词数量，对应预期结果的f_字段，按分类名分组得到最终统计数据。

注：若实际需求是分类名包含搜索词，可将REGEXP_CONTAINS的参数调换为REGEXP_CONTAINS(c.category_name, s.search_name)

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