SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

分析関数の衝撃

PostgreSQLの分析関数の衝撃4
(集合の一致と全称肯定命題)

array_agg関数とbool_and関数の使用例

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (2.4 KB)

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関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。

Teams
member team_id status
ジョー 1 待機
ケン 1 出動中
ミック 1 待機
カレン 2 出動中
キース 2 休暇
ジャン 3 待機
ハート 3 待機
ディック 3 待機
ベス 4 待機
アレン 5 出動中
ロバート 5 休暇
ケーガン 5 待機
出力結果
team_id member
3 ジャン
3 ハート
3 ディック
4 ベス

 答えは、下記となります。

window関数で書き換えたSQL1
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で赤線を引いてます。

SQLのイメージ
SQLのイメージ

 ここでは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式との使い方も含めてまとめると、以下のように考えることができます。

SQLでの存在肯定命題と全称肯定命題と存在否定命題と全称否定命題
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で対応できます。

group byを使ったSQL1
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で赤線を引いてます。

SQLのイメージ
SQLのイメージ

 既にお気づきかもしれませんが、window関数でpartition by句によるパーティション作成と、group by句によるグループ化は考え方が非常に似ており、SQLのイメージが同一(分割するキーで分割して線を引く)になるのです。また、『帰ってきたHAVING句』で提示されている「集合の性質を調べるための条件の使い方一覧」は、partition by句でパーティションで分割した集合にも適用可能です。

 なお、PostgreSQLでは、bool_and関数とbool_or関数が実装されてます。bool_and関数が全称肯定命題に相当し、bool_or関数が存在肯定命題に相当しますので、下記のような別解もあります。

window関数で書き換えたSQL2
select team_id,member
from (select team_id,member,
      bool_and(status = '待機')
      over(partition by team_id) as willOut
        from Teams) a
where willOut;
group byを使ったSQL2
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のマニュアルです。ウィンドウ関数に関する説明です。

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2689 2009/10/04 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング