各ユーザーの所属グループの総組合せを求める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

Comments

[退会済みユーザー]

ID ４つで総数が 72 個になる。
ID が 1000 個もあったら総数は 300桁を超える数になる。
それを計算してどうするのでしょう。

[Orlofsky]

不要なPL/SQLタグは削除してください。
MariaDBはPL/SQLをサポートしていますが、MySQLはPL/SQLを使えません。
https://blog.s-style.co.jp/2018/04/1680/

Answer 1

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

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 2

パターンを横方向にもってはだめですか？

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'

Comments

[tsukin]

ありがとうございます。入力となるテーブル内のユーザーの数が可変（500とかになるかもしれない）なのです。
列数制限などもあるのでできれば縦に並べたいです。

[yambejp]

あとは専用のプロシージャをつくって処理するとか・・・