SQL/BigQuery 別のテーブルを結合してフラグを立てる

SQL 別のテーブルを結合してフラグを立てる

クエリを先に書いておく。テーブルAにテーブルBをidをキーにしてLEFT JOINしている場合を考える。

SELECT
a.id
, (CASE WHEN b.id IS NOT NULL THEN 1 ELSE 0 END) as flag1
FROM A
LEFT JOIN B ON A.id=B.id

補足

補足1:
,(CASE WHEN b.id IS NULL THEN 0 ELSE 1 END) as flag1
,(CASE WHEN b.id = a.id THEN 1 ELSE 0 END) as flag1

などでも同じ結果になる。LEFT JOINの場合は結合できていればb.idに値が入るのでそれが判定できればよい。

補足2:0/1のフラグ形式にしているが区別がつけばよいのでboolen型でTrue/False、文字列型にする、1/2のように区分にする付けるでもかまわない(が、チームの人とは認識を共有しておくほうがいい)。 0/1なら件数を集計する場合にそのままsumが使えるのでフラグ形式の方が何かと便利だというのが個人的な見解。

補足3:結合の場合は結合した後のカラムを使えるのでidに限らない。

解説

例えば顧客全員のidリスト(テーブルA)と、ある商品の購入者のリスト(テーブルB)があって、idごとににその商品の購入経験があるかどうかの区別を付けたいとか、店舗リスト(テーブルA)があって、商品リスト(テーブルB)にある特定の商品を扱っているかなど知りたいなど集計でもBIでもよく使う方法。

そのまま条件を満たす人数を集計したり、フラグを集計キーに使ってフラグ事に比較したりと用途は多い。

購入経験があるIDだけ必要ならばINNER JOINで取り出せばよいが、区別して比較をしたい場合などには全員のIDリストに購入フラグを立てることで区別を付けることができる。

やっていることは基本的。

  • idリストはそのまま残したいからテーブルAにテーブルBをLEFT JOINする。INNER JOINだとテーブルBにないidが消えてしまう
  • テーブルBにidがある場合はb.idにa.idと同じ値が入り、無い場合はb.idがnullになることを利用して判別している

これを1つのSQLにしている。もちろん2段階に分けても同じことができるので、慣れないうちはJOINだけしたテーブルを確認してみると良いだろう。

正しく結合されているかを確認する

結合キーが両テーブルともユニークになってるのであれば、結合後のレコード数に変化は無い。もし変化した場合はテーブルBの結合キーが重複しているので、意図せずして件数が増えた場合は重複を確認すること。

応用:数種類のフラグを同時につける

いくつものテーブルをまとめてJOINすることで、複数のフラグを同時につけることもできる。この場合も結合した後のテーブルからCASE文で作っている。

SELECT
 a.id
 ,(CASE WHEN b.id IS NOT NULL then 1 else 0 end) as flag1
 ,(CASE WHEN c.id IS NULL THEN 1 else 0 end) as flag2
FROM A
LEFT JOIN B
ON A.id=B.id
LEFT JOIN テーブルC
ON A.id=C.id

SQL TIPS

Posted by shinu