Oracle SQL中如何基于COUNT结果计算AVG？新手技术求助

首先说明为什么直接引用别名licz会报错：Oracle SQL的执行顺序是先处理FROM/JOIN，再执行GROUP BY、HAVING，最后才处理SELECT子句。也就是说，当你在同层SELECT里写avg(licz)时，licz这个别名还没被定义，系统自然识别不了。

下面给两种可行的解决方法：

方法一：使用子查询 #

把原查询的结果作为子查询，在外层对licz字段计算平均值：

SELECT nazwa, klatka_id, klatka_nazwa, licz, 
       AVG(licz) OVER () AS avg_number  -- 计算所有行的licz平均值
FROM (
    SELECT zwierzeta.nazwa, zwierzeta.klatka_id, klatki.nazwa AS klatka_nazwa, 
           COUNT(zwierzeta.klatka_id) AS licz
    FROM zwierzeta
    JOIN klatki ON zwierzeta.zwierze_id = klatki.klatka_id
    GROUP BY zwierzeta.nazwa, zwierzeta.klatka_id, klatki.nazwa
    HAVING COUNT(zwierzeta.klatka_id) >= 1
) sub;

如果需要按某个维度分组计算平均值，比如按klatka_id分组，把AVG(licz) OVER ()改成AVG(licz) OVER (PARTITION BY klatka_id)即可。

方法二：使用CTE（公共表表达式） #

CTE写法更清晰，逻辑和子查询一致：

WITH counted_data AS (
    SELECT zwierzeta.nazwa, zwierzeta.klatka_id, klatki.nazwa AS klatka_nazwa, 
           COUNT(zwierzeta.klatka_id) AS licz
    FROM zwierzeta
    JOIN klatki ON zwierzeta.zwierze_id = klatki.klatka_id
    GROUP BY zwierzeta.nazwa, zwierzeta.klatka_id, klatki.nazwa
    HAVING COUNT(zwierzeta.klatka_id) >= 1
)
SELECT nazwa, klatka_id, klatka_nazwa, licz, 
       AVG(licz) OVER () AS avg_number
FROM counted_data;

另外提一句：原查询的关联条件zwierzeta.zwierze_id = klatki.klatka_id看起来有点奇怪，一般动物表和笼子表的关联应该是动物表的笼子ID关联笼子表的ID，也就是zwierzeta.klatka_id = klatki.klatka_id，如果这是笔误的话，建议修正，否则会导致关联结果不符合预期。

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