達人SQL1-10の「集合にきめ細かな条件を設定する」のまとめです。
HAVING句を利用してクエリを作ることで、集団の中から欲しい特徴を持った集団を自在に切り取ることができる、とのこと。
例えば、とある男女混合でチーム分けされた集団が有り、その中で身長を測定した結果をまとめたテーブルがあったとする。
| id | team | sex | height |
|---|---|---|---|
| 1 | A | M | 180 |
| 2 | A | M | 165 |
| 3 | A | F | 160 |
| 4 | A | F | 150 |
| 5 | B | M | 175 |
| 6 | B | M | 165 |
| 7 | B | F | 155 |
| 8 | B | F | 155 |
| 9 | C | M | 180 |
| 10 | C | M | 155 |
| 11 | C | F | 150 |
| 12 | C | F | 145 |
この中から「身長160cm以上の人がチームの半数以上のチーム名」を取得する場合には、次のようになる。
select team from UserData group by team having count(*) * 0.50 <= sum(case when height >= 160 then 1 else 0 end);
まず、teamでGROUP BYすることで各チームごとの部分集合を作る。
次に、having句の中でチーム毎の人数の半数と身長が160cm以上の人の数とを比較している。
このようにCASE式とsumを組み合わせることで、特定条件を満たした行数の合計を計算することができる。
同じようにして、「各チームごとに、日本人男性の平均身長（167cm）を超えている男性の人数が、日本人女性の平均身長（154cm）を超えている女性の人数よりも多いチーム」を取得することもできる。
select team from UserData group by team having sum(case when sex='M' and height >= 167 then 1 else 0 end) >= sum(case when sex='F' and height >= 154 then 1 else 0 end);
少し複雑にはなったが、基本は1つ目のクエリと同じ。
CASE式とSUMを使って求めたい条件に合致する行数を数え上げ、それを比較している。
このCASE式とSUMを使った条件については、SUM以外の集約関数を組み合わせて利用することもできる。
select team from UserData group by team having avg(case when sex='M' then height else null end) >= avg(case when sex='F' then height else null end) + 20;
このクエリでは、「チームごとの男性の平均身長が、女性の平均身長 + 20cmよりも高いチーム名」を検索している。
このように、CASE式とavgを利用して条件を作ることもできる。
（なお、CASE式のELSEの場合にnullになっているのは、case式の結果が空集合になってしまっても正常に動作させるためである。）
これでHAVING句については終わり。
HAVING句は条件として集約関数が使えて、さらにCASE式と組み合わせることで可能性が無限大に広がる。
これはきっとこれからのクエリ生活にも役に立つと思う。
