Menu

PostgreSQL string_agg と array_agg (GROUP_CONCAT)

string_agg はグループの値を1つの文字列に連結し、array_agg は配列にまとめます。並び順、DISTINCT、FILTER、NULL の扱い、よくあるエラーを解説します。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

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人の名前を返します。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める