string_agg(expression, separator) はグループごとに値を1つの文字列に連結し、array_agg(expression) は配列にまとめます。並び順を指定するには、呼び出しの中に ORDER BY を書きます: string_agg(name, ', ' ORDER BY name)。
IT は Bo, Kim, Li と {6100,4800,3900}、Sales は Ana, Raj と {5000,4200} を返します。どちらも count や sum と同じ通常の集約関数です。GROUP BY があればグループごとに、なければテーブル全体に対して動きます。
値の並び順を指定する
集約の中に ORDER BY がないと、値は行が届いた順に並び、実行するたびに変わることがあります。クエリの末尾の ORDER BY が並べ替えるのは結果の行で、各文字列の中の値ではありません。内側の ORDER BY は最後の引数の後に、カンマなしで書きます。
string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)
DISTINCT で重複を取り除く
Ada はペンを2回買っていますが、DISTINCT により1回だけ表示されます: book, pen。DISTINCT を使うときは、ORDER BY に使えるのは集約する値だけです。別の列で並べ替えると "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list" で失敗します。
NULL と空のグループの扱い
string_agg は NULL を飛ばして Bo, Li を返します。array_agg は NULL も残します: {Bo,Li,NULL}。array_agg から NULL を除くには FILTER (WHERE name IS NOT NULL) を付けます。
対象が0行のとき、どちらも空文字列や空配列ではなく NULL を返します。呼び出し側が値を期待しているなら包みます: COALESCE(string_agg(...), '') や COALESCE(array_agg(...), '{}')。
FILTER で一部の行だけを集約する
FILTER (WHERE ...) を付けると、クエリの他の部分はすべての行を見たまま、その集約だけを条件に合う行に絞れます。
IT は3人で Bo, Li を、Sales は2人で Ana を表示します。
別テーブルの各行にリストを付ける
顧客ごとに購入した商品のリストを表示するには、サブクエリで集約します。こうすると外側のクエリに GROUP BY は要りません。
SELECT c.id, c.name, c.email,
(SELECT string_agg(o.product, ', ' ORDER BY o.product)
FROM orders o
WHERE o.customer_id = c.id) AS products
FROM customers c;
注文のない顧客も結果に残り、products は NULL になります。GROUP BY 付きの結合では、LEFT JOIN にしない限りそうした顧客は残りません。
よくあるエラーと応用
- 数値:
string_aggはテキストを受け取るので、string_agg(id, ',')は "function string_agg(integer, unknown) does not exist" で失敗します。キャストします:string_agg(id::text, ',')。 - グループごとの先頭 N 件: 並べ替えた配列を切り出します。
(array_agg(name ORDER BY salary DESC))[1:2]。 - JSON で出力:
json_aggとjsonb_aggは行を JSON 配列にまとめ、jsonb_object_agg(key, value)はオブジェクトを作ります。JSONB を参照してください。 - すでに配列がある場合:
array_to_string(arr, ', ')は既存の配列を連結し、unnestは配列を行に戻します (配列)。 - ウィンドウ関数として: どちらもウィンドウ関数としても使えます。たとえば
string_agg(name, ', ') OVER (PARTITION BY dept)なら、リスト全体をすべての行に表示できます。
よくある質問
PostgreSQL で GROUP_CONCAT に相当するものは何ですか?
string_agg(column, ',') です。MySQL の GROUP_CONCAT と違って区切り文字は必須で、並び順は呼び出しの中に書きます: string_agg(name, ', ' ORDER BY name)。
string_agg で数値を連結するには?
値をテキストにキャストします: string_agg(id::text, ',')。string_agg はテキスト (または bytea) しか受け取らないので、整数を渡すと "function string_agg(integer, unknown) does not exist" で失敗します。
string_agg に長さの上限はありますか?
独自の上限はありません。結果は通常の text 値で、最大 1 GB まで入ります。MySQL の GROUP_CONCAT は group_concat_max_len で切り詰められますが、PostgreSQL は切り詰めません。
array_agg で先頭の N 件を取得するには?
集約の中で並べ替え、結果を切り出します: (array_agg(name ORDER BY score DESC))[1:3] はグループごとに上位3人の名前を返します。