9-28 等しい集合が存在する、集合を求める

SQLパズル

PartsTable1    PartsTable2
ID   Val       ID   Val
---  ---       ---  ---
AAA   10       AAA   10
AAA   20       AAA   30
BBB   10       BBB   10
BBB   40       BBB   40
CCC   40       CCC   30
CCC   50       DDD   60
DDD   60       DDD   70
EEE   80       EEE   80
FFF   10       FFF   10
FFF   70       FFF   30
GGG   20

PartsTable1とPartsTable2の、IDごとの集合で
互いに等しい集合を出力する。

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

出力結果
ID   Val
---  ---
BBB   10
BBB   40
EEE   80

SQLパズル(日本語版)のパズル26 [等集合の発見] を参考にさせていただきました
SQLパズル 第2版のパズル27 [等しい集合を見つける] を参考にさせていただきました

データ作成スクリプト

create table PartsTable1(ID,Val) as
select 'AAA',10 union
select 'AAA',20 union
select 'BBB',10 union
select 'BBB',40 union
select 'CCC',40 union
select 'CCC',50 union
select 'DDD',60 union
select 'EEE',80 union
select 'FFF',10 union
select 'FFF',70 union
select 'GGG',20;

create table PartsTable2(ID,Val) as
select 'AAA',10 union
select 'AAA',30 union
select 'BBB',10 union
select 'BBB',40 union
select 'CCC',30 union
select 'DDD',60 union
select 'DDD',70 union
select 'EEE',80 union
select 'FFF',10 union
select 'FFF',30;

SQL

select ID,Val
from (select ID,Val,array_agg(Val) over(partition by ID) as arrayVal
        from PartsTable1) a
where exists(select 1 from PartsTable2 b
              where b.ID = a.ID
             having array_agg(b.Val) <@ a.arrayVal
                and array_agg(b.Val) @> a.arrayVal)
order by ID,Val;

解説

集合Aと集合Bが互いに包含関係を持つなら、
集合Aと集合Bは等しいという数学を使ってます。

単純に=演算子で配列同士を比較すると、要素の順序まで見るので
この場合には、=演算子は使えません。

select array[1,2,3] = array[3,2,1];
 ?column?
----------
 f

9.17. 配列関数と演算子

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

---