CodeZine(コードジン)

特集ページ一覧

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

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

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

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

目次

関係除算を表現する

 次に関係除算を表現するSQLについて考えます。「SQLで集合演算」では、以下のSQLが提示されています。

差集合で関係除算(剰余を持った除算)
SELECT DISTINCT emp
  FROM EmpSkills ES1
 WHERE NOT EXISTS
        (SELECT skill
           FROM Skills
         minus
         SELECT skill
           FROM EmpSkills ES2
          WHERE ES1.emp = ES2.emp);

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

Skillsテーブル
Skill
Oracle
UNIX
Java
EmpSkillsテーブル
EmpSkill
相田Oracle
相田UNIX
相田Java
相田C#
神崎Oracle
神崎UNIX
神崎Java
平井UNIX
平井Oracle
平井PHP
平井Perl
平井C++
若田部Perl
渡来Oracle
出力結果
Emp
相田
神崎

 手続き型の言語であれば、次のような手順となるでしょう。

  1. SkillsテーブルのSkillを配列に保存し、それぞれにフラグを用意
  2. EmpSkillsテーブルをEmpの昇順にソート
  3. 配列に保存したSkillsテーブルのSkillのフラグを初期化
  4. EmpSkillsテーブルをEmpの最小値からループ開始
  5. 配列に保存したSkillsテーブルのSkillにフラグをつける
  6. EmpSkillsテーブルのEmpがブレイクしたら、3に戻る

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

分析関数で書き換えたSQL(Skillsテーブルが空集合の場合に非対応)
select a.Emp
  from EmpSkills a,(select Skill,
                    count(*) over() as SkillCount
                      from Skills) b
 where a.Skill = b.Skill
group by a.Emp,b.SkillCount
having count(*) = b.SkillCount;

 where句で内部結合して、group byでグループ化を行った後のイメージは、次のようになります。

group byでグループ化した状態のイメージ
group byでグループ化した状態のイメージ

 この後、having句の条件としてcount(*) = b.SkillCountを指定しています。必要なスキルの数を示すb.SkillCountは全て3ですから、相田さんと神埼さんのみが返されます。ただし、Skillsテーブルが空集合の場合、上記のSQLではデータが出力されません。これを考慮したSQLは次のようになります。

分析関数で書き換えたSQL(Skillsテーブルが空集合の場合に対応)
select a.Emp
  from EmpSkills a Left Join (select Skill,
                              count(*) over() as needCount
                                from Skills) b
                     on 1=1
group by a.Emp,b.needCount
having count(decode(a.Skill,b.Skill,1)) = nvl(b.needCount,0);

 Left Join句で外部結合して、group byでグループ化を行った後のイメージは、次のようになります(27行目以降は割愛)。

group byでグループ化した状態のイメージ
group byでグループ化した状態のイメージ

 having句では、decode関数を使ってa.Skillb.Skillが一致するレコードの数と、Skillsテーブルのレコードの数が等しいか判定しています。これは、count関数がnullをカウント対象としないことへの考慮です。

 外部結合の結合条件には1=1を指定し、判定結果が必ずtrueとなるように記述しています。Skillsテーブルが空集合でない場合は、全ての行と結合した結合結果(クロスジョインと同じ結合結果)が返されます。また、Skillsテーブルが空集合の場合は、結合先の情報をnullとした結合結果が返される仕組みです。

 Skillsテーブルが空集合の場合は、having句の条件式の左辺のcount(decode(a.Skill,b.Skill,1))0、右辺のnvl(b.needCount,0)0となるため、having句において等号が成立し、EmpSkillsテーブルの全てのEmpが出力されます。

 Oracle10gの新機能「Partitioned Outer Join」を使用した別解(Skillsテーブルが空集合の場合に非対応)も記載しておきます。

Partitioned Outer Joinを使用した別解
select b.Emp
  from Skills a
  Left Join EmpSkills b
 partition by (b.Emp)
    on (a.Skill = b.Skill)
group by b.Emp
having count(*) = count(b.Skill);

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

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

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