3. 等しい部分集合を見つける
次に等しい部分集合を見つけるSQLについて説明します。『SQLで集合演算』では、以下のSQLが提示されています。
等しい部分集合を見つける
SELECT SP1.sup AS s1, SP2.sup AS s2
FROM SupParts SP1, SupParts SP2
WHERE SP1.sup < SP2.sup --業者の組み合わせを作る
AND SP1.part = SP2.part --条件1．同じ種類の部品を扱う
GROUP BY SP1.sup, SP2.sup
HAVING COUNT(*) = (SELECT COUNT(*) --条件2．同数の部品を扱う
FROM SupParts SP3
WHERE SP3.sup = SP1.sup)
AND COUNT(*) = (SELECT COUNT(*)
FROM SupParts SP4
WHERE SP4.sup = SP2.sup);
これをwindow関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
EmpSkills
| sup | part |
| A | ボルト |
| A | ナット |
| A | パイプ |
| B | ボルト |
| B | パイプ |
| C | ボルト |
| C | ナット |
| C | パイプ |
| D | ボルト |
| D | パイプ |
| E | ヒューズ |
| E | ナット |
| E | パイプ |
| F | ヒューズ |
出力結果
| s1 | s2 |
| A | C |
| B | D |
答えは、下記となります。
一致数と件数を比較
select a.sup as s1,b.sup as s2 from SupParts a,SupParts b where a.sup < b.sup group by a.sup,b.sup having count(case when a.part=b.part then 1 end) = count(distinct a.part) and count(case when a.part=b.part then 1 end) = count(distinct b.part);
上記のSQLでwhere句を評価した後のSQLのイメージ（24行目以降は割愛）は、下記となります。group by a.sup,b.supで赤線を引いてます。
SQLのイメージ
まず、自己結合によって業者の組み合わせを作った後、group by a.sup,b.supで業者の組み合わせごとにグループ化しています。その後、distinctオプションを指定したcount関数によって、そのグループの2つの業者の部品数を求め、count(case when a.part=b.part then 1 end)と等しいことをhaving句の条件としてます。
なお、ここでは前述した論理式を利用しています。
集合の相等性を調べる公式
（集合Aと集合Bの要素数が等しい） かつ （A ⊇ B） ⇔ A = B
別解として、with句でcount関数を使い、件数を求めておいて、内部結合後の件数と比較してもいいでしょう。
件数を求めておいて、内部結合後の件数と比較
with work as( select sup,part, count(*) over(partition by sup) as cnt from SupParts) select a.sup as s1,b.sup as s2 from work a,work b where a.sup < b.sup and a.part=b.part and a.cnt=b.cnt group by a.sup,a.cnt,b.sup having count(*) = a.cnt;
前問と同様に、array_agg関数を使う方法もあります。比較する集合が1列しかない場合は、array_agg関数が非常に強力です。比較する集合が複数列でも、dense_rank関数を使用して1列に対応させてから、array_agg関数を使うこともできます。
array_agg関数を使用した別解1
select a.sup as s1,b.sup as s2 from SupParts a,SupParts b where a.sup < b.sup group by a.sup,b.sup having array_agg(a.part) <@ array_agg(b.part) and array_agg(a.part) @> array_agg(b.part);
array_agg関数を使用した別解2
select a.sup as s1,b.sup as s2
from (select sup,array_agg(part) as arrayPart
from SupParts
group by sup) a,SupParts b
where a.sup < b.sup
group by a.sup,a.arrayPart,b.sup
having array_agg(b.part) <@ a.arrayPart
and array_agg(b.part) @> a.arrayPart;
