BigQuery中‘Scalar subquery produced more than one element’错误求助

错误原因 #

你遇到的错误核心是：SELECT语句中的标量子查询（比如(SELECT type FROM UNNEST(hits))）会从hits数组返回多个值，但标量子查询要求只能返回单个值，因此触发报错。另外代码存在重复定义CTE、关联逻辑不准确的问题，导致无法正确定位用户最后一次访问的最后一个hit。

修正后的代码 #

WITH non_buyers_last_visit AS (
  -- 筛选从未购买的用户，获取他们的最后一次访问编号
  SELECT
    fullVisitorId AS visitor_id,
    MAX(visitNumber) AS last_visit_number
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  GROUP BY fullVisitorId
  HAVING SUM(IFNULL(totals.transactions, 0)) = 0 -- 确保无任何购买记录
),
user_last_session AS (
  -- 获取这些用户最后一次访问的完整会话数据
  SELECT
    n.visitor_id,
    n.last_visit_number,
    s.hits
  FROM non_buyers_last_visit n
  JOIN `bigquery-public-data.google_analytics_sample.ga_sessions_*` s
    ON n.visitor_id = s.fullVisitorId
    AND n.last_visit_number = s.visitNumber
),
user_last_hit AS (
  -- 展开hits数组，筛选每个会话中最后一个hit
  SELECT
    visitor_id,
    last_visit_number AS last_visit,
    hit.hitNumber AS last_hit,
    hit.type,
    hit.page.pagePath AS page,
    -- 取hit中第一个产品（可根据需求调整，比如合并所有产品）
    ARRAY(SELECT v2ProductName FROM UNNEST(hit.product))[OFFSET(0)] AS product,
    hit.eventInfo.eventAction AS action,
    hit.eventInfo.eventLabel AS label
  FROM user_last_session, UNNEST(hits) hit
  QUALIFY ROW_NUMBER() OVER(PARTITION BY visitor_id ORDER BY hit.hitNumber DESC) = 1
)
-- 输出所需结果集
SELECT
  visitor_id,
  last_visit,
  last_hit,
  type,
  page,
  product,
  action,
  label
FROM user_last_hit

关键优化点 #

• 用SUM(IFNULL(totals.transactions, 0)) = 0替代原有的COUNT判断：当用户无交易时，totals.transactions为NULL，COUNT会忽略NULL，SUM能更准确判断是否有过购买行为。
• 用QUALIFY + ROW_NUMBER()直接筛选最后一个hit：无需先查询MAX(hitNumber)再关联，一步到位定位目标记录，逻辑更简洁。
• 处理产品字段：每个hit可能包含多个产品，这里用数组索引取第一个，若需要展示所有产品，可替换为ARRAY_TO_STRING(ARRAY(SELECT v2ProductName FROM UNNEST(hit.product)), ", ")合并为字符串。
• 移除重复CTE定义：将逻辑拆分为清晰的三步，避免冗余。

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