各ユーザーの所属グループの総組合せを求めるSQL

Question

次のようなテーブルがMySQL内に存在する場合、各IDの所属するグループの総組合せのパターンをMySQL上で処理して作成したいです（ストアド・プロシージャ or SQL）。
前提として、IDの数は可変（1000を超えるかもしれない）です。

|ID|グループ|同ID件数|同ID通し番号|
|:--|:--:|--:|--:|
|A|1|3|1|
|A|2|3|2|
|A|3|3|3|
|B|3|2|1|
|B|4|2|2|
|C|1|4|1|
|C|2|4|2|
|C|4|4|3|
|C|5|4|4|
|D|2|3|1|
|D|3|3|2|
|D|4|3|3|

出力イメージとしては下記のようなテーブルをイメージしています。
|パターン|ID|グループ|
|:--|:--:|--:|
|1|A|1|
|1|B|3|
|1|C|1|
|1|D|2|
|2|A|1|
|2|B|3|
|2|C|1|
|2|D|3|
|3|A|1|
|3|B|3|
|3|C|1|
|3|D|4|
|途中略|||
|72|A|3|
|72|B|4|
|72|C|5|
|72|D|4|

Accepted Answer

おそらく以下のようなクエリで実現できると思います。

```sql
WITH RECURSIVE 
A (シーケンス番号, 組合せ) AS (
    SELECT 0 , JSON_ARRAY()
  UNION ALL
    SELECT B.シーケンス番号, JSON_ARRAY_APPEND(A.組合せ, '$', JSON_OBJECT('ID', B.ID, 'グループ', B.グループ))
    FROM A
    JOIN (SELECT T.ID, T.グループ, DENSE_RANK() OVER (ORDER BY ID) AS シーケンス番号 FROM T) AS B 
    ON A.シーケンス番号 + 1 = B.シーケンス番号
),
B (インデックス) AS (
    SELECT 0
  UNION ALL
    SELECT インデックス + 1
    FROM B
    WHERE インデックス + 1 < (SELECT MAX(JSON_LENGTH(組合せ)) FROM A)
)
SELECT 
  C.パターン, 
  JSON_UNQUOTE(JSON_EXTRACT(C.組合せ, CONCAT('$[', B.インデックス, '].ID'))) AS ID,
  JSON_EXTRACT(C.組合せ, CONCAT('$[', B.インデックス, '].グループ')) AS グループ
FROM (
  SELECT 
    組合せ, 
    ROW_NUMBER() OVER(PARTITION BY JSON_LENGTH(組合せ)) AS パターン, 
    DENSE_RANK() OVER(ORDER BY JSON_LENGTH(組合せ) DESC) AS レベル
  FROM A
) C
JOIN B ON C.レベル = 1
ORDER BY パターン, ID
```

IDが1000を超える可能性があるとのことですが、たとえば1IDあたり2グループに属するとすると、組み合わせの総数は2の1000乗(≧10の300乗)となり、事実上有限時間内では処理できないと思います。計算量爆発というやつですね。どういう業務を想定されているのかわからないのですが、処理数に上限を設ける、枝刈により処理数を減らす、そもそも業務自体を見直すなどの工夫が必要になるかと思います。

Answer

パターンを横方向にもってはだめですか？
```SQL
create table tbl (id varchar(10),grp int);
insert into tbl values
('A',1),
('A',2),
('A',3),
('B',3),
('B',4),
('C',1),
('C',2),
('C',4),
('C',5),
('D',2),
('D',3),
('D',4);

select * from 
tbl as t1,
tbl as t2,
tbl as t3,
tbl as t4
where 1
and t1.id='A' 
and t2.id='B'
and t3.id='C'
and t4.id='D'
```

パターン	ID	グループ
1	A	1
1	B	3
1	C	1
1	D	2
2	A	1
2	B	3
2	C	1
2	D	3
3	A	1
3	B	3
3	C	1
3	D	4
途中略
72	A	3
72	B	4
72	C	5
72	D	4

ID	グループ	同ID件数	同ID通し番号
A	1	3	1
A	2	3	2
A	3	3	3
B	3	2	1
B	4	2	2
C	1	4	1
C	2	4	2
C	4	4	3
C	5	4	4
D	2	3	1
D	3	3	2
D	4	3	3

関連した質問