在BigQuery中查询GA数据时，如何用模糊匹配排除指定站点？

你遇到的Scalar subquery produced more than one element错误，根源在于你的子查询返回了多行结果，但LIKE操作符只能和单个值进行匹配，没法直接处理多个匹配模式。下面给你两种可行的解决方案，帮你精准排除目标站点：

方案一：使用NOT EXISTS关联子查询 #

这种方式会逐行检查当前处理后的URL是否存在于排除列表的匹配规则中，只有完全不匹配的记录才会被保留，逻辑清晰且性能不错：

SELECT DISTINCT 
  h.eventinfo.eventAction AS eventAction, 
  h.eventinfo.eventlabel AS eventlabel 
FROM `projectName.ga_sessions_*`, UNNEST(Hits) h 
WHERE _TABLE_SUFFIX BETWEEN "20190101" AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) 
  AND type = 'EVENT' 
  AND h.eventInfo.eventCategory = 'EventName'
  -- 核心：检查当前URL是否不在排除列表的匹配范围内
  AND NOT EXISTS (
    SELECT 1 
    FROM `projectName.datasetName.ExclusionList` el
    -- 用正则简化URL前缀清理，替代多次REPLACE
    WHERE REGEXP_REPLACE(h.eventInfo.eventLabel, r'^(https?://|www\.)', '') LIKE CONCAT(el.ThirdPartyURL, '%')
  )

方案二：使用LEFT JOIN + IS NULL过滤 #

通过左连接把主数据和排除列表关联，然后过滤掉匹配到排除项的记录，也是一种常用的排除逻辑：

SELECT DISTINCT 
  h.eventinfo.eventAction AS eventAction, 
  h.eventinfo.eventlabel AS eventlabel 
FROM `projectName.ga_sessions_*`, UNNEST(Hits) h 
-- 左连接排除列表，匹配规则和上面一致
LEFT JOIN `projectName.datasetName.ExclusionList` el
  ON REGEXP_REPLACE(h.eventInfo.eventLabel, r'^(https?://|www\.)', '') LIKE CONCAT(el.ThirdPartyURL, '%')
WHERE _TABLE_SUFFIX BETWEEN "20190101" AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) 
  AND type = 'EVENT' 
  AND h.eventInfo.eventCategory = 'EventName'
  -- 只保留没有匹配到排除项的记录
  AND el.ThirdPartyURL IS NULL

额外优化点 #

我把你原来的三次REPLACE换成了REGEXP_REPLACE，用正则表达式r'^(https?://|www\.)'可以一次性去掉http://、https://和www.前缀，代码更简洁也更易维护。

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