映画の評価を顧客データと結合する

SQLで進めるデータドリブンな意思決定

Irene Ortner

Data Scientist at Applied Statistics

JOIN

SQL でのデータ結合の前提コース

LEFT JOIN

LEFT JOIN は外部結合の一種。
左のテーブルを全件保持し、右のテーブルと一致を取得。
識別子で2つのテーブルの対応行を定義。

テーブルに別名を付ける

SELECT *
FROM customers AS c
WHERE c.customer_id = 1;

LEFT JOIN のテーブル

左テーブル: renting_selected

| renting_id | customer_id | rating |
|------------|-------------|--------|
| 518        | 1           | `null` |
| 203        | 2           | 6      |
| 478        | 4           | 7      |
| 292        | 4           | 8      |
| 477        | 5           | `null` |
| 400        | 6           | `null` |

右テーブル: customers_selected

| customer_id | name               | gender |
|-------------|--------------------|--------|
| 1           | Robert Bohm        | male   |
| 2           | Wolfgang Ackermann | male   |
| 3           | Daniela Herzog     | female |
| 4           | Julia Jung         | female |

LEFT JOIN の例

SELECT *
FROM renting_selected AS r
LEFT JOIN customers_selected AS c
ON r.customer_id = c.customer_id;

| renting_id | customer_id | rating | customer_id | name               | gender |
|------------|-------------|--------|-------------|--------------------|--------|
| 518        | 1           | `null` | 1           | Robert Bohm        | male   |
| 203        | 2           | 6      | 2           | Wolfgang Ackermann | male   |
| 478        | 4           | `null` | 4           | Julia Jung         | female |
| 292        | 4           | 8      | 4           | Julia Jung         | female |
| 477        | 5           | 7      | `null`      | `null`             | `null` |

複数の JOIN

SELECT m.title, 
       c.name
FROM renting AS r
LEFT JOIN movies AS m
ON r.movie_id = m.movie_id
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id;

Let's practice!

SQLで進めるデータドリブンな意思決定