My Photo

« Webアプリケーションの機能と脆弱性の対応 | Main | 入力処理とセキュリティ »

August 19, 2013

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
1Suzuki
2Tanaka
3Yamashita
customerTagRelation
customerID tagID
11
12
32

下記は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」カテゴリの記事

SQL」カテゴリの記事

Comments

Post a comment

(Not displayed with comment.)

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関数のようなことをする:

« Webアプリケーションの機能と脆弱性の対応 | Main | 入力処理とセキュリティ »

April 2017
Sun Mon Tue Wed Thu Fri Sat
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30            
無料ブログはココログ

日本blog村

  • にほんブログ村 IT技術ブログへ
  • にほんブログ村 アニメブログへ
  • にほんブログ村 サッカーブログ アルビレックス新潟へ

好きな音楽家

メモ

XI-Prof