SQLでAVGとCOUNTはNULLを無視する
ColumnA | ColumnB |
---|---|
100 | 100 |
200 | 200 |
300 | 300 |
400 | NULL |
500 | 500 |
テーブルが上記の状態のとき、ColumnA、ColumnBに対して集合関数を使用してみると以下のような結果が出力される。
ColumnA
SUM | MAX | MIN | AVG | COUNT |
---|---|---|---|---|
1500 | 500 | 100 | 300 | 5 |
ColumnB
SUM | MAX | MIN | AVG | COUNT |
---|---|---|---|---|
1100 | 500 | 100 | 275 | 4 |
集合関数を使用する列にNULLが含まれる場合、AVGとCOUNTについてはNULLが無視された結果が出力される。 COUNTは「総レコード数-NULLレコード数」、AVGは「該当レコードの有効値の合計/(総レコード数-NULLレコード数)」なので、レコード数を基準に考えると正確な値は出力されない。
集合関数を使用する場合は、NULLを許容しない(NOT NULL)列だけで使用したり、列にデフォルト制約を使用してNULLが挿入されない状態を作るなどの前提を用意しておくべきだと思うんだけど、もし集合関数の結果にNULLのレコードを含みたい場合はCASE式が使える。
SELECT AVG(Sub.ColB), COUNT(Sub.ColB) FROM (SELECT CASE WHEN ColumnB IS NULL THEN 0 ELSE ColumnB END AS ColB FROM Table) Sub
SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2016/06/17
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2008/02/07
- メディア: 単行本(ソフトカバー)
- 購入: 54人 クリック: 1,004回
- この商品を含むブログ (78件) を見る
UPDATE文でCASE式を使う
SQLのUPDATE文で、別の列の状態から更新する列の値を設定したいときなどにはSET句にCASE式が使える。
UPDATE table SET column_A = CASE WHEN column_B = 0 THEN 'column_B is 0' WHEN column_B = 1 THEN 'column_B is 1' WHEN column_B = 2 THEN 'column_B is 2' ELSE 'column_B is other' END
SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2016/06/17
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2008/02/07
- メディア: 単行本(ソフトカバー)
- 購入: 54人 クリック: 1,004回
- この商品を含むブログ (78件) を見る