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

array_agg関数とbool_and関数の使用例

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

Skills

Skill

Oracle

UNIX

Java

EmpSkills

Emp	Skill
相田	Oracle
相田	UNIX
相田	Java
相田	C#
神崎	Oracle
神崎	UNIX
神崎	Java
平井	UNIX
平井	Oracle
平井	PHP
平井	Perl
平井	C++
若田	Perl
渡来	Oracle

出力結果

Emp

相田

神崎

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

window関数で書き換えた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 a.Emp,b.SkillCountで赤線を引いてます。

SQLのイメージ

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

window関数で書き換えた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(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のイメージ

　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関数が非常に強力です。

array_agg関数を使用した別解1（Skillsテーブルが空集合の場合に対応）

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句で使ってもいいです。

array_agg関数を使用した別解2（Skillsテーブルが空集合の場合に対応）

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);