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

9-27 集合の包含関係を調べるのアレンジです

データ作成スクリプト

create table SupParts(
sup  char(3),
part varchar2(8),
primary key(sup,part));

insert into SupParts
select 'AAA','ボルト' from dual
union select 'AAA','ナット'   from dual
union select 'AAA','パイプ'   from dual
union select 'BBB','ボルト'   from dual
union select 'BBB','パイプ'   from dual
union select 'CCC','ボルト'   from dual
union select 'CCC','ナット'   from dual
union select 'CCC','パイプ'   from dual
union select 'DDD','ボルト'   from dual
union select 'DDD','パイプ'   from dual
union select 'EEE','ヒューズ' from dual
union select 'EEE','ナット'   from dual
union select 'EEE','パイプ'   from dual
union select 'FFF','ヒューズ' from dual;
commit;

SQL

--■■■分析関数を使う方法■■■
select distinct s1,s2
from (select a.sup as s1,b.sup as s2,
      count(distinct a.part) over(partition by a.sup,b.sup) as SupCount1,
      count(distinct b.part) over(partition by a.sup,b.sup) as SupCount2,
      count(decode(a.part,b.part,1))
      over(partition by a.sup,b.sup) as ExistSum
        from SupParts a,SupParts b
       where a.sup < b.sup)
where ExistSum = all(SupCount1,SupCount2);

--■■■分析関数を使わない方法■■■
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(decode(a.part,b.part,1))
     = all(count(distinct a.part),count(distinct b.part));

--■■■件数を内部結合の条件にする方法■■■
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.cnt = b.cnt
   and a.part = b.part
group by a.sup,b.sup,a.cnt
having count(*) = a.cnt
order by a.sup,b.sup;

解説

集合の一致
ふたつの集合ＡとＢに対して、ＡとＢとが等しいとは、
Ａ⊂Ｂ　と　Ｂ⊂Ａ　が両方とも成り立つときをいう。
またこのときＡ＝Ｂと書く。

だそうですが

(集合Ａに重複した要素が存在しない、かつ、集合Ｂに重複した要素が存在しない)
かつ
(Ａ⊂Ｂ、または、Ｂ⊃Ａが成立する)
かつ
集合Ａと集合Ｂの要素数が等しい

と、Ａ＝Ｂも同値です。

という論理を使ってます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
・count(distinct a.part) = count(distinct b.part) によって、要素数が等しい
・count(decode(a.part,b.part,1) = count(distinct a.part) によって、包含関係が成立

having句で、上記2つの論理積が真となる組み合わせを抽出しているのです。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
集合が等しいことの必要十分条件の証明
数学的に厳密に正しい証明かは、分かりません。

----------------------------------------------------------
集合Aと集合Bの要素数が等しい。 すなわち  n(A) = n(B)
かつ
少なくとも1つの包含関係が成立する。 すなわち  A⊂B または A⊃B
----------------------------------------------------------

集合Aと集合Bが等しいパターン1
(要素数は一致する。
片方の包含関係でいいのですが、互いに包含関係が成立)
SetA    SetB
-----   -----
Item1   Item1
Item2   Item2

集合Aと集合Bが等しいパターン2
(要素数は一致する。
片方の包含関係でいいのですが、互いに包含関係が成立)
SetA    SetB
-----   -----
空集合   空集合

集合Aと集合Bが等しくないパターン1
(要素数は一致するが、どちらの包含関係も成立せず)
SetA    SetB
-----   -----
Item1   Item1
Item2   Item3

集合Aと集合Bが等しくないパターン2
(要素数は一致するが、どちらの包含関係も成立せず)
SetA    SetB
-----   -----
Item1   Item3
Item2   Item4

集合Aと集合Bが等しくないパターン3
(片方の包含関係が成立するが、要素数が一致せず)
SetA    SetB
-----   -----
Item1   Item1
Item2   Item2
        Item3

集合Aと集合Bが等しくないパターン4
(片方の包含関係が成立するが、要素数が一致せず)
SetA    SetB
-----   ------
Item1    空集合
Item2

10-311 紐づく集合の一致を調べる

CodeZine：SQLで集合演算
CodeZine：分析関数の衝撃（完結編）

OTNのスレッド