SQLメモ　複数id持ちidの他idリスト

背景

業務で使えたSQLを自分のためにも記事に残しておこうと思いますので参考になる方は参考にしてください。

環境

BIGQUERY

利用するテーブル

idのマスタデータ

common_id：1ユーザーに1つ割り当てられるid
id：1ユーザーが複数発行可能なid

table

集計したいこと

複数idを持っているユーザーは他のidのリストのカラムを追加

アウトプットイメージ

例えば、aaaというcommon_idのユーザーが00001，00002，00004という3つのidを持っている場合、idが00001の行には00002,00004を入れるものです。

コード

SELECT
    t1.common_id
    , t1.id
    , (SELECT STRING_AGG(t2.id, ",") FROM table AS t2 WHERE t1.common_id = t2.common_id AND t1.id != t2.id) AS other_id
FROM
    table

説明

ポイント
①STRING_AGG()という関数は、一つ目の引数を二つ目の引数で区切ってつなげられるものです。
②サブクエリをSELECT文中で使った場合、サブクエリの外の他の行の値1行1行に対してWHERE句で判定ができます。例えばcommon_idがaaaでidが00001の行に対しては、WHERE句のt1.common_id = t2.common_id AND t1.id != t2.idでtable(t2)からcommon_idがaaaでidが00001ではない行だけを残し、STRING_AGG()でつなげています。これを全ての行で行っています。

さいごに

以上、少しでもご参考になれば幸いです。