SQL練習室
← SQLガイド

SQLのSTRING_AGG・ARRAY_AGGとは?複数行を1行にまとめる使い方と順序・重複・NULL

STRING_AGGは、複数行の文字列を区切り文字でつないで1つの文字列にする集約関数です。ARRAY_AGGは同じように複数行を1つの配列にまとめます。商品ごとのタグを1行にまとめながら、順序、重複、NULLの扱いを確認しましょう。

例で使う表

product_tagsは1行が「商品1つにつけたタグ1つ」を表します。productは商品名、tagはタグで、未設定の行はNULLです。りんごには「果物」が2回付いており、みかんには1件だけNULLがあり、ぶどうはタグがNULLの1行だけです。

product_tags:商品タグ7件
idproducttag
1りんご果物
2りんご国産
3りんご果物
4みかん果物
5みかんNULL
6バナナ輸入
7ぶどうNULL

STRING_AGG・ARRAY_AGGの基本構文

sql
SELECT string_agg(tag, ', ' ORDER BY id)
FROM product_tags;

STRING_AGGで複数行を区切り文字つきの1行にする

string_agg(値, 区切り文字)は、各行の値を区切り文字でつないだ1つの文字列を返します。GROUP BYがなければ全行が対象で、結果は1行です。ここではりんごの3件を「, 」でつなぎます。連結の順序は集約の中のORDER BYで指定しないと保証されないため、ここではid順にしています。

sql
SELECT string_agg(tag, ', ' ORDER BY id)
FROM product_tags
WHERE product = 'りんご';
実行結果
string_agg
果物, 国産, 果物

GROUP BYで商品ごとにまとめる

GROUP BYと組み合わせると、グループごとに1つの文字列ができます。りんごは3件、みかんは1件、バナナは1件が連結されます。みかんは2行ありますが、1行はタグがNULLなので連結に入らず「果物」だけになります。ぶどうはNULLしかないので、結果もNULLです。

sql
SELECT product, string_agg(tag, ', ' ORDER BY id)
FROM product_tags
GROUP BY product
ORDER BY min(id);
実行結果
productstring_agg
りんご果物, 国産, 果物
みかん果物
バナナ輸入
ぶどうNULL

集約の中のORDER BYで連結順を決める

ORDER BYは関数のカッコの中、引数の後ろに書きます。ここではタグ名の降順にしており、元のid順(果物、国産、果物)とは別の順序になります。外側のORDER BYは出力行の順序を決めるだけで、連結される値の順序には影響しません。

sql
SELECT string_agg(tag, ', ' ORDER BY tag DESC)
FROM product_tags
WHERE product = 'りんご';
実行結果
string_agg
果物, 果物, 国産

DISTINCTで重複した値を1つにする

string_agg(DISTINCT ...)は、同じ値を1回だけ連結します。りんごの「果物」は2件ありますが、結果には1回だけ現れます。DISTINCTと集約内のORDER BYを併用する場合、ORDER BYにはDISTINCTで指定した式と同じものしか書けません。

sql
SELECT string_agg(DISTINCT tag, ', ' ORDER BY tag)
FROM product_tags
WHERE product = 'りんご';
実行結果
string_agg
国産, 果物

NULLはSTRING_AGGでは無視され、ARRAY_AGGでは残る

STRING_AGGはNULLの値を連結から除きます。一方ARRAY_AGGはNULLも配列の要素として残します。みかんの2行を比べると、string_aggは「果物」だけ、array_aggは{果物,NULL}になります。NULLの有無まで保ちたいときや、後でアプリ側で配列として扱いたいときはARRAY_AGGが向きます。

sql
SELECT string_agg(tag, ', ' ORDER BY id) AS joined,
       array_agg(tag ORDER BY id) AS tags
FROM product_tags
WHERE product = 'みかん';
実行結果
joinedtags
果物{果物,NULL}

すべてNULLのグループにはCOALESCEで代わりの文字を出す

対象の値がすべてNULLのグループでは、STRING_AGGの結果もNULLです。画面や帳票で空欄にしたくないときは、COALESCEで代わりの文字を指定します。ぶどうは「(なし)」になり、他の商品の結果は変わりません。

sql
SELECT product,
       COALESCE(string_agg(tag, ', ' ORDER BY id), '(なし)') AS tags
FROM product_tags
GROUP BY product
ORDER BY min(id);
実行結果
producttags
りんご果物, 国産, 果物
みかん果物
バナナ輸入
ぶどう(なし)

STRING_AGG・ARRAY_AGGでよくある間違い

集約内のORDER BYを省くと連結順が保証されない

ORDER BYを書かないSTRING_AGGやARRAY_AGGは、行を読んだ順に連結します。この順序は実行のたびに同じとは限らず、データ更新や実行計画で変わることがあります。結果の順序が意味を持つ場合は、必ず集約の中にORDER BYを書きます。

sql
SELECT string_agg(tag, ', ' ORDER BY id)
FROM product_tags
WHERE product = 'りんご';

実際に1問解いてみる

STRING_AGG

商品「りんご」のタグを、id順に「, 」(カンマと半角スペース)でつないだ1つの文字列として取得してください。別名は付けません。

テーブル

読み込み中…

実行結果

関連構文

STRING_AGG・ARRAY_AGGについてよくある質問

STRING_AGGの区切り文字を省略できますか?
できません。string_agg(値, 区切り文字)の2引数が必須です。区切りが不要なら空文字''を指定します。
STRING_AGGは数値の列にも使えますか?
引数はtext(またはbytea)です。数値の列は::textなどで文字列へ変換してから渡します。
ARRAY_AGGとSTRING_AGGはどう使い分けますか?
表示用に1つの文字列へまとめるならSTRING_AGG、後続の処理で要素として扱ったりNULLも残したりするならARRAY_AGGです。みかんの例では、STRING_AGGは「果物」、ARRAY_AGGは{果物,NULL}になります。
対象の行が0件のときの結果は?
STRING_AGGもARRAY_AGGもNULLを返します。COUNTのように0にはなりません。空の配列が必要なときはCOALESCEで補います。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。