SQL多列查询时仅对单列应用Avg()聚合的实现方法

需要编写SQL查询返回以下字段：

• 项目名称
• 校区名称
• 对应项目的学费
• 该项目所属校区开设的所有项目的平均学费

涉及的数据库表结构如下：

CREATE TABLE Campus
(
    Campus_ID INTEGER NOT NULL,
    Campus_Name    varchar(24),
    Campus_Address varchar(24),
    Campus_City varchar(12)
);

CREATE TABLE Program
(
    Program_ID INTEGER NOT NULL,
    Program_Name varchar(24),
    Program_Description varchar(2000),
    Tuition_Fess numeric(7,2),
    Program_Coordinator_ID INTEGER,
    Campus_ID INTEGER
);

原有查询无法运行的核心原因有两点：

• 违反SQL的GROUP BY语法规则：SELECT子句中出现的所有非聚合字段，必须全部包含在GROUP BY的分组字段列表中。原有查询仅按Campus.Campus_Name分组，但SELECT中同时查询了Program.Program_Name、Program.Tuition_Fess两个非聚合字段，不符合语法要求。
• 存在字段拼写错误：表结构中学费字段名为Tuition_Fess，查询中错写为Tution_Fees，会触发字段不存在的报错。

从逻辑层面看，GROUP BY Campus.Campus_Name会把同一个校区的所有项目压缩成单条结果，数据库无法确定压缩后的单条记录要返回该校区下哪个具体项目的名称、哪个项目的单独学费，本身也不符合需求。

这种需要同时保留明细行数据、又要计算同分组下聚合值的场景，用窗口函数实现最简洁，不需要对结果做分组压缩，就能为每一条项目明细匹配到所属校区的平均学费：

SELECT 
    p.Program_Name,
    c.Campus_Name,
    p.Tuition_Fess AS Program_Tuition,
    AVG(p.Tuition_Fess) OVER (PARTITION BY c.Campus_ID) AS Campus_Avg_Tuition
FROM Program p
INNER JOIN Campus c
    ON p.Campus_ID = c.Campus_ID;

语法说明：

• OVER (PARTITION BY c.Campus_ID)定义窗口计算的分区规则：按校区ID拆分数据分区
• AVG()函数会针对每一行项目数据，计算它所在分区（即所属校区）下所有项目学费的平均值
• 用Campus_ID做分区字段比用Campus_Name更稳妥，可避免不同校区重名导致的统计错误

如果使用的是不支持窗口函数的老旧数据库版本，可以通过子查询先预计算每个校区的平均学费，再和明细表关联得到结果：

SELECT 
    p.Program_Name,
    c.Campus_Name,
    p.Tuition_Fess AS Program_Tuition,
    avg_calc.Campus_Avg_Tuition
FROM Program p
INNER JOIN Campus c
    ON p.Campus_ID = c.Campus_ID
INNER JOIN (
    SELECT Campus_ID, AVG(Tuition_Fess) AS Campus_Avg_Tuition
    FROM Program
    GROUP BY Campus_ID
) avg_calc
    ON c.Campus_ID = avg_calc.Campus_ID;

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