📝

SQLでNULLを0や他の値としてAVGやSUMを計算したい

2022/09/12に公開約900字

結論

IFNULL()COALESCE()を使います。

説明

次のようなtest_scoreテーブルがあったとします。

id score1
1 10
2 20
3 30
4 NULL

「score1の平均を取りたい」時、単純にscore1に対してAVG()関数を使うと、結果はNULLであるid=4のユーザを除いた平均値である 20 となります。

SELECT AVG(score1) FROM test_score; # 結果は20

ただし、要件によっては「NULLのユーザを0として平均値を出したい」ということがあると思います。
その場合、以下のようにすると期待する結果を得られます。

# 方法1
SELECT AVG(IFNULL(score1, 0)) FROM test_score; # 結果は15
# 方法2
SELECT AVG(COALESCE(score1, 0)) FROM test_score; # 結果は15

ここで、COALESCE()は「与えられた引数のうち、NULLでない最初の引数を返す関数」です。
したがって、例えば「試験が受けられなかったユーザ(score1がNULL)には再試験を受けさせ、その結果をscore2のカラムに格納する。再試験を受けた者は再試験の点数をその人の点数として全体の平均値を出したい」といった要件の時に便利です。

id score1 score2
1 10 NULL
2 20 NULL
3 30 NULL
4 NULL 20
SELECT AVG(COALESCE(score1, score2, 0)) FROM test_score; # 結果は20

上記はAVGだけではなく、SUMに関しても同様のことが言えます。

MySQLのリファレンス

https://dev.mysql.com/doc/refman/5.6/ja/comparison-operators.html

Discussion

ログインするとコメントできます