SQLメモ 複数id持ちidの他idリスト
背景
業務で使えたSQLを自分のためにも記事に残しておこうと思いますので参考になる方は参考にしてください。
環境
BIGQUERY
利用するテーブル
idのマスタデータ
common_id:1ユーザーに1つ割り当てられるid
id:1ユーザーが複数発行可能なid
集計したいこと
複数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()でつなげています。これを全ての行で行っています。
さいごに
以上、少しでもご参考になれば幸いです。