BigQuery中SQL报错‘Each function argument is an expression, not a query’解析与修复

错误含义 #

这个报错的核心是：你给MAX()函数传了一个返回多行结果的查询，但MAX()只能接收单个表达式，或者返回单行单列的标量子查询。你的子查询SELECT AVG(quantity) FROM \farmers_market.customer_purchases` GROUP BY customer_id会输出每一位客户的平均购买量，是多行数据，没法直接作为MAX()`的参数，所以BigQuery抛出了这个错误。

针对不同需求的解决方案 #

以下分两种常见需求场景给出解决代码：

场景1：筛选自身最大购买量大于自身平均购买量的客户 #

如果要找「某客户的单笔最大购买数量」超过「该客户所有购买记录平均数量」的客户，直接在分组后计算对应聚合值即可：

SELECT customer_id 
FROM customer_purchases
GROUP BY customer_id
HAVING MAX(quantity) > AVG(quantity)
ORDER BY customer_id

场景2：筛选最大购买量大于所有客户平均购买量的客户 #

如果要找「客户的最大购买数量」超过「所有客户各自平均购买量的整体平均值」，需要先嵌套子查询算出这个整体均值，再做比较：

SELECT customer_id 
FROM customer_purchases
GROUP BY customer_id
HAVING MAX(quantity) > (
  SELECT AVG(customer_avg)
  FROM (
    SELECT AVG(quantity) AS customer_avg
    FROM `farmers_market.customer_purchases`
    GROUP BY customer_id
  )
)
ORDER BY customer_id

如果你的需求是「客户的最大购买量超过所有购买记录的整体平均」（不区分客户，直接计算所有quantity的平均值），代码更简洁：

SELECT customer_id 
FROM customer_purchases
GROUP BY customer_id
HAVING MAX(quantity) > (SELECT AVG(quantity) FROM `farmers_market.customer_purchases`)
ORDER BY customer_id

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