Postgres クエリ結果を一列に表示する

1. 目的

あるクエリの結果。

poke_name	waza
ピカチュウ	でんこうせっか
ピカチュウ	十万ボルト
コラッタ	でんこうせっか
コラッタ	しっぽをふる

これを
下のように一発で取りたい

poke_name	wazas
ピカチュウ	でんこうせっか,十万ボルト
コラッタ	でんこうせっか,しっぽをふる

2. DB構成

ポケモンテーブル

poke	type
pk	serial primary key
name	varchar(20)

技テーブル

waza	type
pk	serial primary key
name	varchar(20)

ポケモンと技のテーブル

poke_waza	type
poke_key	integer
waza_key	integer

3. 結論

SELECT 
    p.name AS poke_name, 
    array_to_string(
        ARRAY(
            SELECT w.name
            FROM poke_waza pw
                JOIN waza w ON w.pk = pw.waza_key
            WHERE p.pk = pw.poke_key
        ), ','
    ) AS wazas
FROM poke p

結果

poke_name	wazas
ピカチュウ	でんこうせっか,十万ボルト
コラッタ	でんこうせっか,しっぽをふる

何も考えずにとる

SELECT 
    p.name AS poke_name, 
    w.name AS waza_name
FROM poke_waza pw
    JOIN poke p ON p.pk = pw.poke_key
    JOIN waza w ON w.pk = pw.waza_key 

poke_name	waza
ピカチュウ	でんこうせっか
ピカチュウ	十万ボルト
コラッタ	でんこうせっか
コラッタ	しっぽをふる

4. ざっくりと解説

メモ程度です。
array()
array_to_string()

4-1. ARRAY()

内側から。
クエリの結果が配列として返るらしい。

-- イメージ
SELECT 
    p.name AS poke_name, -- 例えば「ピカチュウ」
    array_to_string(
        ['でんこうせっか','十万ボルト'], 
        ','
    ) AS wazas
FROM poke p    

4-2. array_to_string()

引数	説明
第一引数	配列
第二引数	分割する文字
第三引数	Nullのときに代わりとなる文字

-- イメージ
SELECT 
    p.name AS poke_name, -- 例えば「ピカチュウ」
    'でんこうせっか,十万ボルト' AS wazas
FROM poke p

5. 経緯

ピカチュウ行が重複するのが嫌で、
ピカチュウの技リストを取得するクエリを使っていた。
そうすると、もちろんコラッタに対しても同じクエリを使う。

ポケモンのリスト（30匹にLIMITしていた）をとってくるクエリを1回、
それぞれの技リストを取得するので30回
クエリを発行していた。

10秒くらいかかってクエリの発行回数が多いことに気が付いた。

6. 参考

感想

1.3秒くらいになってとりあえず良しとする。
DBはまだまだ勉強することが多いなあ。

ピカチュウは例えですえ