不同于之前的AVG函數(shù)的使用（只對單列求平均值），今天的分享是如何用SQL計算報表中多列字段的平均值。
假設(shè)某在線教育平臺需要統(tǒng)計學生的平均成績，但存在以下復(fù)雜情況：學生可能缺考某些科目（成績?yōu)榭眨煌瑢W生參加考試的科目數(shù)量不同。問題：求每個學生的平均分數(shù)，其中平均分數(shù) = 總分/實際參加考試科目數(shù)，而非固定除以總科目數(shù)。案例報表如下，建表語句見文末：
?假如每個列都沒有空值的話，那么上面報表中5列的平均值等于(math_score+chinese_score+english_score+physics_score+chemistry_score)/5，那要統(tǒng)計每行非空列的數(shù)量就不是固定的5列了。要解決上面的問題，就需要統(tǒng)計一下三個指標數(shù)量：
非空列分數(shù)之和：將 NULL 轉(zhuǎn)換為 0 后累加
非空列數(shù)量：將 NULL 轉(zhuǎn)換為 0 ，非NULL轉(zhuǎn)換成1后累加
平均分數(shù)：非空列分數(shù)之和/非空列數(shù)量，注意要避免分母為0導致報錯
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;
大多數(shù)數(shù)據(jù)庫支持CASE表達式和COALESCE函數(shù)，所以基本上可以選擇上述的SQL代碼來解決問題。
部分數(shù)據(jù)庫支持IF、NULLIF函數(shù)等，也可以用這些函數(shù)來簡化上述代碼。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
