SQLアタマ養成講座
第11回 SQL流集合操作(4) 集合の性質を調べる
集合の性質を調べる
これまでは,
ここでは,
サンプルに,
表7 階級の振り分け
name | age | age_ | Height | weight |
---|---|---|---|---|
Anderson | 30 | 成人 | 188 | 90 |
Adela | 21 | 成人 | 167 | 55 |
Bates | 87 | 老人 | 158 | 48 |
Becky | 54 | 成人 | 187 | 70 |
Bill | 39 | 成人 | 177 | 120 |
Chris | 90 | 老人 | 175 | 48 |
Darwin | 12 | 子供 | 160 | 55 |
Dawson | 25 | 成人 | 182 | 90 |
Donald | 30 | 成人 | 176 | 53 |
では,
まず,
リスト11 集合の性質を調べるためのクエリ
SELECT CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'
ELSE NULL END AS age_class,
COUNT(*)
FROM Persons
GROUP BY CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'
ELSE NULL END
HAVING COUNT(*) = SUM(CASE WHEN weight / POWER(height /100, 2) < 25 THEN 1
ELSE 0 END);
図13 リスト11の実行結果
age_class count(*) --------- ---------- 老人 2 子供 1
ここで重要な役割を果たすトリックは,
もしもっと直截に
リスト12 集合の性質を調べるためのクエリ
SELECT CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'
ELSE NULL END AS age_class,
COUNT(*) AS all_cnt,
SUM(CASE WHEN weight / POWER(height /100, 2) < 25 THEN 1
ELSE 0 END) AS not_fat_cnt →「肥満」以外の人数を数える式
FROM Persons
GROUP BY CASE WHEN age < 20 THEN '子供'
WHEN age BETWEEN 21 AND 69 THEN '成人'
WHEN age > 70 THEN '老人'
ELSE NULL END;
図14 リスト12の実行結果
age_class all_ cnt not_ fat_ cnt ---------- ---------- ----------- 成人 6 3 老人 2 2 子供 1 1
こうすることで,
おわりに
SQLの集合操作の勘所を学ぶために,
- SQLの基本的な処理単位は,
レコード (行) ではなく 「レコードの集合」 - GROUP BY句は元のテーブルを小さな部分集合
(類) に切り分ける 「カット」 の機能を持つ - GROUP BY句はさらに,
カットした集合単位に行をまとめる 「集約」 の機能も持つ - GROUP BY句から集約の機能を取り去って,
カットだけを残したのがPARTITION BY句 - どちらの句も,
列名に限らず 「式」 を引数に取れる柔軟さが強み。これによって, SQLは集合をどんな複雑な基準によってでも切り刻み, 料理することが可能となっている
GROUP BYやPARTITION BYは,
本章の内容について,
- 『SQLパズル 第2版』
(J.セルコ 著, 翔泳社, 2007) - GROUP BYの応用として興味深いのは,
「カット」 の機能をうまく利用した 「パズル29 最頻値を求める」 や入れ子の再帰集合を作って累計を求める 「パズル35 在庫調整」。HAVING句については, 特性関数を駆使する 「パズル11 作業依頼」 や集合指向の極致と言うべき芸術的な欠番探索の方法を紹介する 「パズル57 欠番探し――バージョン1」 など。 - 『達人に学ぶ SQL徹底指南書』
(ミック, 翔泳社, 2008) - 本書ではHAVING句をかなり重点的に取り上げました。
「1-4 HAVING句の力」 や 「1-10 帰ってきたHAVING句」 を参照。一方, HAVING句の復権に力を入れるあまり, GROUP BY句についての説明が薄くなってしまったので, 今回の特集と併せて読んでいただくと, より一層効果的でしょう。 - 注6)
- HAVING句については,
『指南書』 の中でかなり詳しく取り上げています。先に知りたい人は 「1-4.HAVING句の力」 や 「1-10.帰ってきたHAVING句」 を参照。
連載のおわりに
以上,
3つの章にわたって, SQLの基本的なロジックを解説してきました。なるべく手続き型言語の考えにひきつけて, SQLとの架橋を果たそうと試みてきたつもりですが, いかがだったでしょう。 SQLは,
基本的には集合と述語という, それ自身, 大変しっかりした数学的・ 論理的な基礎を持つ道具立てをうまく応用した言語ですが, おそらく私たちエンジニアにとっては, 正面からこうしたなじみの薄い概念に取り組むよりも, なるべく慣れ親しんだ手続き型の考えからSQLの集合指向に接近するほうが理解しやすいのではないか, と考えて, このような構成をとりました。 この連載を読んで,
SQLについて, よりハイレベルな技術を学びたいと思った方 (あるいは全然物足りなかった方) は, まずは拙著 『SQL徹底指南書』 を参照されることを勧めます。その後に, 『指南書』 の巻末の 「参考文献」 に挙げた書籍を紐解くことで, より高度で興味深いデータベースの世界が開けていくことでしょう。 - GROUP BYの応用として興味深いのは,