PostgreSQL中按州和学年计算各学校年级入学占比平均值

我需要按州（St_A）和学年（School_year），计算各学校各年级入学人数占该校总入学人数的百分比的平均值。以下是Enrollment表的创建、插入语句，以及我尝试的查询语句，但系统报错提示无法在窗口函数上使用AVG，求改写方法。

表结构与测试数据 #

CREATE TABLE Enrollment (
    School_ID int, 
    District_ID int, 
    School_year text, 
    St_A text,
    Grade_level text, 
    Enrollment int
);
        
INSERT INTO Enrollment VALUES
    (1, 100, 'SY22_23','NY','Gr2',56),
    (2, 200,'SY22_23','CA','Gr4', 78),
    (3, 300,'SY22_23','CO', 'Gr1', 102),
    (4, 400, 'SY22_23','TX', 'Gr4', 202),
    (5, 500, 'SY24_25','DC','Gr3', 105),
    (6, 600,'SY24_25','CA','Gr4', 69),
    (7, 700,'SY24_25','AZ', 'Gr2', 91),
    (8, 800, 'SY24_25','TX', 'Gr3', 98),
    (9, 900, 'SY23_24','DC','Gr4',56),
    (10, 1000,'SY23_24','DC','Gr1', 60),
    (11, 1100,'SY23_24','TX', 'Gr3',65),
    (12, 1200, 'SY23_24','NY', 'Gr2', 70),
    (1, 100, 'SY22_23','NY','Gr3',56),
    (1, 200,'SY22_23','CA','Gr2', 78),
    (3, 300,'SY22_23','CO', 'Gr4', 102),
    (4, 400, 'SY22_23','TX', 'Gr3', 202),
    (5, 500, 'SY24_25','DC','Gr2', 105),
    (6, 600,'SY24_25','CA','Gr4', 69),
    (7, 700,'SY24_25','AZ', 'Gr3', 91),
    (8, 800, 'SY24_25','TX', 'Gr1', 98),
    (9, 900, 'SY23_24','DC','Gr1',56),
    (10, 1000,'SY23_24','DC','Gr2', 60),
    (11, 1100,'SY23_24','TX', 'Gr3',65),
    (12, 1200, 'SY23_24','NY', 'Gr4', 70);

原错误查询语句 #

SELECT School_Year, St_A, Enrollment, School_ID, Grade_level,
AVG(cast(Enrollment as float)/(SUM(Enrollment) OVER (Partition by School_ID))) AS AVG_percent,
From Enrollment
GROUP BY Grade_level
ORDER BY St_A, School_year

解决思路与正确查询语句 #

问题分析 #

原查询的核心错误是混合使用聚合函数与窗口函数的逻辑冲突：你试图直接对窗口函数的结果用AVG聚合，同时GROUP BY Grade_level的分组逻辑完全不符合需求——我们需要先计算每个学校各年级的占比，再按州、学年、年级维度对这些占比求平均值。

正确SQL语句 #

-- 分步计算：先得学校各年级占比，再按州+学年+年级求平均占比
SELECT
    school_year,
    st_a,
    grade_level,
    ROUND(AVG(grade_enroll_percent) * 100, 2) AS avg_grade_percent -- 转成百分比格式，保留2位小数
FROM (
    -- 子查询：计算单所学校内，各年级入学人数占该校总人数的比例
    SELECT
        school_year,
        st_a,
        school_id,
        grade_level,
        CAST(enrollment AS FLOAT) / SUM(enrollment) OVER (PARTITION BY school_id) AS grade_enroll_percent
    FROM enrollment
) AS school_grade_ratios
GROUP BY school_year, st_a, grade_level
ORDER BY st_a, school_year, grade_level;

逻辑说明 #

1. 子查询school_grade_ratios：
   • 用窗口函数SUM(enrollment) OVER (PARTITION BY school_id)计算每所学校的总入学人数
   • 用当前年级的入学人数除以该校总人数，得到该年级在本校的占比grade_enroll_percent
2. 外层查询：
   • 按school_year（学年）、st_a（州）、grade_level（年级）分组
   • 对每个分组内的所有学校年级占比求平均值，最终得到该州该学年该年级的平均入学占比
   • 用ROUND(..., 2)将结果转为百分比格式，更易读

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