2. 関係除算を表現する
次に関係除算を表現するSQLについて考えます。『SQLで集合演算』では、以下のSQLが提示されています。
SELECT DISTINCT emp FROM EmpSkills ES1 WHERE NOT EXISTS (SELECT skill FROM Skills EXCEPT SELECT skill FROM EmpSkills ES2 WHERE ES1.emp = ES2.emp);
これをwindow
関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
Skill |
Oracle |
UNIX |
Java |
Emp | Skill |
相田 | Oracle |
相田 | UNIX |
相田 | Java |
相田 | C# |
神崎 | Oracle |
神崎 | UNIX |
神崎 | Java |
平井 | UNIX |
平井 | Oracle |
平井 | PHP |
平井 | Perl |
平井 | C++ |
若田 | Perl |
渡来 | Oracle |
Emp |
相田 |
神崎 |
答えは、下記となります。
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 a.Emp,b.SkillCount
で赤線を引いてます。
![SQLのイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/2689/fig01.gif)
この後、having
句の条件としてcount(*) = b.SkillCount
を指定しています。必要なスキルの数を示すb.SkillCountは全て3ですから、相田さんと神埼さんのみが返されます。ただし、Skillsテーブルが空集合の場合、上記のSQLではデータが出力されません。これを考慮したSQLは下記のようになります。
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(case when a.Skill=b.Skill then 1 end) = coalesce(b.needCount,0);
Left Join
句による外部結合とgroup by
句によるグループ化を行った後のイメージは、次のようになります(28行目以降は割愛)。group by a.Emp,b.needCount
で赤線を引いてます。
![SQLのイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/2689/fig02.gif)
having
句では、case
式を使ってa.Skillとb.Skillが一致するレコードの数と、Skillsテーブルのレコードの数が等しいか判定しています。これは、count
関数がnullをカウント対象としないことへの考慮です。
外部結合の結合条件には1=1
を指定し、判定結果が必ずtrueとなるように記述しています。Skillsテーブルが空集合でない場合は、全ての行と結合した結合結果(クロスジョインと同じ結合結果)が返されます。また、Skillsテーブルが空集合の場合は、結合先の情報をnullとした結合結果が返される仕組みです。
Skillsテーブルが空集合の場合は、having
句の条件式の左辺のcount(case when a.Skill=b.Skill then 1 end)
は0、右辺のcoalesce(b.needCount,0)
も0となるため、having
句において等号が成立し、EmpSkillsテーブルの全てのEmpが出力されます。
PostgreSQL8.4の新機能である、array_agg
関数を使う方法もあります。比較する集合が1列しかない場合は、array_agg
関数が非常に強力です。
select a.Emp from EmpSkills a group by Emp having exists(select 1 from Skills b having count(*) = 0 or array_agg(b.Skill) <@ array_agg(a.Skill));
下記のように、boolean
型を返すスカラーサブクエリをhaving
句で使ってもいいです。
select a.Emp from EmpSkills a group by Emp having (select count(*) = 0 or array_agg(b.Skill) <@ array_agg(a.Skill) from Skills b);