PostgreSQLの便利関数

今回は仕事でよく使っているPostgreSQLの関数を紹介します
下記のようなデータを使用します

これから下記のようなデータを抽出したい場合

A	1,2018/01/01	2,2018/01/20	3,2018/02/15	4,2018/03/01

1,2018/01/01

2,2018/01/20

3,2018/02/15

4,2018/03/01

まずは基本的なSQLで名前がAのみを検索

SELECT 名前,回数,日付 FROM table WHERE 名前 = 'A'

このままでは複数行となるのでそれをまとめます

SELECT 名前,回数,日付 FROM table WHERE 名前 = 'A' GROUP BY 名前

「GROUP BY」でまとめる場合は対象カラムを全て書かないといけません
しかし今回は名前のみでまとめ後のカラムは全て取得したいので下記のようにします

SELECT 名前
 ,array_agg(回数), array_agg(日付)
 FROM table
 WHERE 名前 = 'A'
 GROUP BY 名前

「array_agg」を使用すると回数を「{1,2,4,3}」というデータで取得することができます
今度は回数と日付を一つのデータとしていきます

SELECT
 名前
 ,array_agg(回数 || ',' || 日付)
 FROM table
 WHERE 名前 = 'A'
 GROUP BY 名前

文字結合は「||」で行えます
これで「{1,2018/01/01;2,2018/01/20;4,2018/03/01;3,2018/02/15}というデータになります
しかし回数が順番になっていないのでさらに順番通りにします

SELECT 名前
 ,array_agg(回数 || ',' || 日付 ORDER BY 回数)
 FROM table
 WHERE 名前 = 'A'
 GROUP BY 名前

「array_agg」の中で「ORDER BY」を行うことで並び順を指定することが可能です

ちなみに「array_agg」のセパレートが「；」なのでそれが嫌な人は「array_to_string」を使用して変更することができます

SELECT 名前
 ,array_to_string(array_agg(回数 || ',' || 日付 ORDER BY 回数), ',')
 FROM table
 WHERE 名前 = 'A'
 GROUP BY 名前

なにか役立つことがあればまた書いていきます

MissingMemory