【Presto（TreasureData）】CONCAT関数のようにフィールドを結合する方法

初めに

• 業務で少しばかりSQLを使っています。非エンジニアのマーケティング職です。
• 今回Qiitaに初めて記事投稿しますので記事を読んでくださった皆様、よろしければFBお待ちしています。

きっかけ

• 自社で扱っているDBが、MySQLではなくTREASURE DATAを使用しています。
• フィールドを結合したかったので、MySQLではCONCAT関数を使うのが分かり試したら動きませんでした。
• CONCAT関数と同じことをPrestoで実行したかったのですが、調べても出てこなったため、職場のエンジニアに聞いた事をアウトプットします。

やりたいこと

• 複数のデータを1つのフィールドにまとめた状態として出したい

例

• 2つのテーブルを使用します。
• 倉庫テーブル(db.warehouse)

id	warehouse_name
1	東京
2	名古屋
3	大阪

• 商品テーブル(db.goods)

id	goods_name	warehouse_id	instock_date
1	みかん	3	2021-◯◯-△△
2	りんご	1	2021-◯◯-△△
3	桃	2	2021-◯◯-△△
4	柿	3	2021-◯◯-△△
5	バナナ	2	2021-◯◯-△△
6	バナナ	1	2021-◯◯-△△
7	りんご	2	2021-◯◯-△△
8	桃	2	2021-◯◯-△△
9	梨	2	2021-◯◯-△△
10	ぶどう	1	2021-◯◯-△△

ゴールイメージ

• どの倉庫にどの商品が入っているのかをまとめて確認したい
• 3列目のstorage_goodsカラムで倉庫にある商品を連結させたい

id	warehouse_name	storage_goods
1	東京	りんご,バナナ,ぶどう
2	名古屋	りんご,バナナ,梨,桃
3	大阪	みかん,柿,桃

結論

• SELECT句に array_join(array_agg(db.goods.goods_name), ', ') を入れる事で解決出来ます。

ゴールイメージのクエリ

SELECT
    db.warehouse.id
    , db.warehouse.warehouse_name
    , array_join(array_agg(db.goods.goods_name), ', ') AS storage_goods
FROM
    db.warehouse 
JOIN
    db.goods
    ON db.warehouse.id = db.goods.warehouse_id
GROUP BY
    db.warehouse.id
    , db.warehouse.warehouse_name

• ※GROUP BY句でSELECTしたカラムをまとめてください。
  • GROUP BYしないとarray_join(array_agg(db.goods.goods_name), ', ')の結合先が重複し、エラーになります。
• ※今回は2つのテーブルをJOINしていますが、1つのテーブル上で複数行を結合する際も同じです。
  • JOIN句を丸々削除して使ってください。

おまけ

重複等削除

• 今回は結合する行に重複するデータはありませんでしたが、結合の際に重複を排除することが可能です。
• NULLがあるデータを削除する事も可能です。

条件	クエリ
重複削除	array_join(array_agg(DISTINCT db.goods.goods_name), ', ') DISTINCTを入れる
NULLを削除	array_join(array_agg(db.goods.goods_name)　FILTER(WHERE db.goods.goods_name IS NOT NULL), ', ') FILTER(WHERE name IS NOT NULL)を入れる
重複削除＆NULLを削除	array_join(array_agg(DISTINCT db.goods.goods_name)FILTER(WHERE db.goods.goods_name IS NOT NULL), ', ') 1行目と2行目を合わせて使用する

,ではなく/で区切りたい

• 文末の', 'を'/ 'に変えると出来ます。
  • array_join(array_agg(db.goods.goods_name), '/ ')

参考にさせていただいた記事