コマンド道場

$ man array-agg

ARRAY_AGG

集約関数

グループの中の値を配列にまとめる。STRING_AGG と違い NULL も配列に入る。

呼び出しの形

ARRAY_AGG(式)
ARRAY_AGG(式 ORDER BY 式)
ARRAY_AGG(DISTINCT 式)

つまずきやすいところ

  • NULL も配列に入ります。 STRING_AGG とは逆なので、要素数がずれます。除くなら FILTER (WHERE ... IS NOT NULL) を付けてください。

  • 並び順は指定しないと決まりません。ARRAY_AGG(式 ORDER BY 式) と書いてください。

  • 配列型は対応している製品が限られます。 移植性を優先するなら STRING_AGG にしてください。

実行例

結果は実際に流したものです。同じ SQL を 自由に打てる画面 で試すと、同じ結果になります。

部署ごとに名前を配列にまとめる
SELECT busho_id, ARRAY_AGG(name ORDER BY name) AS meibo, ARRAY_LENGTH(ARRAY_AGG(name), 1) AS ninzu FROM shain GROUP BY busho_id ORDER BY busho_id
busho_idmeiboninzu
1["鈴木"]1
2["中村","佐藤","小林","田中"]4
3["伊藤","山田","松本","高橋"]4
4["山本","斎藤","清水"]3
5["加藤","吉田"]2
7["井上","木村"]2
NULL["大野","渡辺"]2

7 行

配列のままなので、要素数を数えることもできます。

NULL も配列に入る
SELECT ARRAY_AGG(busho_id ORDER BY shain_id) AS null_mo_hairu, ARRAY_AGG(busho_id ORDER BY shain_id) FILTER (WHERE busho_id IS NOT NULL) AS filter_de_nozoku FROM shain WHERE shain_id >= 15
null_mo_hairufilter_de_nozoku
[4,4,null,null][4,4]

1 行

STRING_AGG は NULL を飛ばしますが、ARRAY_AGG は入れます。要素数が変わります。

説明

ARRAY_AGG は、グループに属する値を配列にまとめます。文字列としてつなぐ STRING_AGG と似ていますが、値のまま保持するので、あとから要素数を数えたり添字で取り出したりできます。

⚠️ NULL も配列に入ります。 STRING_AGG が NULL を飛ばすのと逆です。混ざると要素数がずれるので、除きたいなら ARRAY_AGG(式) FILTER (WHERE 式 IS NOT NULL) と書いてください。

⚠️ 並び順は指定しないと決まりません。ARRAY_AGG(式 ORDER BY 式) と、引数の中に ORDER BY を書きます。

⚠️ 配列型は PostgreSQL の機能です。標準 SQL にはありますが、対応している製品は限られます。移植性を考えるなら STRING_AGG のほうが安全です。

次に読む

使い方を順に読むなら

関係する関数

根拠にした一次情報