【SQL入門編⑥】テーブルを結合しよう！その１

この記事では、データ分析の際に欠かせない、「テーブルの結合」について勉強していきます！

テーブルの結合について

そもそもテーブルの結合って何？

テーブルの結合をすることで、それまでのテーブルだけじゃ分からなかった、有益な情報が分析できるようになるというメリットがあります！

テーブルの結合のイメージを持とう！

今回は、以下二つのテーブルを結合させることで、どの商品をどんなユーザーが購入したかを分析してみたいと思います。

結合させるテーブル
・ユーザーの購入履歴が分かるpurchasesテーブル
・ユーザー情報が分かるusersテーブル

puchasesテーブルのuser_idカラムは、usersテーブルのidカラムと紐づくように設計されています。そのため、以下のようにuser_id = id になるようにテーブル同士を結合させることが可能です。

↑ 左4列が元purchasesテーブル、右5列が元usersテーブルですね。

二つのテーブルが結合されたことで、

・干し柿まんじゅうは2名の女性から買われている
・どのユーザーも登録日には何も買っていない

などの新しい分析が可能になりましたね！

SQLでテーブルを結合してみよう！

先ほどのpurchasesテーブルとusersテーブルの結合を、SQLで行ってみましょう！

元となるテーブルを選ぶ

まずは、結合前の元となるテーブルを選びます。
ここではpurchasesテーブルを選択しましょう。

FROM
    purchases

結合させたいテーブルを選ぶ

purchasesテーブルの右側に結合させるテーブルを選びます。
今回はusersテーブルを選択します。

FROM
    purchases
    INNER JOIN users
        ON purchases.user_id = users.id

新しく「INNER JOIN」と「ON」の2つの単語が出てきましたね。順に詳しく見ていきましょう！

INNER JOIN で結合テーブルを宣言

元となるテーブル名 INNER JOIN 結合させたいテーブル名と書くことで、どのテーブル同士を結合させるかを宣言しています。

今回の場合は、purchasesテーブルにusersテーブルを結合させたいので、purchases INNER JOIN users という書き方になります！

ON で結合条件を宣言

ON 結合条件 と書くことで、二つのテーブルの結合条件を宣言しています。
ONの後ろは、どのテーブルのどのカラムなのかを明らかにするために、テーブル名.カラム名 と書く必要があります。

抽出したいデータを選択しよう

STEP２の結果、2つのテーブルが結合され、以下のテーブルができました！

あとはこのテーブルから何を抽出するかを決めればよいですね！
今回は、このテーブルの左から順に「id」「item」「id」「gender」「age」の五つのカラムの値を抽出するとしましょう。

この場合、以下のようにSELECT句で元テーブル名.カラム名を指定します。

SELECT
    purchases.id,
    purchases.item,
    users.id,
    users.gender,
    users.age
FROM
    purchases
    INNER JOIN users
        ON purchases.user_id = users.id

このクエリを実行すると、以下のような結果が抽出されます！

ちなみに、先ほどの結合で生まれたテーブルは、通常のテーブルと同様、WHERE句で絞り込んだり、GROUP BY句でグループ化することも可能です！

以下それぞれのクエリと抽出結果の例を挙げますので、「こんな書き方もできるんだ～」というのを感じていただければ嬉しいです！

■ 結合したテーブルを「干し柿まんじゅう」のレコードだけに絞ってみる

SELECT
    *
FROM
    purchases
    INNER JOIN users
        ON purchases.user_id = users.id
WHERE
    purchases.item = "干し柿まんじゅう"

■ 結合したテーブルで、商品ごとの購入者の平均年齢を出してみる

SELECT
    purchases.item,
    AVG(users.age) AS average_age
FROM
    purchases
    INNER JOIN users
        ON purchases.user_id = users.id
GROUP BY
    item

それでは、これまでの復習も兼ねて、練習問題にトライしてみましょう！

練習問題

練習問題①

以下二つのテーブルを使って、商品ごとの女性購入者数を抽出するクエリを書いてください。ただし、purchasesテーブルのuser_idカラムの値と、usersテーブルのidカラムの値は紐づくこととします。

SELECT
    purchases.item,
    COUNT(users.gender) AS female
FROM
    purchases
    INNER JOIN users
        ON purchases.user_id = users.id
WHERE
    users.gender = "女性"
GROUP BY
    item

練習問題②

以下二つのテーブルを使って、日別の売上合計を抽出するクエリを書いてください。ただし、purchasesテーブルのitem_idカラムの値とitemsテーブルのidカラムの値は紐づくこととします。

SELECT
    purchases.purchased_at,
    SUM(items.price) AS purchase_amount
FROM
    purchases
    INNER JOIN items
        ON purchases.item_id = items.id
GROUP BY
    purchased_at

お疲れ様でした！ちょっと難しかったでしょうか？

テーブルの結合ができるようになると、これまで以上にデータ分析の幅が広がるため、くじけずに一緒に頑張って乗り越えましょう！