SQLの授業でCASE式のELSEで0を返していい時とNULLでないといけない場合の話がわかりにくくて、同級生の天才くんに教えて貰ってなんとかわかったと思うので、せっかくだからメモしておく。
CASE式で表のデータを集約関数で計算するすべての場合に当てはまることだけど、行列入れ替えないならGROUP BYでやるからわざわざCASE式にしないから、行列入れ替えのときに出てきがちなことだと思います。
CASE式で行列を入れ替える時の例
分類毎の商品と単価の表があって、そこから分類毎に合計を出すと、こうなる。
| bunrui | 分類毎の商品単価の合計 |
|---|---|
| 衣服 | （略） |
| キッチン用品 | （略） |
| 事務用品 | （略） |
これを、CASE式を使ったSELECT文で行列を入れ替えるとこうなる。⇧これを行列入れ替えたんじゃなく、元テーブルから合計（SUM）を計算しつつ行列を入れ替えてます。
| bunrui | 衣服 | キッチン用品 | 事務用品 |
|---|---|---|---|
| 分類毎の商品単価の合計 | （略） | （略） | （略） |
SELECT SUM(CASE WHEN bunrui='衣服' THEN tanka ELSE 0 END)AS sum_tanka_ifuku,
SUM(CASE WHEN bunrui='キッチン用品' THEN tanka ELSE 0 END)AS sum_tanka_kitchen,
SUM(CASE WHEN bunrui='事務用品' THEN tanka ELSE 0 END)AS sum_tanka_jimu
FROM Shohin;
SUMならこれでいい、他の集約関数は？
上のコード（SQLだと”リスト”っていうのかな）のSUMを、AVG,COUNT,MAX,MINに単純に変えればいいわけじゃなかったのですよ。ELSEで0を返してその行を終わらせるかNULLを返すかで、正しさが変わってきてしまう。先に正解を書く。
--AVGのときELSE NULL
SELECT AVG(CASE WHEN bunrui='衣服' THEN tanka ELSE NULL END)AS avg_tanka_ifuku,
AVG(CASE WHEN bunrui='キッチン用品' THEN tanka ELSE NULL END)AS avg_tanka_kitchen,
AVG(CASE WHEN bunrui='事務用品' THEN tanka ELSE NULL END)AS avg_tanka_jimu
FROM Shohin;
--MAXのときはELSE 0でも良い
SELECT MAX(CASE WHEN bunrui='衣服' THEN tanka ELSE 0 END)AS max_tanka_ifuku,
MAX(CASE WHEN bunrui='キッチン用品' THEN tanka ELSE 0 END)AS max_tanka_kitchen,
MAX(CASE WHEN bunrui='事務用品' THEN tanka ELSE 0 END)AS max_tanka_jimu
FROM Shohin;
--MINのときもELSE NULL
SELECT MIN(CASE WHEN bunrui='衣服' THEN tanka ELSE NULL END)AS min_tanka_ifuku,
MIN(CASE WHEN bunrui='キッチン用品' THEN tanka ELSE NULL END)AS min_tanka_kitchen,
MIN(CASE WHEN bunrui='事務用品' THEN tanka ELSE NULL END)AS min_tanka_jimu
FROM Shohin;
--COUNTのときもELSE NULL
(リストは省略)
なんでだと思いますか
（以下の説明は、一行目の衣服を例にとってます。）
AVGのときはNULL
AVGは、データ数の合計を、データ数を分母として割ったもの。
0を返してしまうと、0の入ったデータとして、合計は増えないのに分母だけ増えてしまう。それも、衣服じゃないデータで0を返すから、分母が全データ数になってしまう。
衣服じゃない時にはNULLを返す、ってすれば、衣服の分だけのAVGがとれる。衣服じゃなかった行の時にはNULLが返り、NULLは計算に入らないので分子はもちろん、分母を増やすこともない。
MINのときもNULL
MINのとき、ELSE 0だと、そのデータが衣服じゃなかったら0が返される。
他の分類も同じく、その分類以外のデータのせいで、0が返される。
そうすると、最小値は、その分類も、0になってしまう。
NULLにしておけば、何も返されないので、ちゃんと分類毎の最小値が出せる。
COUNTのときもNULL
COUNTの時、0が返ると、0であっても値は値なので、データの数としてカウントしてしまう。NULLにしてデータの数としてカウントしないようにする必要がある。
（または、COUNTでなくSUMにして、THENで1を返させて、それを合計させてCOUNT的に使う場合には、データの数を数えてるわけじゃないので0を返させてOK）
