はじめに
最近『達人に学ぶ SQL 徹底指南書 第 2 版』を読みました。
この本を通して SQL には SQL の「考え方」がある ことが分かりました。
SQL は「宣言型」であり「集合指向」の言語のため、普段使い慣れた手続き型言語とは発想の仕方が異なります。
ここでは、その SQL 的な考え方 を整理します。
本題に入る前に...
- 手続き型言語 : どう処理するかを手順で書く（ex. C, Java, Python）
- 宣言型言語 : 欲しい結果だけを書く（ex. SQL, Haskell, Lisp）
SQL は「どう計算するか」ではなく「どんなデータが欲しいか」を書くスタイルです。
1. SQL を支える理論
SQL を支えているのは大きくこの 2 つです。
- 集合論（テーブルは集合だよ、という考え方）
- 述語論理（条件を「存在する／存在しない」で表現する考え方）
1.1 集合論
1.1.1 テーブルは集合
RDB（リレーショナルデータベース）は「関係モデル」という集合論の発想を採用しています。
つまり テーブルは「行の集まり＝集合」 として考えられます。
ちょっと難しいですが、関係（=テーブル）の定義からも集合であることがわかります 🤓
関係の定義
関係: $R$ , 属性（列）: $A_i$ , その定義域: $D_i$
$$R \subseteq D_1 \times D_2 \times D_3 \times \cdots \times D_n$$
自己結合
テーブルが集合であることを実感しやすいのが 自己結合 です。
同じテーブルを「別の集合っぽく」扱って比較します。
ex. 同じ値段の商品を探す
Products テーブル
| name | price |
|---|---|
| りんご | 50 |
| みかん | 100 |
| ぶどう | 50 |
| スイカ | 80 |
| レモン | 30 |
| いちご | 100 |
| バナナ | 100 |
SELECT DISTINCT P1.name, P1.price
FROM
Products P1
INNER JOIN Products P2
ON P1.price = P2.price
AND P1.name <> P2.price
ORDER BY P1.price;
結果
| name | price |
|---|---|
| りんご | 50 |
| ぶどう | 50 |
| いちご | 100 |
| みかん | 100 |
| バナナ | 100 |
手続き型言語なら「for 文で 1 行ずつ比較」しますが、SQL なら 「条件を宣言」するだけ で OK です。
1.1.2 集約（GROUP BY / HAVING）
HAVING 句は 「部分集合に条件をかける」 という集合的な発想です。
ex. 学生が全員レポート提出済みの学部を探す
Students テーブル
| student_id | dpt | sbmt_date |
|---|---|---|
| 100 | 理学部 | 2018-10-10 |
| 101 | 理学部 | 2018-9-22 |
| 102 | 文学部 | |
| 103 | 文学部 | 2018-9-10 |
| 200 | 文学部 | 2018-9-22 |
| 201 | 工学部 | |
| 202 | 経済学部 | 2018-9-25 |
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);
結果
| dpt |
|---|
| 理学部 |
| 経済学部 |
HAVING で「提出済みの人数」と「その学部の総人数」を比較しています。
集合にまとめて条件をかけてるのがポイントです。
1.2 述語論理
述語論理は 「条件をつけて対象をしぼり込む考え方」 です。
その中でも SQL に関係するのは「量化子」と呼ばれるものです。
- 存在量化子（∃）：「～するものが存在する」
- 全称量化子（∀）：「すべてのものが～する」
SQL の世界では、この量化子が EXISTS で表現されます。
1.2.1 存在量化
「この条件を満たす行がひとつでもあれば OK」というのが EXISTS です。
サブクエリの結果が 1 行でも返ってきたら真（TRUE）になります。
イメージ
∃x P(x) ⇔ EXISTS (サブクエリ)
ex. 会議に参加していない人を探す
Meetings テーブル
| meeting | person |
|---|---|
| 第 1 回 | 伊藤 |
| 第 1 回 | 水嶋 |
| 第 1 回 | 坂東 |
| 第 2 回 | 伊藤 |
| 第 2 回 | 宮田 |
| 第 3 回 | 坂東 |
| 第 3 回 | 水嶋 |
| 第 3 回 | 宮田 |
SELECT DISTINCT
M1.meeting,
M2.person
FROM
Meetings M1
CROSS JOIN Meetings M2
WHERE
NOT EXISTS(
SELECT *
FROM Meetings M3
WHERE
M1.meeting = M3.meeting
AND M2.person = M3.person
);
結果
| meeting | person |
|---|---|
| 第 1 回 | 坂東 |
| 第 2 回 | 伊藤 |
| 第 2 回 | 宮田 |
| 第 3 回 | 坂東 |
やっていることは次の通りです 👇
- すべての会議 × すべての人の組み合わせを作る
- その会議にその人が「存在しない」場合だけ残す
つまり NOT EXISTS を使って「存在しない」を表現することで、「参加していない人」を洗い出しています。
1.2.2 全称量化
「すべてのものが～する」を SQL で表現するには工夫が必要です。
残念ながら SQL に FORALL 述語なんてありません。
そこで 二重否定を使います。
「すべての要素が条件を満たす」
⇔「条件を満たさない要素はひとつも存在しない」
イメージ
∀x P(x) ⇔ ¬∃x ¬P(x) ⇔ NOT EXISTS (条件を満たさない行)
ex. すべてのテストで 50 点以上の生徒を探す
TestScores テーブル
| student_id | subject | score |
|---|---|---|
| 100 | 算数 | 100 |
| 100 | 国語 | 80 |
| 100 | 理科 | 80 |
| 200 | 算数 | 80 |
| 200 | 国語 | 95 |
| 300 | 算数 | 40 |
| 300 | 国語 | 90 |
| 300 | 社会 | 55 |
| 400 | 算数 | 80 |
SELECT DISTINCT
student_id
FROM
TestScores TS1
WHERE
NOT EXISTS(
SELECT *
FROM TestScores TS2
WHERE
TS2.student_id = TS1.student_id
AND TS2.score < 50
);
結果
| student_id |
|---|
| 100 |
| 200 |
| 400 |
これは「50 点未満の点数が存在しない」＝「すべて 50 点以上」を表しています。
2. 手続き型言語との比較
SQL を、慣れ親しんだ手続き型のプログラミング言語と比較してみます。
2.1 順次実行
手続き型なら「上から順番に実行」ですが、SQL では実行順序はこうです。
FROM → WHERE → GROUP BY → HAVING → SELECT (→ ORDER BY)
SELECT 句は、最後に 見た目を整えたり とか 計算した列を追加したり しているだけです。
だから、実行順序どおりに FROM 句から読み書きする と、流れが自然でロジックも追いやすいです。
2.2 条件分岐
SQL の条件分岐は CASE 式を使います。
if 文より関数型っぽいです。
CASE
WHEN x = 1 THEN 'x は 1 です'
WHEN x = 2 THEN 'x は 2 です'
ELSE 'x はそれ以外の数です'
END
2.3 繰り返し
SQL には for 文や while 文はありません。
その代わり「集約（GROUP BY）」と「相関サブクエリ」を使って繰り返し処理を表現します。
2.3.1 GROUP BY を使った繰り返し
繰り返し処理を SQL 的に言い換えると 「集合にまとめて条件を書けること」 になります。
ex. 学生が全員レポート提出済みの学部を探す（再掲）
Students テーブル
| student_id | dpt | sbmt_date |
|---|---|---|
| 100 | 理学部 | 2018-10-10 |
| 101 | 理学部 | 2018-9-22 |
| 102 | 文学部 | |
| 103 | 文学部 | 2018-9-10 |
| 200 | 文学部 | 2018-9-22 |
| 201 | 工学部 | |
| 202 | 経済学部 | 2018-9-25 |
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);
結果
| dpt |
|---|
| 理学部 |
| 経済学部 |
集合に対して一気に条件をかけるので、ループは不要です。
2.3.2 相関サブクエリを使った繰り返し
相関サブクエリは「外側の行ごとに内側の検索を繰り返す」ため、ループ的に動作します。
ex. 各商品分類ごとの平均より高い商品を探す
Shohin テーブル
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka |
|---|---|---|---|
| 0001 | T シャツ | 衣服 | 1000 |
| 0002 | 穴あけパンチ | 事務用品 | 500 |
| 0003 | カッターシャツ | 衣服 | 4000 |
| 0004 | 包丁 | キッチン用品 | 3000 |
| 0005 | 圧力鍋 | キッチン用品 | 6800 |
| 0006 | フォーク | キッチン用品 | 500 |
| 0007 | おろしがね | キッチン用品 | 880 |
| 0008 | ボールペン | 事務用品 | 100 |
SELECT
shohin_bunrui,
shohin_mei,
hanbai_tanka
FROM
Shohin S1
WHERE
hanbai_tanka > (
SELECT AVG(shohin_tanka)
FROM Shohin S2
WHERE S1.shohin_bunrui = S2.shohin_bunrui
GROUP BY shohin_bunrui
);
結果
| shohin_bunrui | shohin_mei | hanbai_tanka |
|---|---|---|
| 事務用品 | 穴あけパンチ | 500 |
| 衣服 | カッターシャツ | 4000 |
| キッチン用品 | 包丁 | 3000 |
| キッチン用品 | 圧力鍋 | 6800 |
相関サブクエリを避けるなら WINDOW 関数を使う（WinMagic）
相関サブクエリには可読性やパフォーマンスの観点で課題があります 😵💫
WINDOW 関数を使うと、効率的に置き換えられる場合が多いです。
相関サブクエリは外側テーブルの行ごとにテーブルスキャンが発生します。
ただし、結合キーにインデックスが貼られている場合は、その限りではなく効率的に処理されます。
SELECT
shohin_bunrui,
shohin_mei,
hanbai_tanka
FROM
(
SELECT
shohin_mei,
shohin_bunrui,
hanbai_tanka,
AVG(hanbai_tanka) OVER(PARTITION BY shohin_bunrui) AS avg_tanka
FROM
Shohin
) TMP
WHERE
hanbai_tanka > avg_tanka;
理解しやすく、テーブルスキャンが 1 回で済むためパフォーマンスも良いです。
おわりに
『達人に学ぶ SQL 徹底指南書 第 2 版』を読んで SQL 脳が鍛えられた気がします 🤔
もっと早くに読めばよかった...