9-56 集合が等しい組み合わせを求める

SQLパズル

SupPartsテーブル
sup  part
---  ------
AAA  ボルト
AAA  ナット
AAA  パイプ
BBB  ボルト
BBB  パイプ
CCC  ボルト
CCC  ナット
CCC  パイプ
DDD  ボルト
DDD  パイプ
EEE  ヒューズ
EEE  ナット
EEE  パイプ
FFF  ヒューズ

supごとの、partの集合の組み合わせで、
集合が等しい組み合わせを、
以下の形式で出力する。

SupPartsテーブルに重複行は存在しないものとする。

出力結果
S1   S2
---  ---
AAA  CCC
BBB  DDD

---

データ作成スクリプト

create table SupParts(sup,part) as
select 'AAA','ボルト'   union
select 'AAA','ナット'   union
select 'AAA','パイプ'   union
select 'BBB','ボルト'   union
select 'BBB','パイプ'   union
select 'CCC','ボルト'   union
select 'CCC','ナット'   union
select 'CCC','パイプ'   union
select 'DDD','ボルト'   union
select 'DDD','パイプ'   union
select 'EEE','ヒューズ' union
select 'EEE','ナット'   union
select 'EEE','パイプ'   union
select 'FFF','ヒューズ';

SQL

--■■■クロスジョインを使わない方法■■■
select a.sup,b.sup
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
order by a.sup,b.sup;

--■■■クロスジョインを使う方法■■■
select a.sup,b.sup
  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)
order by a.sup,b.sup;

--■■■配列型を使わない方法■■■
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)
order by a.sup,b.sup;

解説

配列型の使いどころでしょう。

PostgreSQLメモ 配列の比較に関する実験
PostgreSQLパズル 9-28 等しい集合が存在する、集合を求める

OracleSQLパズル 9-56 集合が等しい組み合わせを求める

---