「達人に学ぶ SQL徹底指南書」の1-4を読み終わった。
この章のタイトルは「HAVING句の力」となっていて、HAVING句を利用して集合の状態を調べるということを実践している。
ここでは、本の中に出てきた「歯抜けの有無の確認」「最頻値を求める」「中央値を求める」についてまとめておく。
連番になっているテーブルの歯抜けを探す。
以下のようにidが連続して続いているようなテーブルの中で、歯抜けになっているところがあるかの検証にhaving句が利用できる
| id | name |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 6 | F |
| 8 | H |
たしかにこのテーブルの中では、id=5, 7が抜けていてしまっている。
このテーブルについて、歯抜けのデータが有るかどうかを調べるときはhaving句を使って以下のようにすれば良い。
SELECT 'has gap!' AS check FROM gap HAVING count(*) <> max(id);
| "check" |
|---|
| has gap! |
having句の中で集約関数同士の結果を比較している。
本来はidが連番になっていることを利用して、その最大値と実際に登録されている個数を比べることで連番になっているか調べることができる。
このように、集約関数の結果はhaving句内で比較することが可能である。
繰り返すが、同じようなする場合は対象となる列がそもそも連番になっていることが前提になっている点が重要ある。
最頻値を求める
統計でも代表値として用いられる最頻値も、having句を利用することで可能になる。
次のようなテーブルの最頻値を求めてみる。
| name | score |
|---|---|
| A | 100 |
| B | 80 |
| C | 80 |
| D | 80 |
| E | 65 |
| F | 30 |
| G | 30 |
| H | 30 |
| I | 20 |
| J | 20 |
SELECT score, count(*) AS cnt FROM test_result GROUP BY score HAVING count(*) >= ALL ( SELECT count(*) FROM test_result GROUP BY score );
| score | cnt |
|---|---|
| 80 | 3 |
| 30 | 3 |
これについては、まずhaving句の中のサブクエリによって各score毎に値が何個ずつあるかが計算される。
次に、ALLを使ってこれらの個数以上登録されているscoreを見つけ出す。
自分自身を集計した結果を再度自分自身を集計するために利用する、再帰的に検索を行っているところがポイント。
これと同じことは極値関数を利用してもできる。
SELECT score, count(*) AS cnt FROM test_result GROUP BY score HAVING count(*) >= ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM test_result GROUP BY score ) tmp );
メジアン（中央値）を求める
同じテーブルを用いて、中央値を計算してみる。
SELECT avg(DISTINCT score) FROM ( SELECT t1.score FROM test_result t1, test_result t2 GROUP BY t1.score HAVING sum(CASE WHEN t2.score >= t1.score THEN 1 ELSE 0 END) >= count(*) / 2 AND sum(CASE WHEN t2.score <= t1.score THEN 1 ELSE 0 END) >= count(*) / 2 ) tmp;
| avg |
|---|
| 47.5 |
やってることは、テーブル全体の中から値の上位半数と下位半数を取り出してきて、共通部分を中央値を求める。
また、今回のようにテーブルに含まれる行数が偶数の場合、取り出される値が2つになるので最終的に平均を取ったものを中央値にしている。
本の中では、他に「NULLを含まない集合を探す」「関係除算」について説明されていたが、これらについては後日まとめようと思う。（覚えていれば。）
