SQL修行vol.6

2024年3月22日 17:17

『達人に学ぶSQL徹底指南書』ー5.EXISTS述語の使い方

1日1章が理解の限界かなぁって思っていたけど、2章目いけそうなのでトライ。どんどん数学的は話になっていくもんで、ちょっとおもしろくなってきた（理解できたとは言ってない）。

前提１：SQLとRDBを支える基礎理論

「集合論」「一階述語論理」の2つがある。これまでは前者の側面からの解説で、今回は後者の側面からの解説。

集合論
1874～1897年にカントルによってつくられた数学の一分野。彼は集合を次のように定義した。「集合とは、明確に定義され、かつ互いに明確に弁別できるわれわれの直観あるいは思惟(しい)の対象mを、一つの全体にまとめたものMのことである。

コトバンクー日本大百科全書

述語論理
単純な命題だけを扱うのでなく、対象を表わす変数をもった述語をも扱う論理。命題論理を拡張して、「すべての…」を表わす全称記号（∀）と「…が存在する」を表わす存在記号（∃）を用いる。

コトバンクー精選版日本国語大辞典

SQLとどう関係があるかというと、EXSITSは「量化」という述語論理の強力な機能を実現するためにSQLに取り入れられた。（今日初耳単語多いな・・・）

量化（りょうか、英: Quantification）
言語や論理学において、論理式が適用される（または満足される）議論領域の個体の「量」を指定すること。

Wikipedia

前提２：述語ってなに？

戻り値が真理値になる関数
つまり、適当な引数が与えられたら"true" or "false" or "unknown"を返す関数
例：=, <, >, BETWEEN, LIKE, IN, IS NULL

ちなみに、WHERE句も
述語を組み合わせて1つの述語を作っている
ことになる。
∵WHERE句の戻り値が"true"になる命題（レコード）のみがテーブルから選択される。
→「集合」と「述語」はほとんど同じ意味

理論編

🐣存在と階層
ここではなぜEXSITSがそんなに特異なのかを明かす。
他の述語（=やBETWEEN）との違いは「引数にとる値」である。
他の述語：スカラ値（単一の値）
EXISITS ：SELECT文（行の集合）

述語論理では入力レベルに応じて述語を分類する。
一階の述語：=やBETWEENなどのように1行
二階の述語：EXSITSのように行の「集合」
三階の述語：「集合の集合」
・・・

🐣全称量化と存在量化
述語論理には量化子という特別な述語「全称量化子[∀]」「存在量化子[∃]」がある。後者の存在量化子をSQLに実装したのが「EXSITS」である。前者はSQLに実装されていない。

つまり、
全称量化「すべての行が条件Pを満たす」を
存在量化「条件Pを満たさない行が存在しない」へ
変換して考える必要がある。

実践編

🐣テーブルに存在「しない」データを探す
ここに何回かにわたる会合とその出席者を記録するテーブルがあるとして、このテーブルから「出席しなかった人物」を求める（全回欠席はなし）。

〜考え方〜
全員皆勤したと仮定した場合の集合
から
現実に参加した人の集合
を引き算する

存在量化の応用パターン

--全員皆勤したと仮定した場合の集合
SELECT DISTINCT
   M1.meeting,
   M2.person
FROM
   Meetings M1
      CROSS JOIN
         Meetings M2;

--上から現実に参加した人の集合を引き算する
SELECT DISTINCT
   M1.meeting
   M2.person
FROM
   Meetings M1
      CROSS JOIN
         Meetings M2
WHERE NOT EXSITS(SELECT
                   *
                 FROM
                    Meeting M3
                 WHERE
                    M1.meeting = M3.meeting,
                    AND
                    M2.person = M3.person
                 );

差集合（EXCEPT）演算の利用パターン

SELECT
   M1.meeting,
   M2.person
FROM
   Meetings M1,
   Meetings M2
EXCEPT
   SELECT
      meeting,
      person
   FROM
      Meetings;

EXCEPTについて補足
1つ目の検索結果を元にして、2つ目の検索結果に存在するデータを差し引く場合に使う集合演算子（まぁそのままだ。EXCEPTより前のSELECTで取得できた結果から、EXCEPTより後のSELECTで取得できた結果をひく。）

🐣全称量化　その１ー肯定↔︎二重否定の変換に慣れる

例１
全称量化（＝肯定）：すべての教科が50点以上である
存在量化（＝二重否定）：50点未満である教科が1つも存在しない

存在量化（＝二重否定）をNOT EXISTSで表現すると

SELECT DISTINCT
   student_id
FROM
   TestScores TS1
WHERE NOT EXIST (SELECT
                    *
                 FROM
                    TestScores TS2
                 WHERE
                    TS1.student_id = TS2.student_id
                    AND
                    TS2.score < 50
                 );

例２
全称量化（＝肯定）：1.算数の点数が80点以上、2.国語の点数が50点以上
→ある学生の全ての行について、教科が算数ならば80点以上であり、教科が国語なら50点以上である
存在量化（＝二重否定）：ある学生の行において算数が80点未満で、国語が50点未満の行は存在しない

存在量化（＝二重否定）をNOT EXISTSで表現すると

SELECT DISTINCT
   student_id
FROM
   TestScores TS1
WHERE subject IN ('算数', '国語')
      AND
      NOT EXISTS (SELECT
                     *
                  FROM
                     TestSCores TS2
                  WHERE
                     TS1.student_id = TS2.student?id
                     AND
                     1 = CASE 
                            WHEN subject = 算数 AND score < 80 THEN 1 ELSE 0
                                 subject = 国語 AND score < 50 THEN 1 ELSE 0
                         END
                  );

🐣全称量化　その２　集合VS述語
HAVING（集合指向）とEXSITS（述語論理）の比較をするよ。
例：工程1番まで完了のプロジェクトを選択する

HAVING（集合指向）
工程番号が1以下で「完了」の行数＋工程番号が1より大きくて「待機」の行数＝プロジェクト全体の行数

SELECT
   project_id
FROM
   Projects
GROUP BY
   project_id
HAVING COUNT (*) = SUM(CASE
                          WHEN step_nbr <>=1 AND status = '完了' THEN 1
                          WHEN step_nbr  >1  AND status = '待機' THEN 1
                          ELSE 0
                        END
                        );

EXSITS（述語論理）
プロジェクト内の全ての行について、工程番号が1以下ならば完了であり、1より大きければ待機である

SELECT
   project_id
FROM
   Projects P1
WHERE NOT EXSITS (SELECT
                     status
                  FROM
                     Projects P2
                  WHERE
                     P1.project_id = P2.project_id
                     AND status <> CASE
                                      WHEN step_nbr <= 1
                                      THEN '完了'
　　　　　　　　　　　　　              　 ELSE '待機'
                                   END
                  );

🐣列に対する量化
EXISTS「行方向への量化」なので、使えない。
例1：オール1の行を探す

--全称量化：col1-col3のすべての列が1である
SELECT
   *
FROM
   ArrayTble
WHERE
   1 = ALL (col1, col2, col3);

--存在量化1：少なくとも1つは9である
SELECT
   *
FROM
   ArrayTble
WHERE
   9 = ANY (col1, col2, col3);

--存在量化2：少なくとも1つは9である
SELECT
   *
FROM
   ArrayTble
WHERE
   9 IN (col1, col2, col3);

例2：オールNULLの行を探す

SELECT
   *
FROM
   ArrayTbl
WHERE
   COALESE (col1, col2, col3) IS NULL;