SQLで各種別の全組み合わせを表示したい

Question

前提・実現したいこと

下記のような、あるキャラクタの各種別とその値を格納しているテーブルがあるとすると、これらの種別の全組み合わせをSQLで出力したいのですが、うまく行かなくて困っています。

types

id	name
1	撃種
2	属性
3	戦型

values

id	type_id	value
1	1	反射
2	1	貫通
3	2	火
4	2	水
5	2	木
6	2	光
7	2	闇
8	3	パワー型
9	3	バランス型
10	3	スピード型
11	3	砲撃型

この２つのテーブルを使って下記のような全組み合わせ一覧を表示するSQLを作りたいです。

no	pattern
1	撃種:反射,属性:火,戦型:パワー型
2	撃種:反射,属性:火,戦型:バランス型
..	...
40	撃種:貫通,属性:闇,戦型:砲撃型

撃種2種 × 属性５種 × 戦型４種 ＝ ４０レコード

使用するデータ

postgreSQLのインポート用のSQLのサンプルを載せておきます。
試しているバージョンは、9.5です。

CREATE TABLE types
(
    id      SERIAL NOT NULL,
    type    VARCHAR
);
INSERT INTO types (type) VALUES ('撃種'),('属性'),('戦型');
CREATE TABLE values
(
    id      SERIAL NOT NULL,
    type_id INTEGER,
    value   VARCHAR
);
INSERT INTO values (type_id, value) VALUES
(1,'反射'),(1,'貫通'),
(2,'火'),(2,'水'),(2,'木'),(2,'光'),(2,'闇'),
(3,'パワー型'),(3,'バランス型'),(3,'スピード型'),(3,'砲撃型');

試したこと

WITH RECURSIVEを使うとできるのではと思ったのですが、今のところできていません。

https://www.postgresql.jp/document/9.5/html/queries-with.html

また、下記のようにPL/pgSQLで実現しようとしてみたのですが、今のところできていません。

DO
$$
DECLARE
  r1 RECORD;
  r2 RECORD;
BEGIN
  FOR r1 IN SELECT * FROM types LOOP
    FOR r2 IN SELECT * FROM values WHERE type_id = r1.id LOOP
      ...
    END LOOP;
  END LOOP;
END;
$$;

Answer 1

typesテーブルのidカラムは1,2,3…という連番で、抜けがない前提にたつと、以下のようなSQLで実現できるとは思います。

WITH RECURSIVE
P (id, value) AS (
  SELECT T.id, T.type || ':' || V.value
  FROM types T
  JOIN values V ON T.id = V.type_id
),
Q (id, pattern) AS (
    SELECT id, value FROM P WHERE id = 1
  UNION ALL
    SELECT P.id, pattern || ',' || P.value
    FROM P
    JOIN Q ON P.id = Q.id + 1
)
SELECT ROW_NUMBER() OVER() AS no, pattern
FROM Q
WHERE id = (SELECT MAX(id) FROM Q)

---

2021-08-18 10:11追記

typesテーブルのidカラムが連番ではないのであれば、DENSE_RANKを使って連番を生成するやり方があるかと思います。

WITH RECURSIVE
P (id, value) AS (
  SELECT DENSE_RANK() OVER(ORDER BY T.id), T.type || ':' || V.value
  FROM types T
  JOIN values V ON T.id = V.type_id
),
Q (id, pattern) AS (
    SELECT id, value FROM P WHERE id = 1
  UNION ALL
    SELECT P.id, pattern || ',' || P.value
    FROM P
    JOIN Q ON P.id = Q.id + 1
)
SELECT ROW_NUMBER() OVER() AS no, pattern
FROM Q
WHERE id = (SELECT MAX(id) FROM Q)

Comments

[mj29]

idカラムは連番でないのですが、それ以外は条件を満たしております。ありがとうございますmm
連番でなくてもOKな方法をもう少し模索してみます。

[mj29]

感動しました！ありがとうございますmm
理解に時間がかかりましたが、なるほどなSQLですね！

Answer 2

人により正解が違いそうな……私なら？

途中で動く様に、組み合わせ通し番号に各属性を持たせ、現時点では存在しない組み合わせが影響しない様に組むかな？

組み合わせとして被っても抜けても良い

「はじまりの村のシーンで登場する分」的なセットごとに動かしてテスト出来る設計にして、パッケージ等で最終統合？

Comments

[mj29]

おっしゃってる意味と合っているか自信がございませんが、実はその組み合わせのテーブルを作成するためのSQLが知りたくて質問しました。
ですので、組み合わせ通し番号と各属性のテーブル（今回なら40パターン x 3種別＝120レコード）の作成までがゴールだったりします。

[mj29]

結局組み合わせのテーブルにレコードを作成するところで行き詰まりまして、再度質問させてもらいましたmm
https://teratail.com/questions/357150

Answer 3

全パターンの組合せは直積(CROSS JOIN）を使用します。
こんな感じで。

with type_value as (
  select types.id, concat(types.type ,':', values.value) as type_name
  from types 
       inner join values
       on   types.id=values.type_id
) 
select  row_number() over() as no
     ,  concat_ws(',', type_value1.type_name, type_value2.type_name, type_value3.type_name) as pattern 
from  (select * from type_value where id =1 ) type_value1
      cross join (select * from type_value where id =2 ) type_value2
      cross join (select * from type_value where id =3 ) type_value3

Comments

[mj29]

種別の数は可変なので、なんとかtypes.idを指定しなくても動くようにしたいですが、最悪cross joinとPL/pgSQLでがんばればいけそうな気がしました。ありがとうございますmm