全称文を述語で表現する
次に全称文を述語で表現するSQLについて解説します。「帰ってきたHAVING句」では、以下のSQLが提示されています。
SELECT team_id, member FROM Teams T1 WHERE NOT EXISTS (SELECT * FROM Teams T2 WHERE T1.team_id = T2.team_id AND status <> '待機' );
これを分析関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。
| member | team_id | status |
| ジョー | 1 | 待機 |
| ケン | 1 | 出動中 |
| ミック | 1 | 待機 |
| カレン | 2 | 出動中 |
| キース | 2 | 休暇 |
| ジャン | 3 | 待機 |
| ハート | 3 | 待機 |
| ディック | 3 | 待機 |
| ベス | 4 | 待機 |
| アレン | 5 | 出動中 |
| ロバート | 5 | 休暇 |
| ケーガン | 5 | 待機 |
| member | team_id |
| ジャン | 3 |
| ハート | 3 |
| ディック | 3 |
| ベス | 4 |
手続き型の言語であれば、次のように処理を行うものと思われます。
- team_idの昇順にソート
- team_idの最小値からループ
- team_idがブレイクするまでに、statusが待機のデータがなかったら出力
分析関数を使ったSQLでも似たような考え方を使います。
select team_id,member from (select team_id,member, min(case when status = '待機' then 1 else 0 end) over(partition by team_id) as willOut from Teams) where willOut = 1;
インラインビューの中のselect文のイメージは次のようになります。
ここではmin関数を利用します。team_id毎に作成したパーティション内では、statusが待機なら1、それ以外なら0と判定し、case式の最小値を求めています。min関数の結果が1なら、全てのstatusは待機となります。そして、外側のwhere句で、min関数で求めた最小値=1（willOut=1）を条件としてレコードの絞り込みをおこなっています。
exists述語が∃X:P(X)に、not exists述語が￢(∃X:P(X))にそれぞれ相当します。類似するmax関数やmin関数とcase式との使い方も含めてまとめると、以下のように考えることができます。
max(case when 条件P then 1 else 0 end) = 1 --存在肯定命題 ∃X:P(X) min(case when 条件P then 1 else 0 end) = 1 --全称肯定命題 ∀X:P(X) max(case when 条件P then 0 else 1 end) = 1 --存在否定命題 ∃X:￢P(X) min(case when 条件P then 0 else 1 end) = 1 --全称否定命題 ∀X:￢P(X)
exists述語で∀X:P(X)を表現する場合、∀X:P(X) = ￢(∃X:￢P(X)) という要領で否定の条件を用いる必要があります。しかし、max関数、min関数とcase式を利用できる場合は、否定の条件を使う必要がありません。また、条件が真の場合は1が、偽の場合は0が関数の戻り値となるので、ブール値として利用することも可能です。
なお、memberカラムを必要とせず、全てのstatusが待機となるteam_idを求める場合は、以下のSQLで対応できます。
select team_id from Teams group by team_id having min(case when status = '待機' then 1 else 0 end) = 1;
select文の実行時のイメージは、先ほどと同じです。
既にお気づきかもしれませんが、分析関数でpartition by句によるパーティション作成と、group by句によるグループ化は考え方が非常に似ており、SQLイメージが同一（ソートキーでソートして線を引く）になるのです。また、「帰ってきたHAVING句」で提示されている「集合の性質を調べるための条件の使い方一覧」は、partition by句でパーティションで分割した集合にも適用可能です。
