CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃(完結編)

CodeZineに掲載されたSQLを分析関数で記述する 4

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2008/07/22 14:00

ダウンロード ソースコード (4.6 KB)

目次

全称文を述語で表現する

 次に全称文を述語で表現する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 <> '待機' );

 これを分析関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。

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

 手続き型の言語であれば、次のように処理を行うものと思われます。

  1. team_idの昇順にソート
  2. team_idの最小値からループ
  3. team_idがブレイクするまでに、statusが待機のデータがなかったら出力

 分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書き換えた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文のイメージは次のようになります。

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を使ったSQL
select team_id
  from Teams
group by team_id
having min(case when status = '待機' then 1 else 0 end) = 1;

 select文の実行時のイメージは、先ほどと同じです。

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

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


  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

All contents copyright © 2005-2022 Shoeisha Co., Ltd. All rights reserved. ver.1.5