不同于之前的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
