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 というテーブルを例にして考える。
データは以下のようになっている。
customerID | customerName |
---|---|
1 | Suzuki |
2 | Tanaka |
3 | Yamashita |
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
集約関数
« Webアプリケーションの機能と脆弱性の対応 | Main | 入力処理とセキュリティ »
「PostgreSQL」カテゴリの記事
- CakePHPのインストール(2015.06.14)
- PHPからPDOでPostgreSQLに接続する(2015.06.09)
- PostgreSQLのインストール その2(2015.03.29)
- PostgreSQLのインストール その1(2015.03.23)
- 列の値を使って日時に「+n日」する(2015.02.04)
「SQL」カテゴリの記事
- 列の値を使って日時に「+n日」する(2015.02.04)
- PostgreSQLでMySQLのgroup_concat関数のようなことをする(2013.08.19)
- TIMESTAMP WITH TIME ZONE型(2014.07.20)
- 共通表式(共通テーブル式)(2014.02.14)
- PL/pgSQL(2014.02.16)
TrackBack
TrackBack URL for this entry:
http://app.cocolog-nifty.com/t/trackback/26461/58025308
Listed below are links to weblogs that reference PostgreSQLでMySQLのgroup_concat関数のようなことをする:
Comments