データ分析とインテリジェンス

[SQL]テーブルを結合してフラグを立てる

「そのまま使える前処理」シリーズの目次はこちら

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

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

select
a.id
,(case when b.id is not null then 1 else 0 end) as flag1
from テーブル1 as a
left join テーブル2 as b on a.id=b.id

※テーブル1とテーブル2をidをキーにして結合

補足1:case文については
,(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
でも同じ結果になる。

補足2:0/1のフラグ形式にしているが区別がつけばよいので、文字列にしたり1/2のようにしてもかまわないが、あとで件数を集計する場合はフラグ形式の方が使いやすい。

補足3:case文で区分を定めれば、複数のカテゴリーを設定することもできる。

■やりたいこと:あるテーブルに含まれているかどうかのフラグを立てる

例えば、顧客全員のIDリスト(テーブル1)と、ある商品の購入者のリスト(テーブル2)があって、そこに購入経験があるかどうかの区別を付けたいというような場合、購入経験があるIDだけ必要ならばinner joinで取り出せばよいが、区別して比較をしたい場合にはこの方法は手間がかかる。そんな時に有用なのが、全員のIDリストに購入フラグを立てる方法。

■解説

やっていることはシンプルで

1.IDリストはそのまま残したいからテーブル1にテーブル2をleft joinする
2・両方にある場合は同じ値が入り、テーブル2に無い場合はnullになることを利用して判別する

これを1つのSQLにしている。もちろん2段階に分けても同じことができるので、慣れないうちは段階を踏んでどうなっているかを見ると良いだろう。

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

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

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

いくつものテーブルをまとめてJOINすることで、複数のフラグを同時につけることもできる

select
a.id
,(case when b.id is not null then 1 else 0 end) as flag1
,(case when c.id is not null then 1 else 0 end) as flag2
from テーブル1 as a
left join テーブル2 as b on a.id=b.id
left join テーブル3 as c on a.id=c.id

このエントリーをはてなブックマークに追加

タグ:


最新のブログ記事5件

最悪のデータ分析組織とは
「人工知能でいい感じの成果を出してくれ」にどう向き合うか
すごい人工知能が開発されたら起きる未来について
csvファイルの扱い方
仕事を早くすることのメリットについて

ブログトップ > [SQL]テーブルを結合してフラグを立てる