はじめに

最近『達人に学ぶ 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 脳が鍛えられた気がします 🤔
もっと早くに読めばよかった...