初めに
この問題は、SQLパズル #21 『飛行機と飛行士』 を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです
この記事では、関係除算について詳しく見ていきたいと思います
関係除算ですが、なかなかとっつきにくい考え方をしています
SQLならでは・・・といいますか、とてもユニークな思考です。
SQLを理解するには、関係除算は避けては通れない考え方です
始めます!!
この問題はテーブルが2つ有ります。1つは飛行機を保管する格納庫のデータ
もう1つは、飛行機を操縦できるパイロットのデータです。
求めたい回答は、『格納庫に有るすべての飛行機を操縦できるパイロットを探す』です
その過程で、データの割り算を行います。これが関係除算と呼ばれる考え方です
テーブル と データ
CREATE TABLE PilotSkills (
pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY (pilot, plane)
);
CREATE TABLE Hangar (
plane CHAR(15) PRIMARY KEY
);
INSERT INTO PilotSkills
VALUES ('Celko', 'Piper Cub'),
('Higgins', 'B-52 Bomber'),
('Higgins', 'F-14 Fighter'),
('Higgins', 'Piper Cub'),
('Jones', 'B-52 Bomber'),
('Jones', 'F-14 Bomber'),
('Smith', 'B-1 Bomber'),
('Smith', 'B-52 Bomber'),
('Smith', 'F-14 Fighter'),
('Wilson', 'B-1 Bomber'),
('Wilson', 'B-52 Bomber'),
('Wilson', 'F-14 Fighter'),
('Wilson', 'F-17 Fighter');
INSERT INTO Hangar
VALUES ('B-1 Bomber'),
('B-52 Bomber'),
('F-14 Fighter');
Hangerテーブルには3機の飛行機が登録されています
PilotSkillsテーブルには5名のパイロットがいて、操縦できる飛行機の機種が登録されています
この3機の飛行機を全て操縦できるパイロットを SQL で求めていきます
パイロットが操縦できる飛行機一覧
SELECT PS1.*,
(SELECT array_agg(plane) FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot)
FROM PilotSkills AS PS1
まずは、各パイロットが操縦できる飛行機を1つの集合データにします。
PilotSkills テーブルを同じパイロット名で自己結合しています
⇒ WHERE PS1.pilot = PS2.pilot
これにより、PS1のパイロットと同じパイロットが操縦できる飛行機を
PS2から集合データとして取出している
▼ 出力
パイロットが操縦できる格納庫にある飛行機一覧
SELECT PS1.*,
(SELECT array_agg(plane) FROM Hangar
WHERE EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
FROM PilotSkills AS PS1
▼ 出力
全体のイメージ
青い点線部分は『パイロットが操縦できる飛行機一覧』を参照ください
格納庫に存在する飛行機を操縦できるパイロットが取り出せるSQLが確認できました
このなかで、Smith と Wilson は全ての飛行機が操縦できます
次は、格納庫に有る飛行機が操縦出来ない パイロット情報と取出してみます
パイロットが操縦できない格納庫にある飛行機一覧
SELECT PS1.*,
(SELECT array_agg(plane) FROM Hangar
WHERE NOT EXISTS ( -- NOT EXISTS に変更
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
FROM PilotSkills AS PS1
▼ 出力
WHERE句の EXISTS を NOT EXISTS に変更
Celko, Higgins, Jones は格納庫に有るいくつかの飛行機が操縦できない・・・・が
Smith, Wilson は格納庫に有る飛行機で、操縦出来ない飛行機は存在しない
⇒ つまり、全部の飛行機を操縦できる
⇒ この情報が取り出したい
サブクエリを WHERE句で使う
SELECT PS1.*,
(SELECT array_agg(plane) FROM Hangar
WHERE NOT EXISTS ( -- NOT EXISTS に変更
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
FROM PilotSkills AS PS1
WHERE NOT EXISTS (
SELECT * FROM Hangar
WHERE NOT EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
▼ 出力
WHERE句に NOT EXISTSを追記した
この中に、『格納庫にある飛行機が操縦出来ないパイロット情報』を入れている
つまり、NOT EXISTS(●●●) なので
⇒ ●●● が存在しない
⇒ ●●● とは
⇒ パイロットが操縦出来ない格納庫にある飛行機
2重否定を使って、肯定文を書くイメージです
操縦出来ない格納庫にある飛行機 が・・・存在しない パイロットを探す
⇒ NOT EXISTS ⇒ NOT EXISTS
Smith と Wilson が取り出せるイメージ
内側の NOT EXISTS(サブクエリ)
⇒ Celko,Higgins,Jones は 格納庫に有る操縦出来ない飛行機が返ってくる
⇒ Smith と Wilson は格納庫にある全ての飛行機が操縦出来るので空白が返ってくる
外側の NOT EXISTS
⇒ 内側の NOT EXISTS(サブクエリ) から空白が返ってくるパイロットを取出す
⇒ 格納庫の中で 操縦できない飛行機 が、自分の操縦出来る飛行機の中に 存在しない
⇒ 格納庫にある全ての飛行機が操縦できるパイロット
⇒ Smith と Wilson がこれに当てはまる
回答SQL
--SELECT PS1.*
SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS (
SELECT * FROM Hangar
WHERE NOT EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
▼ 出力
| pilot |
|---|
| Smith |
| Wilson |
実際に割り算をしてみる
関係除算とは言葉のごとく割り算をしています・・・が
SQL での割り算の考え方なのでいまいちピンとこないです
回答SQLのどこで割り算をしているのでしょうか？
無理やりですが、イメージ化してみました。
おそらくですが・・・このような処理をしています
しかし、割り算しているようには見えません・・・ただ
PilotSkills AS PS1 から、『格納庫にある飛行機を操縦できないパイロット』を
取り除いている・・だけの様にみえます
そこで、割り算の特性を確認します
⇒ 6 ÷ 3 = 2
⇒ 被除数 ÷ 除数 = 回答
⇒ 3 x 2 = 6
⇒ 除数 x 回答 = 被除数
つまり、除数と回答の掛け算が被除数になれば
関係除算が割り算の様な処理を行ている・・・と見る事が出来ます
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
本文より引用
関係除算の重要な特性の1つは
除テーブルと商をクロス結合する(すなわち直積を得る)と
被除テーブルの妥当な部分集合になる事である
関係除算という名前は
クロス結合がちょうど掛け算のように作用することから付けられている
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
確認SQL
WITH answer AS (
SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS (
SELECT * FROM Hangar
WHERE NOT EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
)
SELECT * FROM answer, Hangar
▼ 出力(画像右側)
回答SQLを CTE (answer) で定義して Hangar テーブルと CROSS JOIN しています
画像左側は上記の途中で取得したデータです
⇒ 『サブクエリを WHERE句で使う』参照
⇒ PilotSkills テーブルからデータを取出しています
画像右側が確認SQLの掛け算
⇒ 回答(answer) x 除数(Hangar) = 被除数(PilotSkills)
画像左側と右側のデータが大方一致しています
関係除算は割り算の様な処理を行っている・・・と言えます
