不同于之前的AVG函数的使用（只对单列求平均值），今天的分享是如何用SQL计算报表中多列字段的平均值。
假设某在线教育平台需要统计学生的平均成绩，但存在以下复杂情况：学生可能缺考某些科目（成绩为空），不同学生参加考试的科目数量不同。问题：求每个学生的平均分数，其中平均分数 = 总分/实际参加考试科目数，而非固定除以总科目数。案例报表如下，建表语句见文末：
假如每个列都没有空值的话，那么上面报表中5列的平均值等于(math_score+chinese_score+english_score+physics_score+chemistry_score)/5，那要统计每行非空列的数量就不是固定的5列了。要解决上面的问题，就需要统计一下三个指标数量：
SQL如下：
SELECT student_name, (COALESCE(math_score, 0) + COALESCE(chinese_score, 0) + COALESCE(english_score, 0) + COALESCE(physics_score, 0) + COALESCE(chemistry_score, 0)) AS total_score,
(CASE WHEN math_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN chinese_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN english_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN physics_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN chemistry_score IS NOT NULL THEN 1 ELSE 0 END) AS valid_columns,
CASE WHEN (CASE WHEN math_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN chinese_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN english_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN physics_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN chemistry_score IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN NULL ELSE (COALESCE(math_score, 0) + COALESCE(chinese_score, 0) + COALESCE(english_score, 0) + COALESCE(physics_score, 0) + COALESCE(chemistry_score, 0)) / (CASE WHEN math_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN chinese_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN english_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN physics_score IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN chemistry_score IS NOT NULL THEN 1 ELSE 0 END) END AS avg_scoreFROM data_learning.student_scores;
大多数数据库支持CASE表达式和COALESCE函数，所以基本上可以选择上述的SQL代码来解决问题。
部分数据库支持IF、NULLIF函数等，也可以用这些函数来简化上述代码。SELECT student_name, (COALESCE(math_score, 0) + COALESCE(chinese_score, 0) + COALESCE(english_score, 0) + COALESCE(physics_score, 0) + COALESCE(chemistry_score, 0)) AS total_score,
(IF(math_score IS NOT NULL , 1 , 0 ) + IF(chinese_score IS NOT NULL , 1 , 0 ) + IF(english_score IS NOT NULL , 1 , 0 ) + IF(physics_score IS NOT NULL , 1 , 0 ) + IF(chemistry_score IS NOT NULL , 1 , 0 )) AS valid_columns,
CASE WHEN (IF(math_score IS NOT NULL , 1 , 0 ) + IF(chinese_score IS NOT NULL , 1 , 0 ) + IF(english_score IS NOT NULL , 1 , 0 ) + IF(physics_score IS NOT NULL , 1 , 0 ) + IF(chemistry_score IS NOT NULL , 1 , 0 )) = 0 THEN NULL ELSE (COALESCE(math_score, 0) + COALESCE(chinese_score, 0) + COALESCE(english_score, 0) + COALESCE(physics_score, 0) + COALESCE(chemistry_score, 0)) / (IF(math_score IS NOT NULL , 1 , 0 ) + IF(chinese_score IS NOT NULL , 1 , 0 ) + IF(english_score IS NOT NULL , 1 , 0 ) + IF(physics_score IS NOT NULL , 1 , 0 ) + IF(chemistry_score IS NOT NULL , 1 , 0 ))
END AS avg_scoreFROM data_learning.student_scores;
create table data_learning.student_scores( student_name varchar(255), math_score INT, chinese_score INT, english_score INT, physics_score INT, chemistry_score INT);
insert into data_learning.student_scores values('林赛', 90, 85, NULL, 78, 92), ('张三', NULL, NULL, NULL, NULL, NULL), ('李四', 75, 88, 92, 85, 80), ('王五', 80, NULL, 90, 80, 70);
该文章在 2025/3/25 9:47:04 编辑过
400 186 1886
