Oracle SQL使用子查询计算AVG()时返回值与原字段相同问题排查

• 你当前使用的是普通聚合函数+GROUP BY语法，这种语法会按照你指定的a,b,c,d,e,f维度对数据分组，每组仅返回一行聚合结果。但你同时返回了x.*（包含每行唯一的columnz），相当于实际分组粒度到了单行级别，AVG函数计算的就是单行自身的平均值，自然和columnz取值完全一致。
• 如果你需要保留所有原始行数据的同时，新增一列全局/分组的columnz平均值，应该使用窗口函数（Oracle分析函数），而非普通GROUP BY聚合。

如果你需要计算所有行的全局columnz平均值，修改后的SQL如下：

SELECT 
    a,
    b,
    c,
    d,
    e,
    f,
    columnx,
    columny,
    columnz,
    AVG(columnz) OVER () AS avg_columnz
FROM (
    SELECT a,b,c,d,e,f,columnx,columny,columnx-columny AS columnz 
    FROM abc
) x;

如果你需要按a,b,c,d,e,f分组，计算每个分组内的平均columnz，仅需要给窗口函数添加PARTITION BY子句即可：

SELECT 
    a,
    b,
    c,
    d,
    e,
    f,
    columnx,
    columny,
    columnz,
    AVG(columnz) OVER (PARTITION BY a,b,c,d,e,f) AS avg_columnz
FROM (
    SELECT a,b,c,d,e,f,columnx,columny,columnx-columny AS columnz 
    FROM abc
) x;

按上述写法修改后，你提供的样例数据的avg_columnz会统一返回(6+3+2+5+1)/5 = 3.4，符合预期的平均值计算效果。

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