【初心者向けSQL】テーブル同士を結合しよう【JOIN / LEFT JOIN】

この記事では、下記の部分まで理解することを目指しましょう。

今持っているデータだけで集計できないタスクを振られたの！
他のテーブルにあるみたいなんだけど、どうしたらいいんだろう。

データは内容によってテーブルを分けて保存するからね。
必要なテーブルを組み合わせて使うんだよ。
今日はテーブル同士の結合について詳しく説明するね。

ahilog

このシリーズの記事はこちらです。

テーブルの結合（JOIN/LEFT JOIN）が必要な理由

結論から言うと、下記３つの理由になります。

データが重くなるから

すべての情報がひとつのテーブルに入っていたら
とても便利だと思いませんか？

しかし、１０列のデータと１００列のデータだと、SQLを回す際の処理の重さが変わってくるんです。

ahilog

普段使わないような情報まで
列に入っていたら、それだけで見にくいですよね。

データアナリストが分析しやすくするために
あえてデータを分けて保存しているんです。

アクセス権限を管理するため

いつも分析に使うデータに
個人情報などが含まれていたら
情報漏えいのリスクがあります。

ahilog

最近は特に、個人情報の管理方法については
厳しく規制されるようになりました。

僕たちデータアナリストが分析する時に、
個人情報まで使うことはありません。

基本的には氏名ではなくID・年齢ではなく年齢階層（10代・20代・・）
住所ではなく居住する都道府県　などのレベルまで丸まったデータを使います。

個人情報は別のところに保存しておいてアクセス制限し、分析時は匿名化されたデータを使う　というように、セキュリティレベルにわけて管理するケースもあります。

更新頻度が内容によって異なるから

これは具体例をもとに說明しますね。

table.3：社員の身長と体重のみ抽出した結果

ahilog

このtable.3は顧客ごとに僕のURLのアクセス履歴をまとめたものです。
※もちろんダミーです。
年齢とか会員種別が変わったらどうしますか？

変わった所だけ更新すればいいんじゃないの？

ahilog

このデータが1億行あったとしたら、すべてのデータを読み込んで、変更点を変えないといけないんですよね。

うっ・・頭が痛くなりそう。
顧客名簿だけ分かれていれば、それを更新するだけでいいのに。
ahilogなんてお客さん少ないんだし笑

ahilog

お客さん少ないのは余計なお世話ｗｗｗ
ぐま子ちゃんの言うとおりで、名簿とアクセスログが分かれていればいいんだ。

せっかくお客さんの情報はIDでわかるようになっているので、

とわけておくことで、
毎回大きなデータに触らずに済むのです。

実際の現場では、
下記のようにデータが分けて保存しています。

このとき、２つのデータに共通するのが「顧客ID」で、これをキーと呼んだりします。

このキー情報をもとに、
２つのテーブルを結合して分析する時に使う技術がJOIN / LEFT JOINです。

次の項でゆっくり說明していきますね。

テーブルの結合方法（JOIN / LEFT JOINの使い方）

テーブルの結合には
大きく２種類の方法をよく用います。

JOINとLEFT JOINというのですが、
今から違いを說明していきますね。

今回の説明では、
顧客データベースからゴールド会員（山田くん）のみを抜き出し
アクセスログデータから山田くんのデータを見つける　という処理を想定します。

ゴールド会員のデータ
テーブル名：gold_members　に抽出して、

というゴールド会員データを抽出している状態です。

こちらを、下記のアクセスログ（テーブル名：access_log）に結合する操作をします。

JOIN（内部結合）について

JOINはイメージで言えば「ダルマ落とし」に似ています。

データを結合すると、
顧客データ（山田くん）の情報のみが抜き取られます。

JOIN句はFROMの後に書きます。
実際に書くとこんな感じになります。

SELECT 
gold_member.顧客ID,
gold_member.顧客氏名,
gold_member.年齢,
gold_member.会員種別,
access_log.アクセス時間,
access_log,顧客が見たURL
FROM 
gold_member
JOIN 
access_log
on
gold_member.顧客ID = access_log.顧客ID

