分析関数の衝撃（完結編）

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

関係除算を表現する

　次に関係除算を表現する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テーブル

Emp	Skill
相田	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でグループ化した状態のイメージ

　この後、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でグループ化した状態のイメージ

　having句では、decode関数を使ってa.Skillとb.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);