Scalar subquery返回多行报错：我的BigQuery SQL语句问题排查

嘿，这个报错我太熟了——“Scalar subquery produced more than one element”说白了就是你写的标量子查询返回了不止一行结果，但SQL要求标量子查询（就是那些放在SELECT列表里的小查询）必须只能返回单个值。咱们来拆解你的问题：

错误根源 #

你在SELECT里写的两个子查询：

(SELECT A.name FROM `pms_table_request_log.partners_request_log_*`, UNNEST(jsonPayload.response.body.data.rows) AS A) AS Partner

和

AVG((SELECT B.rating FROM `pms_table_request_log.partners_request_log_*`, UNNEST(jsonPayload.response.body.data.rows) AS B)) AS Rating

都是全局扫描所有日志的rows数组，完全没有和主查询当前行的日志做关联。举个例子：如果你的表里有10条日志，每条日志的rows有3个元素，那第一个子查询会返回30个name值，这显然不符合标量子查询“返回单个值”的要求，直接触发报错。

修正后的SQL #

正确的做法是先把每个日志的rows数组展开成单独的行（在FROM阶段用UNNEST），这样每一行就对应一个Partner的数据，之后直接基于展开后的行做聚合即可：

SELECT 
  EXTRACT(DATE FROM timestamp) AS Date,
  jsonPayload.request.headers.origin AS URL,
  jsonPayload.response.body.json.data.country.key AS Country,
  A.name AS Partner,
  AVG(A.rating) AS Rating,
  COUNT(*) AS Impression
FROM 
  `pms_table_request_log.partners_request_log_*`,
  UNNEST(jsonPayload.response.body.data.rows) AS A
GROUP BY 
  Date, URL, Country, Partner
ORDER BY 
  Impression DESC;

关键修正点 #

1. 提前UNNEST数组：在FROM阶段直接展开jsonPayload.response.body.data.rows为表A，这样每条日志会被拆分成多个行，每个行对应一个Partner的信息。
2. 直接引用展开后的字段：不需要再用子查询，直接用A.name作为Partner，A.rating计算平均值，这样每个分组自然对应单个Partner的数据。
3. 聚合逻辑更合理：COUNT(*)会统计每个Partner对应的日志条目数（展开后的行数），AVG(A.rating)会计算该Partner的平均评分，完全符合你的需求。

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