SQL中級者になる！（その05/12） - 「大人の教養・知識・気付き」を伸ばすブログ

初めに

　データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を

power-of-awareness.com

を基に学んできた。
　この知識をより昇華させて「SQL中級者になりたい！」ため、同じ著者の

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ

作者:ミック
翔泳社

Amazon

を参考に、更なるSQLの知識を拡充していこう。

前回

power-of-awareness.com

初めに
前回
5.　EXISTS述語の使い方
次回

5.　EXISTS述語の使い方

　 $\mathrm{SQL}$ とリレーショナルデータベースを支える基礎理論は、集合論と述語論理(一階述語論理)である。ここでは述語論理の $\mathrm{EXISTS}$ 述語を扱う。 $\mathrm{EXISTS}$ は量化を実現するために $\mathrm{SQL}$ に取り入れられた。

5.1　述語とは何か

　述語は戻り値が真理値になる関数である。命題の構造を調べるために導入された。リレーショナルデータベースではテーブルの一行を1つの命題と見立てる。

5.2　存在の階層

　 $=$ や $\mathrm{BETWEEN}$ などと $\mathrm{EXISTS}$ とを比較するとその使い方に大きな相違がある。それは述語の引数に何を取るかである。 $\mathrm{x}=\mathrm{y}$ や $\mathrm{x\ BETWEEN\ y\ AND\ z}$ などの述語の引数は単一の値(スカラ値)である。一方で $\mathrm{EXISTS}$ は引数として $\mathrm{SELECT}$ 文を取っている。

SELECT id
FROM Foo F
WHERE EXISTS 
     (SELECT *
      FROM Bar B
      WHERE F.id=B.id)

　述語論理では「入力のレベル」に応じて述語を分類し、1行を入力とする述語を「一階の述語」、行の集合を入力とする述語を「二階の述語」という。

5.3　全称量化と存在量化

　「すべての $x$ が条件 $P$ を満たす」および「条件 $P$ を満たす $x$ が存在する」を表現するための述語として量化子があり、前者を全称量化子、後者を存在量化子という。 $\mathrm{SQL}$ の $\mathrm{EXISTS}$ 述語は述語論理の存在量化子を実現させた。一方で全称量化子は導入しなかった。なぜならば、 $\mathrm{de\ Morgan}$ の法則により、一方がもう一方で表現できるためである。
　 $\mathrm{SQL}$ で全称量化を表現するには「条件 $P$ を満たさない $x$ が存在しない」と表現すればよい。

5.4　テーブルに存在「しない」データを探す

　存在するデータについて、ある性質を満たす条件というものを設定することで、ある条件を見たすものを選択できる。これに対してデータが存在するか否かを調べることもあり得、これが二階の問い合わせで $\mathrm{EXISTS}$ 述語が活きる。

	meeting (会合)	person (出席者)
	第1回	伊藤
	第1回	水島
	第1回	坂東
	第2回	伊藤
	第2回	宮田
	第3回	坂東
	第3回	水島
	第3回	宮田

-- 欠席者のみを求めるクエリ：その1　存在量化の応用
SELECT DISTINCT M1.meeting, M2.person
FROM Meetings M1 CROSS JOIN Meetings M2WHERE NOT EXISTS
(SELECT *
 FROM Meetings M3
 WHERE M1.meeting = M3.meeting
 AND M2.person = M3.person)

-- 欠席者のみを求めるクエリ：その2　差集合演算の利用
SELECT M1.meeting, M2.person
FROM Meetings M!, Meetings M2
EXCEPT
SELECT meeting, person
FROM Meetings

5.4.1　全称量化　その1　肯定と二重否定の変換

　以下のテーブルに対して、「すべての教科で50点以上を取っている生徒」を選択する。

student_id (学生ID)	subject (教科)	score (点数)
100	算数	100
100	国語	80
100	理科	80
200	算数	80
200	国語	95
300	算数	40
300	国語	90
300	社会	55
400	算数	80

このとき「すべての教科で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 -- 50点未満の教科
)


***5.4.2　全称量化　その2　集合 vs. 述語
　[tex:\mathrm{EXISTS}]と[tex:\mathrm{HAVING}]はかなりの互換性を持つ。
　たとえばプロジェクトの工程管理を行うテーブル(projects)

|　　|*<div align="center">project_id</p>(プロジェクトID)</div>|*<div align="center">step_nbr</p>(行程番号)</div>|*<div align="center">status</p>(状態)</div>|
|　　|AA100|[tex:0]|完了|
|　　|AA100|[tex:1]|待機|
|　　|AA100|[tex:2]|待機|
|　　|B200|[tex:0]|完了|
|　　|B200|[tex:1]|完了|
|　　|B200|[tex:2]|待機|
|　　|B200|[tex:3]|待機|

から、1番の工程までを完了させているプロジェクトを取得する。

>|sql|
SELECT project
FROM projects
GROUP BY project_id
HAVING COUNT(*) = SUM(CASE WHEN step_nbr <= 1 AND status = '完了' THEN 1
                                                        step_nbr > 1 AND status = '待機' THEN 1
                                                         ELSE 0 END)
-- 
SELECT *
FROM Projects P1
WHERE NOT EXISTS
(SELECT status
 FROM Projects P2
 WHEREP1.project_id = P2.project_id
 AND status <> CASE WHEN step_nbr <= 1
 THEN '完了'
 ELSE '待機' END)