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行だけです。
| id | product | tag |
|---|---|---|
| 1 | りんご | 果物 |
| 2 | りんご | 国産 |
| 3 | りんご | 果物 |
| 4 | みかん | 果物 |
| 5 | みかん | NULL |
| 6 | バナナ | 輸入 |
| 7 | ぶどう | NULL |
STRING_AGG・ARRAY_AGGの基本構文
SELECT string_agg(tag, ', ' ORDER BY id)
FROM product_tags;STRING_AGGで複数行を区切り文字つきの1行にする
string_agg(値, 区切り文字)は、各行の値を区切り文字でつないだ1つの文字列を返します。GROUP BYがなければ全行が対象で、結果は1行です。ここではりんごの3件を「, 」でつなぎます。連結の順序は集約の中のORDER BYで指定しないと保証されないため、ここではid順にしています。
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です。
SELECT product, string_agg(tag, ', ' ORDER BY id)
FROM product_tags
GROUP BY product
ORDER BY min(id);| product | string_agg |
|---|---|
| りんご | 果物, 国産, 果物 |
| みかん | 果物 |
| バナナ | 輸入 |
| ぶどう | NULL |
集約の中のORDER BYで連結順を決める
ORDER BYは関数のカッコの中、引数の後ろに書きます。ここではタグ名の降順にしており、元のid順(果物、国産、果物)とは別の順序になります。外側のORDER BYは出力行の順序を決めるだけで、連結される値の順序には影響しません。
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で指定した式と同じものしか書けません。
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が向きます。
SELECT string_agg(tag, ', ' ORDER BY id) AS joined,
array_agg(tag ORDER BY id) AS tags
FROM product_tags
WHERE product = 'みかん';| joined | tags |
|---|---|
| 果物 | {果物,NULL} |
すべてNULLのグループにはCOALESCEで代わりの文字を出す
対象の値がすべてNULLのグループでは、STRING_AGGの結果もNULLです。画面や帳票で空欄にしたくないときは、COALESCEで代わりの文字を指定します。ぶどうは「(なし)」になり、他の商品の結果は変わりません。
SELECT product,
COALESCE(string_agg(tag, ', ' ORDER BY id), '(なし)') AS tags
FROM product_tags
GROUP BY product
ORDER BY min(id);| product | tags |
|---|---|
| りんご | 果物, 国産, 果物 |
| みかん | 果物 |
| バナナ | 輸入 |
| ぶどう | (なし) |
STRING_AGG・ARRAY_AGGでよくある間違い
集約内のORDER BYを省くと連結順が保証されない
ORDER BYを書かないSTRING_AGGやARRAY_AGGは、行を読んだ順に連結します。この順序は実行のたびに同じとは限らず、データ更新や実行計画で変わることがあります。結果の順序が意味を持つ場合は、必ず集約の中にORDER BYを書きます。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:集約関数 — string_aggとarray_aggの引数、NULLの扱い、対象が0行のときの結果
- PostgreSQL公式:集約式 — 集約内のORDER BYとDISTINCTの書き方