BigQuery无分类场景下计算修剪均值时遇标量子查询返回多行错误的求助

我太懂你现在的困扰了——想给没有分类的客户生命周期数据计算修剪均值，结果被标量子查询返回多行的错误卡得死死的对吧？咱们来拆解问题，一步步解决它。

问题根源分析 #

你写的bounds CTE里用了窗口函数PERCENTILE_CONT(...) OVER()，这个窗口函数没有指定分区，所以会给uniqueClientsWithLifeTime里的每一行都生成相同的lower_bound和upper_bound值。也就是说，bounds表的行数和原表完全一样，全是重复的四分位数。

当你在WHERE条件里用(SELECT bounds.lower_bound FROM bounds)时，这个子查询会返回所有重复的lower_bound值，而BigQuery的标量子查询要求必须只返回一行结果，所以就触发了“Scalar subquery produced more than one element”的错误。

解决方案：让bounds只返回一行结果 #

我们只需要调整bounds的写法，让它直接输出单行的上下四分位数，这样后续的子查询就不会有返回多行的问题了。这里有两种靠谱的写法：

写法1：用聚合版的PERCENTILE_CONT（推荐）

直接用聚合函数形式的PERCENTILE_CONT，不用窗口函数，这样会直接计算整个数据集的四分位数并返回单行结果：

WITH
bounds AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY ClientLifeTime) AS lower_bound,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY ClientLifeTime) AS upper_bound
  FROM 
    uniqueClientsWithLifeTime
)

SELECT
  AVG(ClientLifeTime) AS trimmed_mean
FROM 
  uniqueClientsWithLifeTime
WHERE 
  ClientLifeTime > (SELECT lower_bound FROM bounds)
  AND ClientLifeTime < (SELECT upper_bound FROM bounds)

写法2：给原窗口函数的结果加LIMIT 1

如果你更习惯窗口函数的写法，也可以在bounds的查询末尾加LIMIT 1，因为所有行的上下界都是相同的，取任意一行就行：

WITH
bounds AS (
  SELECT
    PERCENTILE_CONT(ClientLifeTime, 0.25) OVER() AS lower_bound,
    PERCENTILE_CONT(ClientLifeTime, 0.75) OVER() AS upper_bound
  FROM 
    uniqueClientsWithLifeTime
  LIMIT 1 -- 只保留一行重复的四分位数
)

SELECT
  AVG(ClientLifeTime) AS trimmed_mean
FROM 
  uniqueClientsWithLifeTime
WHERE 
  ClientLifeTime > (SELECT lower_bound FROM bounds)
  AND ClientLifeTime < (SELECT upper_bound FROM bounds)

额外简化：用JOIN替代子查询

如果你不想用子查询，也可以把bounds和原表做交叉连接（因为bounds只有一行，交叉连接不会产生冗余数据），写法更直观：

WITH
bounds AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY ClientLifeTime) AS lower_bound,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY ClientLifeTime) AS upper_bound
  FROM 
    uniqueClientsWithLifeTime
)

SELECT
  AVG(uc.ClientLifeTime) AS trimmed_mean
FROM 
  uniqueClientsWithLifeTime uc
CROSS JOIN bounds b
WHERE 
  uc.ClientLifeTime > b.lower_bound
  AND uc.ClientLifeTime < b.upper_bound

关于四分位数正确性的验证 #

你之前单独查上下界能得到值，其实是因为虽然bounds有很多行，但所有行的上下界都是正确的全表四分位数。现在调整后的写法，得到的四分位数和你之前看到的单个值是一致的，可以放心用。

备注：内容来源于stack exchange，提问作者Francisco Cortes