子查询逻辑返回结果不符问题排查及正确SQL解法解析

问题背景 #

执行自定义SQL子查询后得到结果4434.21，与预期结果4721.14不符。需求为：计算仅包含平均每单消费高于所有订单平均消费的公司的total_amt_usd生命周期平均值。

我的SQL代码 #

WITH t1 AS (SELECT AVG(o.total_amt_usd) total_avg
FROM orders o),

t2 AS(SELECT a.name account_name,
       a.id account_id,
       AVG(o.total_amt_usd) account_avg
FROM accounts a
JOIN orders o
  ON a.id = o.account_id
GROUP BY 1,2
HAVING AVG(o.total_amt_usd) > (SELECT * 
                              FROM t1)
)

SELECT AVG(o.total_amt_usd) 
FROM orders o
JOIN t2 
  ON t2.account_id = o.account_id

正确解法SQL #

SELECT AVG(avg_amt)
FROM (
    SELECT o.account_id,
           AVG(o.total_amt_usd) avg_amt
    FROM orders o 
        GROUP BY 1
        HAVING AVG(o.total_amt_usd) > 
        (SELECT AVG(o.total_amt_usd) avg_all
        FROM orders o)
) t1

核心差异与错误原因 #

• 你的SQL最后一步是对符合条件的公司的所有订单金额直接取整体平均值，相当于把这些公司的每一笔订单金额混在一起计算平均，这不是需求要的「生命周期平均值」。
• 正确逻辑是：先筛选出平均每单消费高于全局平均的公司，计算每个公司自己的平均每单金额，再对这些公司的平均值求平均——这才是需求要求的「生命周期平均值」，也就是每个合格公司的平均消费值的再平均，而非所有订单的直接平均。

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