はじめに
Web アプリケーションエンジニアに必要な技術スタックには大きく次の 4 領域があると考えています。
- フロントエンド
- バックエンド
- データベース
- インフラ（クラウド）
最近の私の個人的なテーマが「DB とクラウドの強化」です 💪
DB 領域の強化をねらって「達人に学ぶ SQL 徹底指南書 第 2 版」を読みました。
この本を読んで得た「SQL の考え方」を宣言型・集合指向言語という切り口からまとめてみます ✍
1. SQL を支える理論
SQL は、宣言型かつ集合指向言語です。
背景に、次の 2 つの理論があるためです。
- 集合論
- 述語論理
本題に入る前に、「手続き的」「宣言的」という言葉について触れておきます。
手続き的: どのように処理するか、ひとつひとつ記述する
宣言的: どのような結果がほしいか、その条件だけを記述する
1.1 集合論
1.1.1 テーブルを集合と見なす
関係モデルを採用したデータベースだから、関係データベースと呼びます。
関係とは、テーブルのことで、その定義は、属性（列）の定義域の直積の部分集合です。
関係の定義
関係: $R$ , 属性（列）: $A_i$ , その定義域: $D_i$
$$R \subseteq D_1 \times D_2 \times D_3 \times \cdots \times D_n$$
つまり、テーブルは集合と見なせます。
自己結合
自己結合は、同じテーブルに違う名前を与えて、それらを別のテーブルとして存在しているかのように扱います。
テーブルの操作は、ファイルのような実体を直接扱うのではなく、メモリ上での扱いが秘匿された集合を扱うという好例です。
手続き型言語のように 1 行ずつ読み出し、それぞれに対する処理を記述するようなことはしません。
もちろん、パフォーマンスを考慮する場合、メモリ上での扱いを意識する必要があります。
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 |
1.1.2 集約（GROUP BY 句、HAVING 句）
HAVING 句は、GROUP BY 句でテーブルを類別し、部分集合に対する条件を記述します。
部分集合ごとに条件を記述するという点が集合指向ですし、結果の条件だけを記述するという点が宣言的と言えます。
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 |
|---|
| 理学部 |
| 経済学部 |
1.2 述語論理
SQL において述語論理が効力を発揮する場面は、「複数行を一単位として」取り扱うときです。
述語論理では、複数の対象をひとまとめにして扱うとき、「量化子」という述語を使います。
SQL では EXISTS 述語を使って表現します。
- 全称量化子：定義なし（NOT EXISTS で代用）
- 存在量化子：EXISTS 述語
1.2.1 存在量化
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 回 | 坂東 |
1.2.2 全称量化（肯定 ⇔ 二重否定）
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 |
条件を次のように変換することで、NOT EXISTS で表現できます。
すべての教科が 50 点以上 ⇔ 50 点未満である教科が 1 つも存在しない
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 |
2. 手続き型言語との比較
2.1 順次実行
手続き型言語では、基本的に上から下に実行されます。
しかし、SQL では次の順で実行されます。
FROM → WHERE → GROUP BY → HAVING → SELECT (→ ORDER BY)
SELECT 句は、一番最後に表示用に見た目を成形したり、計算列を算出しているだけです。
実行順序に従って、FROM 句から書いたほうが自然にロジックを追うことができます。
2.2 条件分岐
SQL は、関数型言語と考え方が近いです。
手続き型言語の if 文や case 文は CASE 式で表現します。
CASE
WHEN x = 1 THEN 'x は 1 です'
WHEN x = 2 THEN 'x は 2 です'
ELSE 'x はそれ以外の数です'
END
2.3 繰り返し
SQL には繰り返し制御文（ループ）が存在しません。
手続き型言語でループを使っていた処理は、集約（GROUP BY 句）と相関サブクエリで表現できます。
ただし、相関サブクエリは難しいです。
相関サブクエリは集合単位の操作や WINDOW 関数で消去できることが多いです。
つまり、集合演算と WINDOW 関数があれば、ほとんどのことは表現できると言えます。
以下では、相関サブクエリを使う書き方と、使わない書き方を例示します。
2.3.1 HAVING vs NOT EXISTS（相関サブクエリ）
それぞれメリット/デメリットがあり、トレードオフの関係です。
- NOT EXISTS はパフォーマンスが良い
- 1 行でも条件を満たさない行が存在すれば、そこで検索を打ち切る
- 結合キーにインデックスが貼られていれば利用できる
- NOT EXISTS は結果に含められる情報量が多い（HAVING は集約される）
- NOT EXISTS は二重否定を使う分、HAVING より直感的にわかりにくい
ex.
次のテーブルから、工程 1 番まで完了のプロジェクトを選択することを考えます。
Projects
| project_id | step_nbr | status |
|---|---|---|
| AA100 | 0 | 完了 |
| AA100 | 1 | 待機 |
| AA100 | 2 | 待機 |
| B200 | 0 | 待機 |
| B200 | 1 | 待機 |
| CS300 | 0 | 完了 |
| CS300 | 1 | 完了 |
| CS300 | 2 | 待機 |
| CS300 | 3 | 待機 |
| DY400 | 0 | 完了 |
| DY400 | 1 | 完了 |
| DY400 | 2 | 完了 |
HAVING（集合指向的）
SELECT project_id
FROM Projects
GROUP BY project_id
HAVING
COUNT(*) = SUM(
CASE
WHEN step_nbr <= 1 THEN AND status = "完了" THEN 1
WHEN step_nbr > 1 AND status = "待機" THEN 1
ELSE 0
END
);
結果
| project_id |
|---|
| CS300 |
NOT EXISTS（述語論理的）
SELECT *
FROM Projects P1
WHERE
NOT EXISTS(
SELECT *
FROM Projects P2
WHERE
P1.project_id = P2.project_id
AND P2.status <> (
CASE
WHEN step_nbr <= 1 THEN "完了"
ELSE "待機"
END
)
);
結果
| project_id | step_nbr | status |
|---|---|---|
| CS300 | 0 | 完了 |
| CS300 | 1 | 完了 |
| CS300 | 2 | 待機 |
| CS300 | 3 | 待機 |
2.3.2 WINDOW 関数 vs 相関サブクエリ
相関サブクエリに比べ、WINDOW 関数には次のようなメリットがあります。（完全上位互換）
- WINDOW 関数は、サブクエリだが、相関サブクエリではない
- 可読性が高く、動作も理解しやすい
- 単体で実行することができる（デバッグが容易）
- WINDOW 関数は、テーブルに対するスキャンが一度だけで済む（パフォーマンスが良い）
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 関数
SELECT
shohin_mei,
shohin_bunrui,
hanbai_tanka,
avg_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;
結果
| shohin_mei | shohin_bunrui | hanbai_tanka | avg_tanka |
|---|---|---|---|
| 穴あけパンチ | 事務用品 | 500 | 300 |
| カッターシャツ | 衣服 | 4000 | 2500 |
| 包丁 | キッチン用品 | 3000 | 2795 |
| 圧力鍋 | キッチン用品 | 6800 | 2795 |
おわりに
これまで雰囲気で SQL を書いていました。
SQL の「背景」を知ることができ、SQL における頭の使い方を学ぶことができました。
もっと早くに読めばよかったと思いました。(読書感想文)