如何在GROUP BY场景下实现嵌套聚合查询（无需OVER子句）

解决方案：拆分聚合计算，避免嵌套 #

原查询报错是因为SQL不支持聚合函数嵌套（sum()里套mean()）。要不用OVER子句实现需求，可以先通过子查询提前算出每个分组的均值，再关联原表计算分子分母的总和：

SELECT 
    t1.id, 
    t1.id2, 
    SUM((t1.v - t2.mean_v) * t1.v2) / SUM(ABS(t1.v - t2.mean_v)) AS res
FROM df t1
JOIN (
    -- 先计算每个(id, id2)分组的v的均值
    SELECT 
        id, 
        id2, 
        SUM(v)::FLOAT / COUNT(v) AS mean_v
    FROM df
    GROUP BY id, id2
) t2 ON t1.id = t2.id AND t1.id2 = t2.id2
GROUP BY t1.id, t1.id2, t2.mean_v

原理说明 #

1. 子查询先对每个(id, id2)分组计算v的均值mean_v，这是一层独立的聚合，不存在嵌套问题。
2. 将原表与子查询结果关联，让每一行数据都能获取到所属分组的均值。
3. 最后再次按(id, id2)分组，分别计算分子（(v-均值)*v2的总和）和分母（|v-均值|的总和），再做除法得到最终结果。

验证结果 #

执行上述SQL后，会得到预期的结果：

id | id2 | res
----|-----|-----
  1 |   1 |  -1
  2 |   1 |  -1
  3 |   2 |  -1

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