PostgreSQLでMySQLのgroup_concat関数のようなことをする

MySQLには group_concat という関数があってとても便利そう。
例えば1:nの親子関係のテーブルがあって連結して検索すると親の主キーに対して複数のレコードが検索される場合、親の主キーに対して1レコードとなる検索をしたい場合に使える。

PostgreSQLでは9.0で string_agg という MySQLのgroup_concatに相当する関数が追加された。
PostgreSQL 9.1 集約関数

9.0以前では配列を使って同じような結果を得ることができる。

customers と customerTagRelation というテーブルを例にして考える。
データは以下のようになっている。

customers

customerID	customerName
1	Suzuki
2	Tanaka
3	Yamashita

customerTagRelation

customerID	tagID
1	1
1	2
3	2

下記はcustomerTagRelation に対して自己相関サブクエリ？で customerID毎にカンマ区切りのtagIDを取得するSQLの例。customerID毎にするためにDISTINCTを付けている。customerTagRelation だけを検索しているので、クエリの結果に customerTagRelation にない customerID = 2 のレコードはない。

--SQLその1
SELECT DISTINCT a.customerID,
                ARRAY_TO_STRING(
                  ARRAY(SELECT b.tagID
                        FROM customerTagRelation b
                        WHERE b.customerID = a.customerID ), ',')
FROM customerTagRelation a
ORDER BY a.customerID;

実行結果は以下。

 customerid | array_to_string
------------+-----------------
          1 | 1,2
          3 | 2
(2 行)

次いで、customers を検索して customerID,customerName毎にカンマ区切りのtagIDを取得するSQL。SELECT句で相関サブクエリを使用する。

--SQLその2
SELECT t1.customerID, t1.customerName,
       (SELECT ARRAY_TO_STRING(
           ARRAY(SELECT t2.tagID FROM customerTagRelation t2
                 WHERE t2.customerID = t1.customerID ), ',')) AS text_csv
FROM customers t1
ORDER BY t1.customerID;

実行結果は以下。

 customerid | customername | text_csv
------------+--------------+----------
          1 | Suzuki       | 1,2
          2 | Tanaka       |
          3 | Yamashita    | 2
(3 行)

上記の配列を使う方法の他には ARRAY_AGG 関数を使う方法がある（8.4以降）。
ARRAY_AGG 関数は集約関数で、値をカンマで連結して波括弧で括ったvarchar型の値を返す。集約関数なので GROUP BY句を使う。
ARRAY_AGG は配列を返すので ARRAY_TO_STRING 関数で文字列にしている。前記の ARRAY と ARRAY_TO_STRING を使う方法よりわかりやすいように思う。8.4以降ならば ARRAY_AGG を使った方がいいだろう。

SQLその1とほぼ同じ検索をするARRAY_AGGを使ったSQL。
ARRAY_AGG は出力形式の変更はできないようだ。

SELECT customerID, ARRAY_TO_STRING(ARRAY_AGG(tagID), ',') AS tag
FROM customerTagRelation
GROUP BY customerID;

実行結果は以下。

 customerid | tag
------------+-----
          1 | 1,2
          3 | 2
(2 行)

SQLその2とほぼ同じ検索をする ARRAY_AGG 関数を使ったSQL。配列を使用した場合よりわかりやすい。デメリットは ARRAY_AGG を使っているので出力形式が固定になること。"{NULL}" はなんかあまり美しくない感じがする。

SELECT t1.customerID
     , MAX(t1.customerName) AS name
     , ARRAY_TO_STRING(ARRAY_AGG(t2.tagID), ',') AS tag
FROM customers t1
LEFT OUTER JOIN customerTagRelation t2
ON t1.customerID = t2.customerID
GROUP BY t1.customerID;

実行結果は以下。

 customerid |   name    | tag
------------+-----------+-----
          1 | Suzuki    | 1,2
          2 | Tanaka    |
          3 | Yamashita | 2
(3 行)

参考ページ：
GROUP_CONCAT関数の便利さは異常 - 開発の風景　～KKZのSE日記～
複数行返ってくるサブクエリを文字列に連結して取得するSQLまとめ - hikky nikky
複数行の結果を単一列に連結 - PostgreSQL - 教えて！goo
PostgreSQL 9.0 のその他の新機能 — Let's Postgres
集約関数