複数行の文字列データを指定したカラムの値ごとに1行に集約できるLISTAGG関数を試してみた #SnowflakeDB

アドベントカレンダー2022

#Snowflake

さがら

2022.12.20

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

※本エントリは、Snowflakeをもっと使いこなそう！ Advent Calendar 2022の２０日目の記事となります。

さがらです。

Snowflakeで、複数行の文字列データを指定したカラムの値ごとに1行に集約できるLISTAGG関数を試してみたので、その内容をまとめてみます。

試してみた

前提

あるフリマサイトで、出品者の一覧を示すmemberテーブルと、出品された商品一覧を示すitemテーブルがあったとします。（ダミーデータなので値は適当です。）

memberテーブル

itemテーブル

このmemberテーブルとitemテーブルは、member.idカラムとitem.user_idでJOIN可能です。

単純にJOINすると下図のようになります。

「各出品された商品の出品者情報を紐づける」という意味ではこのテーブルで良いのですが、「出品者1人ごとに1レコードでどんな商品を出品しているのかまとめたい」というケースもあると思います。これを、LISTAGG関数で実現してみます！

LISTAGG関数を試してみる

itemテーブルに対して、LISTAGG関数を様々な形で使ってみます。

まずは一番シンプルな使い方です。下記のように引数にカラムを1つだけ入れると、そのカラムの各行の値が1行に集約されて返ってきます。

select
    listagg(item_id)
from
    item;

しかしこのままだと全ての文字列が繋がっていて見づらいですよね、そこで、LISTAGG関数の第2引数に、各値の区切り文字を入れることができます！下記のクエリでは、第2引数に','を入れて、カンマ区切りにしています。

select
    listagg(item_id, ',')
from
    item;

また、LISTAGG関数のあとにwithin group (order by [カラム名])で、指定したカラムで出力する順序を指定することも可能です。

select
    listagg(item_id, ',') within group (order by item_id),
    listagg(item_name, ',') within group (order by item_id)
from
    item;

さらに、distinctオプションをつけることで、出力する値の重複削除も可能です。

select
    listagg(distinct user_id, ',') within group (order by user_id)
from
    item;

また、これまでは1行にまとめることしかしていませんでしたが、GROUP BY句と併せることで、指定したカラムの値ごとに行を集約することができます！この使い方が一番使う場面が多いと思います。

select
    user_id,
    listagg(item_id, ', ') within group (order by item_id)
from
    item
group by
    user_id;

ARRAY_CONSTRUCT関数と併せて使うことで出力を配列にすることもできます！配列にしておけば、GET関数などで値を個別に取得できるので、なお便利ですね。

select
    user_id,
    array_construct(listagg(item_id, ', ') within group (order by item_id))
from
    item
group by
    user_id;

itemテーブルをuser_id列のユニーク値ごとに1行にまとめてmemberテーブルとJOINする

ということで、最後のまとめとして、このitemテーブルをuser_id列のユニーク値ごとに1行にまとめてmemberテーブルとJOINすることを考えてみます。

まず、itemテーブルをuser_id列のユニーク値ごとに1行にまとめるには下記のクエリを実行すればOKです。

select
    user_id,
    listagg(item_id, ', ') within group (order by item_id) as item_id,
    listagg(item_name, ', ') within group (order by item_id) as item_name,
    listagg(item_price, ', ') within group (order by item_id) as item_price
from
    item
group by
    user_id;

この上で、memberテーブルとJOINするには、下記のクエリを実行すればOKです。これで、やりたいことが出来ました！

with item_by_user as (
    select
        user_id,
        listagg(item_id, ', ') within group (order by item_id) as item_id,
        listagg(item_name, ', ') within group (order by item_id) as item_name,
        listagg(item_price, ', ') within group (order by item_id) as item_price
    from
        item
    group by
        user_id
)
select
    *
from
    member
left outer join
    item_by_user
on
    member.id = item_by_user.user_id;