集合の性質を調べる
これまでは、
ここでは、
サンプルに、
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句についての説明が薄くなってしまったので、 今回の特集と併せて読んでいただくと、 より一層効果的でしょう。 連載のおわりに
以上、
3つの章にわたって、 SQLの基本的なロジックを解説してきました。なるべく手続き型言語の考えにひきつけて、 SQLとの架橋を果たそうと試みてきたつもりですが、 いかがだったでしょう。 SQLは、
基本的には集合と述語という、 それ自身、 大変しっかりした数学的・ 論理的な基礎を持つ道具立てをうまく応用した言語ですが、 おそらく私たちエンジニアにとっては、 正面からこうしたなじみの薄い概念に取り組むよりも、 なるべく慣れ親しんだ手続き型の考えからSQLの集合指向に接近するほうが理解しやすいのではないか、 と考えて、 このような構成をとりました。 この連載を読んで、
SQLについて、 よりハイレベルな技術を学びたいと思った方 (あるいは全然物足りなかった方) は、 まずは拙著 『SQL徹底指南書』 を参照されることを勧めます。その後に、 『指南書』 の巻末の 「参考文献」 に挙げた書籍を紐解くことで、 より高度で興味深いデータベースの世界が開けていくことでしょう。 - GROUP BYの応用として興味深いのは、