PostgreSQL

PostgreSQL で MySQL の GROUP_CONCAT() 関数を実現する


GROUP_CONCAT() 関数とは

MySQL を使ったことのある方であればご存知かもしれませんが、MySQL には GROUP_CONCAT() という非常に便利な関数があります。これはデータをグループ化した上で付随するデータを全部まとめてくれる便利な関数です。

これを PostgreSQL でも使いたい！ということで、PostgreSQL で同じ 結果を取得できるようにしてみましたので以下に記載します。




MySQL における GROUP_CONCAT() 関数

まず MySQL の GROUP_CONCAT() 関数がどのようなものなのか実際に 試してみましょう。

以下ではテスト用のテーブルを作成し、そのテーブルに６件のデータを登録します。 しかし６件中３件は同じ ID を持つレコードとしています。 
mysql> CREATE TABLE test (id int, name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> 
INSERT INTO test
  (id, name)
VALUES
  (1, 'red'),
  (2, 'blue'),
  (3, 'yellow');

INSERT INTO test
  (id, name)
VALUES
  (1, 'cat'),
  (2, 'dog'),
  (3, 'bird');

mysql> SELECT id, name FROM test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | red    |
|    2 | blue   |
|    3 | yellow |
|    1 | cat    |
|    2 | dog    |
|    3 | bird   |
+------+--------+
6 rows in set (0.00 sec)

上記のように６件のデータが登録されており、id がいくつか重複しています。
では早速 MySQL の GROUP_CONCAT() 関数を呼び出してみましょう。

ここでは "GROUP BY id" によって id 毎にグループ化しています。 
mysql> SELECT id, GROUP_CONCAT(name separator ',') FROM test GROUP BY id;
+------+----------------------------------+
| id   | GROUP_CONCAT(name separator ',') |
+------+----------------------------------+
|    1 | red,cat                          |
|    2 | blue,dog                         |
|    3 | yellow,bird                      |
+------+----------------------------------+
3 rows in set (0.01 sec)

すると id 毎にグループ化されると共に、それに付随するデータも一緒に全部 取得できていますね。

しかし私の知る限り PostgreSQL には同等の関数が存在しません。 ですので同じことが出来る方法を探ってみます。


PostgreSQL でも MySQL の GROUP_CONCAT() 関数と同等の結果を得る

では PostgreSQL で MySQL の GROUP_CONCAT() 関数を同じことを実現してみます。

まず最初に先の MySQL の例と同じテーブル及びデータを準備します

=# CREATE TABLE test (id int, name text);
CREATE TABLE

INSERT INTO test
  (id, name)
VALUES
  (1, 'red'),
  (2, 'blue'),
  (3, 'yellow');

INSERT INTO test
  (id, name)
VALUES
  (1, 'cat'),
  (2, 'dog'),
  (3, 'bird');

=# SELECT id, name FROM test;
 id |  name
----+--------
  1 | red
  2 | blue
  3 | yellow
  1 | cat
  2 | dog
  3 | bird
(6 rows)


PostgreSQL で GROUP_CONCAT() と同等処理を実行

MySQL の GROUP_CONCAT() 関数と同等処理を PostgreSQL で実行するには、 最終的に PostgreSQL の配列操作関数をいくつか使う必要がありました。
  1. array_agg(name) で name の値を集約して配列化
  2. unnest(array_agg(name)) で配列化したデータを行として展開
  3. 行として展開されたデータを ARRAY() 関数で配列化
  4. 配列化されたデータを array_to_string() 関数で文字列化
上記工程を経て、最終的に以下の結果が得られます。またこの結果は MySQL の GROUP_CONCAT() 関数と等価ですね。 
SELECT
  id,
  array_to_string(ARRAY(SELECT unnest(array_agg(name))), ',')
FROM test GROUP BY id ORDER BY id;

 id | array_to_string
----+-----------------
  1 | red,cat
  2 | blue,dog
  3 | yellow,bird
(3 rows)

なお MySQL の GROUP_CONCAT() 関数もそうですが ORDER BY 句を使って 値を並べ替えることも出来ます。 

SELECT
  id,
  array_to_string(ARRAY(SELECT unnest(array_agg(name)) ORDER BY 1), ',')
FROM test GROUP BY id ORDER BY id;

 id | array_to_string
----+-----------------
  1 | cat,red
  2 | blue,dog
  3 | bird,yellow
(3 rows)


何とか同等の結果は得られましたが、これは将来の PostgreSQL バージョンアップによって もっと簡単に実行できることを願います。