「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。今月(2022年10月)からは多忙につき、日々の投稿数を減らします。

MENU

SQL中級者になる!(その05/X)

初めに

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


power-of-awareness.com


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

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

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)

 \mathrm{EXISTS}文を用いると、パフォーマンスが良く、結果に含められる情報量が多い。\mathrm{HAVING}文では集約されるために\mathrm{project}_\mathrm{id}しか表示できない。

5.5 まとめ

 \mathrm{SQL}では\mathrm{EXISTS}は重要な意味を持つ。

  • \mathrm{SQL}における述語は真理値を返す関数を指す。
  • \mathrm{EXISTS}のみが(行の)集合を引数に取れる。
  • その意味では\mathrm{EXISTS}高階関数の一種と見なせる。
  • \mathrm{SQL}には全称量化子に相当する演算子が無いため\mathrm{NOT\ EXISTS}で代用する。
プライバシーポリシー お問い合わせ