SQL データを学ぶ

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

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

この記事の目標

  • 複数テーブルを結合することができる
  • 結合の使い分け(JOIN / LEFT JOIN)を理解する

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

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

ahilog

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが
本記事ではBigQuery環境を想定して執筆します。

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

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

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

POINT

  • データが重くなるから
  • アクセス権限を管理するため
  • 更新頻度が内容によって異なるから

データが重くなるから

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

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

ahilog

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

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

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

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

ahilog

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

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

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

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

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

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

 

顧客ID 顧客氏名 年齢 アクセス時間 顧客が見たURL 会員種別
A 山田くん 30 12:30:00 https://ahilog.com/2021/02/14/extract-columns/ ゴールド
A 山田くん 30 12:35:22 https://ahilog.com/2021/02/10/da-skillcheck-pattern/ ゴールド
B 佐藤さん 44 14:30:00 https://ahilog.com/2021/02/15/bunkei-dataanalyst/ シルバー
C 高橋さん 28 14:32:15 https://ahilog.com/2021/02/16/tensyoku-money/ ブロンズ

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

ahilog

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

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

ahilog

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

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

ahilog

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

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

  • お客さんの会員情報をユーザーIDごとに管理しておく
  • お客さんのアクセスログもユーザーIDごとに管理しておく

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

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

 

顧客ID 顧客氏名 年齢 会員種別
A 山田くん 30 ゴールド
B 佐藤さん 44 シルバー
C 高橋さん 28 ブロンズ

 

顧客ID アクセス時間 顧客が見たURL
A 12:30:00 https://ahilog.com/2021/02/14/extract-columns/
A 12:35:22 https://ahilog.com/2021/02/10/da-skillcheck-pattern/
B 14:30:00 https://ahilog.com/2021/02/15/bunkei-dataanalyst/
C 14:32:15 https://ahilog.com/2021/02/16/tensyoku-money/

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

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

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

テーブルの結合方法(JOIN / LEFT JOINの使い方)

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

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

端的にいうと、結合出来たデータのみを残すのか 全行を残すのかの違いです。

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

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

 

顧客ID 顧客氏名 年齢 会員種別
A 山田くん 30 ゴールド

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

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

 

顧客ID アクセス時間 顧客が見たURL
A 12:30:00 https://ahilog.com/2021/02/14/extract-columns/
A 12:35:22 https://ahilog.com/2021/02/10/da-skillcheck-pattern/
B 14:30:00 https://ahilog.com/2021/02/15/bunkei-dataanalyst/
C 14:32:15 https://ahilog.com/2021/02/16/tensyoku-money/

JOIN(内部結合)について

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

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

顧客ID顧客氏名年齢会員種別アクセス時間顧客が見たURL
A山田くん30ゴールド12:30:00https://ahilog.com/2021/02/14/extract-columns/
A山田くん30ゴールド12:35:22https://ahilog.com/2021/02/10/da-skillcheck-pattern/

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はテーブル名も毎回書かないと行けないの?
めんどくさいなあ・・・

2つ以上のデータを使った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はダルマを落とさないタイプの結合です。
このような感じになります。

 

顧客ID 顧客氏名 年齢 会員種別 アクセス時間 顧客が見たURL 会員種別
A 山田くん 30 ゴールド 12:30:00 https://ahilog.com/2021/02/14/extract-columns/ ゴールド
A 山田くん 30 ゴールド 12:35:22 https://ahilog.com/2021/02/10/da-skillcheck-pattern/ ゴールド
NAN NAN NAN NAN 14:30:00 https://ahilog.com/2021/02/15/bunkei-dataanalyst/ シルバー
NAN NAN NAN NAN 14:32:15 https://ahilog.com/2021/02/16/tensyoku-money/ ブロンズ

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

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

SQLくん

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

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

BigQueryでやってみよう

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが
本記事ではBigQuery環境を想定して執筆します。

使用するデータについて

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

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

ahilog

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

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

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

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

SibSp + Parch (家族人数)が4人以上の
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


create table if exists〜は集計結果をそのテーブル名で保存するために使っています。
テーブルの作り方については下記の記事を参照ください。
※今回はtitanic_sample配下にbig_family_listという名前でテーブルを作成しました。

参考【初心者向けSQL】中間テーブルを使ってみよう【CREATE TABLE/WITH句/副参照 】

続きを見る

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

次に、【課題1】で作ったテーブルを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

t1.*とアスタリスク(*)を書くと、「そのテーブルの列すべて」という意味になります。

ahilog

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

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

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

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

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

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

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

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

キャリアアップに必要な知識を身につけよう | Udemyオンライン動画コース

  • この記事を書いた人

yutasukun

京都橘高校からの史上初東大受験・現役合格を成し遂げ 「リアルドラゴン桜」として横断幕が出たことアリ。 そんな過去の栄光も虚しく10年が経過。 今はデータアナリストとして汐留の広告代理店で バリバリはたらく毎日。

-SQL, データを学ぶ

© 2021 ahilog Powered by AFFINGER5