4. 全称文を述語で表現する
次に全称文を述語で表現する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 <> '待機' );
これをwindow
関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
member | team_id | status |
ジョー | 1 | 待機 |
ケン | 1 | 出動中 |
ミック | 1 | 待機 |
カレン | 2 | 出動中 |
キース | 2 | 休暇 |
ジャン | 3 | 待機 |
ハート | 3 | 待機 |
ディック | 3 | 待機 |
ベス | 4 | 待機 |
アレン | 5 | 出動中 |
ロバート | 5 | 休暇 |
ケーガン | 5 | 待機 |
team_id | member |
3 | ジャン |
3 | ハート |
3 | ディック |
4 | ベス |
答えは、下記となります。
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) a where willOut = 1;
インラインビューの中のselect
文のイメージは下記となります。partition by team_id
で赤線を引いてます。
ここでは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
文の実行時のイメージは、先ほどと同じです。今度は、group by team_id
で赤線を引いてます。
既にお気づきかもしれませんが、window
関数でpartition by
句によるパーティション作成と、group by
句によるグループ化は考え方が非常に似ており、SQLのイメージが同一(分割するキーで分割して線を引く)になるのです。また、『帰ってきたHAVING句』で提示されている「集合の性質を調べるための条件の使い方一覧」は、partition by
句でパーティションで分割した集合にも適用可能です。
なお、PostgreSQLでは、bool_and
関数とbool_or
関数が実装されてます。bool_and
関数が全称肯定命題に相当し、bool_or
関数が存在肯定命題に相当しますので、下記のような別解もあります。
select team_id,member from (select team_id,member, bool_and(status = '待機') over(partition by team_id) as willOut from Teams) a where willOut;
select team_id from Teams group by team_id having bool_and(status = '待機');
最後に
本稿では、『分析関数の衝撃4(完結編)』を、PostgreSQL8.4用にリニューアルした内容を扱いました。次回は、『分析関数の衝撃5(総集編)』を、PostgreSQL8.4用にリニューアルした内容を扱います。
参考資料
- 9.17. 配列関数と演算子
PostgreSQLのマニュアルです。配列関数と演算子に関する説明です。
- 9.18. 集約関数
PostgreSQLのマニュアルです。本稿で扱った
array_agg
関数やbool_and
関数やbool_or
関数に関する説明です。 - 9.19. ウィンドウ関数
PostgreSQLのマニュアルです。ウィンドウ関数に関する説明です。