9-57 集合が等しい組み合わせを求める(複数列版)

SQLパズル

ValTable
Code  Val1  Val2
----  ----  ----
AAAA     1     2
AAAA     2     3
AAAA     3     3
BBBB     5     6
BBBB     7     8
CCCC     1     2
CCCC     2     3
CCCC     3     3
DDDD     5     6
DDDD     7     8
EEEE     1     2
EEEE     5     6
EEEE     6     5
FFFF     6     5
FFFF     7     8
GGGG     6     8
GGGG     7     5
HHHH     8     6
IIII     8     6

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

ValTableに重複行は存在しないものとする。

出力結果
S1    S2
----  ----
AAAA  CCCC
BBBB  DDDD
HHHH  IIII

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

データ作成スクリプト

create table ValTable(
Code char(4),
Val1 number(1),
Val2 number(1),
primary key(Code,Val1,Val2));

insert into ValTable
select 'AAAA',1,2 from dual union
select 'AAAA',2,3 from dual union
select 'AAAA',3,3 from dual union
select 'BBBB',5,6 from dual union
select 'BBBB',7,8 from dual union
select 'CCCC',1,2 from dual union
select 'CCCC',2,3 from dual union
select 'CCCC',3,3 from dual union
select 'DDDD',5,6 from dual union
select 'DDDD',7,8 from dual union
select 'EEEE',1,2 from dual union
select 'EEEE',5,6 from dual union
select 'EEEE',6,5 from dual union
select 'FFFF',6,5 from dual union
select 'FFFF',7,8 from dual union
select 'GGGG',6,8 from dual union
select 'GGGG',7,5 from dual union
select 'HHHH',8,6 from dual union
select 'IIII',8,6 from dual;
commit;

SQL

--■■■グループ化する方法■■■
select a.Code as s1,b.Code as s2
  from ValTable a,ValTable b
 where a.Code < b.Code
group by a.Code,b.Code
having count(case when a.Val1 = b.Val1
                   and a.Val2 = b.Val2
                  then 1 end)
= all(-1+Dense_Rank(null,null) within group(order by a.Val1,a.Val2),
      -1+Dense_Rank(null,null) within group(order by b.Val1,b.Val2));

--■■■グループ化しない方法■■■
select distinct c1,c2
from (select a.Code as c1,b.Code as c2,
      count(case when a.Val1 = b.Val1
                  and a.Val2 = b.Val2
                 then 1 end) over(partition by a.Code,b.Code) as cnt1,
      -1+dense_rank() over(partition by a.Code,b.Code order by a.Val1  asc,a.Val2  asc)
        +dense_rank() over(partition by a.Code,b.Code order by a.Val1 desc,a.Val2 desc) as cnt2,
      -1+dense_rank() over(partition by a.Code,b.Code order by a.Val1  asc,a.Val2  asc)
        +dense_rank() over(partition by a.Code,b.Code order by a.Val1 desc,a.Val2 desc) as cnt3
        from ValTable a,ValTable b
       where a.Code < b.Code)
where cnt1 = all(cnt2,cnt3)
order by c1,c2;

--■■■件数を内部結合の条件にする方法■■■
with work as(
select Code,Val1,Val2,count(*) over(partition by Code) as cnt
  from ValTable)
select a.Code as s1,b.Code as s2
  from work a,work b
 where a.Code < b.Code
   and a.cnt  = b.cnt
   and a.Val1 = b.Val1
   and a.Val2 = b.Val2
group by a.Code,b.Code,a.cnt
having count(*) = a.cnt
order by a.Code,b.Code;

解説

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

だそうですが

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

と、Ａ＝Ｂも同値です。

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

2-3-25 集合関数のdense_rank関数
10-311 紐づく集合の一致を調べる

---

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

---