按班级统计成绩分布：构建查询实现成绩计数与占比计算

现有一张跟踪学生课程表现的数据表student_course_performance，表结构及数据如下：

其中facultyID、academicYear、courseID三个字段共同构成一个“班级”，grade字段的取值范围为A、B、C、D、E。需要构建SQL查询，统计每个班级中各成绩的出现次数（gradeCount），以及该成绩在班级总成绩中的占比（percentage），要求即使成绩未出现也要显示次数为0、占比为0，期望结果如下：

解决方案（以MySQL为例） #

-- 生成所有可能的成绩选项
WITH all_grades AS (
    SELECT 'A' AS grade UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'D' UNION ALL
    SELECT 'E'
),
-- 获取所有唯一的班级
all_classes AS (
    SELECT DISTINCT facultyID, academicYear, courseID
    FROM student_course_performance
),
-- 计算每个班级的总人数
class_totals AS (
    SELECT facultyID, academicYear, courseID, COUNT(*) AS total_students
    FROM student_course_performance
    GROUP BY facultyID, academicYear, courseID
)
-- 交叉连接班级和成绩，左连接原表统计次数，计算占比
SELECT
    ac.facultyID,
    ac.academicYear,
    ac.courseID,
    ag.grade,
    COUNT(scp.studentID) AS gradeCount,
    ROUND(COUNT(scp.studentID) * 100.0 / ct.total_students, 3) AS percentage
FROM all_classes ac
CROSS JOIN all_grades ag
LEFT JOIN class_totals ct ON ac.facultyID = ct.facultyID 
                         AND ac.academicYear = ct.academicYear 
                         AND ac.courseID = ct.courseID
LEFT JOIN student_course_performance scp ON ac.facultyID = scp.facultyID 
                                        AND ac.academicYear = scp.academicYear 
                                        AND ac.courseID = scp.courseID
                                        AND ag.grade = scp.grade
GROUP BY ac.facultyID, ac.academicYear, ac.courseID, ag.grade, ct.total_students
ORDER BY ac.facultyID, ac.academicYear, ac.courseID, ag.grade;

思路说明 #

1. 生成全量成绩列表：通过CTEall_grades枚举所有可能的成绩值，确保未出现的成绩也能被统计。
2. 提取唯一班级：通过all_classes获取表中所有班级组合，避免遗漏任何班级。
3. 计算班级总人数：class_totals统计每个班级的学生总数，作为占比计算的分母。
4. 交叉连接与左连接：将班级和成绩交叉生成全量组合，再左连接原数据表统计对应成绩的人数，左连接保证无匹配数据时计数为0。
5. 占比计算：用成绩计数除以班级总人数，乘以100后保留3位小数得到百分比。

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