とある日の発見
とある日調べものをしていて見つけた記事1にあったSQLがとても気になった。
select subID ,sum((sort=1)*main) as main1 ,sum((sort=2)*main) as main2 ,sum((sort=3)*main) as main3 from sub2 group by subID
このSQLのsum((sort=1)*main)ってなに？
SUM(CASE WHEN sort = 1 THEN sort * main ELSE null END)
みたいなSUM関数の中にCASE式を展開するのは見たことがあったが、
こんな簡略化？したみたいなやり方は初めて見た。
どういう挙動をするのか調べてみた。
SUM関数の挙動
そもそも、sort=1って何を返しているのかを見てみた。
SELECT subID, SUM(sort = 2) FROM sub2 WHERE sort = 2 GROUP BY subID ;
このSQLを実行してみた。 SUM関数の結果はすべての行が１だった。
ますますわからなくなった。
ってことで、Google先生で調べたら、それらしいものを見つけた。
select count(*), count(flag = 1 or null), count(flag = 0 or null) from a;
(flag = 1 or null) の理屈
flag = 1 のとき → true or null → true flag = 0 のとき → false or null → null
なので、count(flag = 1 or null) で flag = 1 の件数がカウントできる。
らしい。
ってことは、SUM関数で１が返ってきたのはTrueってこと？
仮説
ならばと思い、条件がFalseになるSQLを組み立てた。
SELECT subID, SUM(sort = 2) FROM sub2 WHERE sort = 3 GROUP BY subID ;
WHERE句を変えてみた。 結果は、すべての行に関して0を返していた。
真相
sort = 1 のところで行を絞っていると思っていたが、 そうではなくすべての行に関して結果を返していた。
なので、SUM関数で排除しているのはあくまで(sort = 1) * mainの結果だけだということ。
Falseとなった行は0が返り*mainの演算して0となる。
| subID | main | sort |
|---|---|---|
| 1 | 3 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 2 |
| 4 | 1 | 2 |
| 5 | 3 | 3 |
| 6 | 0 | 3 |
例) subID = 1の場合 sum((sort=1)*main) sum((1=1)*3)→sum(1*3)→3 sum((sort=2)*main) sum((1=2)*3)→sum(0*3)→0 sum((sort=3)*main) sum((1=3)*3)→sum(0*3)→0
おもしろい発見だった。 新発見でとても勉強になった。
