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

データ作成スクリプト

create table ValTable(
Code text,
Val1 integer,
Val2 integer,
primary key(Code,Val1,Val2));

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

SQL

--■■■内部結合を使う方法■■■
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,a.cnt,b.Code
having count(*) = a.cnt
order by a.Code,b.Code;

--■■■dense_rank関数でIDを振る方法■■■
with work as(
select Code,
dense_rank() over(order by Val1,Val2) as CID
  from ValTable)
select a.Code,b.Code
  from work a,work b
 where a.Code < b.Code
group by a.Code,b.Code
having array_agg(a.CID) <@ array_agg(b.CID)
   and array_agg(a.CID) @> array_agg(b.CID)
order by a.Code,b.Code;

--■■■行コンストラクタとarray_agg関数を使う方法■■■
select a.Code,b.Code
  from ValTable a,ValTable b
 where a.Code < b.Code
group by a.Code,b.Code
having array_agg(ROW(a.Val1, a.Val2)) @> array_agg(ROW(b.Val1, b.Val2))
   and array_agg(ROW(a.Val1, a.Val2)) <@ array_agg(ROW(b.Val1, b.Val2))
order by a.Code,b.Code;

解説

複数列だと、array_agg関数を使ってもうまくいかないようですねぇ
array_agg(array[列1,列2])は、文法エラーでした。(array_agg関数の引数で配列型が使えないため)
(dense_rank関数で列の組み合わせと1対1となるIDを付与すればいいようですが)

SELECT proname, proargtypes, prosrc FROM pg_proc WHERE proname = 'array_agg';

  proname  | proargtypes |     prosrc
-----------+-------------+-----------------
 array_agg | 2283        | aggregate_dummy

SELECT oid, typname FROM pg_type WHERE oid IN (2283);

 oid  |  typname
------+------------
 2283 | anyelement

34.2.5. 多様型
特殊な用途を持つ疑似型には、anyelementとanyarray、anynonarray、およびanyenumの4つがあります。
これらはまとめて多様型と呼ばれます。これらの型を使用すると宣言された関数は全て、多様関数と呼ばれます。
多様関数は多くの異なるデータ型を操作することができます。
データ型の指定は、特定の呼び出しに実際に渡されるデータ型によって決定されます。

多様引数と結果は互いに結び付いており、多様関数を呼び出す問い合わせが解析される時に特定のデータ型が決定されます。
anyelementとして宣言された位置（引数もしくは戻り値）にはそれぞれ、任意の実データ型を指定することができますが、
1つの呼び出しでは、これら全ては同一の実データ型でなければなりません。
anyarrayとして宣言された位置には、任意の配列データ型を持つことができます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
複数列だと行コンストラクタの結果の配列を使えばいいようです。

-- ■■■ 行コンストラクタとarray_agg関数の使用例 ■■■
postgres=# with tmp(numVal,StrVal) as(
postgres(# values(11,'ab'),
postgres(#       (22,'cd'))
postgres-# select array_agg(Row(numVal,StrVal)) from tmp;
       array_agg
-----------------------
 {"(11,ab)","(22,cd)"}

4.2.11. 行コンストラクタ
9.20.5. 行に関しての比較

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

---