FROMに書いているものが左側
JOINに書いているものが右側にあるイメージをしましょう。

ahilog

ダルマ落としでいうと、左からハンマーが飛んできて
右側にあるダルマからデータが抜き取られるイメージです。

on の後に、何をキーに突合するのかを指定します。
今回だと、顧客IDの一致が条件なので、

gold_member.顧客iD = access_log.顧客IDです。

JOINはテーブル名も毎回書かないと行けないの？
めんどくさいなあ・・・

２つ以上のデータを使ったSQLの場合は、
ちゃんと住所（テーブル名）を書かないとわからないからね。
ただ、省略して書くこともできるよ！

ahilog

テーブル名がめちゃくちゃ長いって、
データ分析してるとあるあるなんです。

そのため、このように省略して書くこともできるので、覚えておきましょう！

SELECT 
g.顧客ID,
g.顧客氏名,
g.年齢,
g.会員種別,
a.アクセス時間,
a,顧客が見たURL
FROM 
gold_member g
JOIN 
access_log a
on
g.顧客ID = a.顧客ID

テーブル名のあとに、スペースを開けてあだ名を付けてあげると
他の部分もそのあだ名でOKなんです。

テーブル名が長いときはお試しください！

LEFT JOIN（外部結合）について

LEFT JOINはダルマを落とさないタイプの結合です。
このような感じになります。

access_logの方のログの中で、
ゴールド会員でないデータも残した状態にすることができます。

NAN　というのは「何も情報がありません」という意味です。

SQLくん

特に何も言われてへんから、
空っぽにしてあるで！

必要に応じてJOINとLEFT JOINを使い分けましょう！

BigQueryでやってみよう

使用するデータについて

BigQueryにtitanicのデータを格納しており、
こちらをもとに分析します。

データはtitanic_sample　というフォルダの中に
titanic_logという名前で格納してあります。

ahilog

この場合、FROM titanic_sample.titanic_log　と「.」をつけて
フォルダ名を書く必要があるので注意しましょう。
大抵の場合、データは何かしらのフォルダにまとめて置かれています。

乗船者の家族の合計人数が4人以上のデータのみをtitanic_logから抜き出してみよう

【課題１】SibSp + Parch >= 4のPassengeridリストを作る

これは今までの記事を読んでくれている皆様ならきっと出来ます。

SibSp + Parch （家族人数）が４人以上の
Passengeridデータだけを抜き出すので、
WHEREを使いましょう。

【初心者のためのSQL】検索条件を指定しよう【WHERE / HAVING】

答えはこんな感じになります。

SELECT 
SibSp+Parch as num_of_family,
Passengerid
from 
`ahilog-analysis.titanic_sample.titanic_log`
where SibSp+Parch >= 4
order by Passengerid

【課題２】【課題１】のテーブルをtitanic_logに結合する

次に、【課題１】で作ったテーブルをaccess_logに結合しましょう。

今回はnum_of_family >= 4のデータだけ残せばいいので、JOINを使います。

select 
t1.*,
t2.num_of_family
from 
`ahilog-analysis.titanic_sample.titanic_log` t1
join 
`ahilog-analysis.titanic_sample.big_family_list` t2
on
t1.Passengerid = t2.Passengerid

ahilog

かなり大掛かりな処理になってくると、SQLを書くだけでも一苦労なのです。
楽できるところは楽していきましょう！

いかがでしたでしょうか。

JOINとLEFT JOINの使い分けについては
最初は少し難しく感じるかもしれません。

実際に書いて結果を見ながら、
どんな場合にどちらを使えばいいかを体得していってください！

ここまでできるとおおよその処理はできるようになっています。

次回以降は実務で使うと便利なポイントについてまとめていきます。

皆様のSQLライフに幸あれ！

より体系的にガッツリと勉強したい方にはUdemyがおすすめです！
リッチな動画コンテンツで豊富な知識を手に入れましょう。