主キー・外部キー・リレーション: テーブル同士のつながりを読む

Stage 3 — 第1章 | データ分析基礎カリキュラム 推定学習時間：30〜40分 | 難易度：★★☆☆☆

この章で学ぶこと

Stage 1では、1つのテーブルから必要な列や行を取り出す方法を学びました。 しかし実務のデータ分析では、必要な情報が1つのテーブルにまとまっているとは限りません。

ECサイトなら、顧客情報は customers、注文情報は orders、商品情報は products のように分かれて保存されることが多いです。 これらを正しくつなぐために必要なのが、主キー、外部キー、リレーション の考え方です。

この章を終えると、こんなことができるようになります：

• 主キーと外部キーの役割を説明できる
• テーブルを分けて保存する理由を理解できる
• どの列を使ってテーブルをつなぐか判断できる
• JOINを学ぶ前提として、テーブル同士の関係を読める

1. なぜテーブルは分かれているのか

ECサイトの分析では、次のような情報を扱います。

これらをすべて1つの巨大な表に入れると、同じ顧客名や商品名が何度も繰り返されます。 更新ミスも起きやすくなります。

そのため、データベースでは役割ごとにテーブルを分けます。

分析では、分かれて保存されたテーブルを必要に応じてつなぎます。 そのための基本がJOINです。

2. 主キーは「その行を一意に見分ける列」

主キーは、テーブル内の1行を一意に識別するための列です。 英語では primary key と呼ばれます。

たとえば customers テーブルでは、customer_id が主キーになります。

customer_id が分かれば、どの顧客かを1人に特定できます。 同じ名前の顧客がいても、IDが違えば別の顧客として扱えます。

ECデータでは、次のような列が主キーとして使われます。

主キーは、後でテーブルをつなぐときの基準になります。

3. 外部キーは「別テーブルの行を指す列」

外部キーは、別のテーブルの主キーを参照する列です。 英語では foreign key と呼ばれます。

たとえば orders テーブルには customer_id があります。

ここで orders.customer_id は、customers.customer_id を指しています。 つまり「この注文はどの顧客のものか」を表します。

JOINでは、このような対応する列を使ってテーブルをつなぎます。

4. リレーションはテーブル同士の関係

リレーションは、テーブル同士がどのようにつながっているかを表す考え方です。 ECデータでは、次のような関係があります。

たとえば「東京都の顧客が買った商品のカテゴリを知りたい」とします。 この問いに答えるには、1つのテーブルだけでは足りません。

• 顧客の都道府県は customers にある
• 注文日は orders にある
• 購入数量は order_items にある
• 商品カテゴリは products にある

このように、分析したい問いが複数テーブルにまたがるときにJOINが必要になります。

5. ER図でテーブル構造を眺める

SQLを書く前に、テーブル同士の関係を ER図（Entity-Relationship Diagram） で眺めると、JOINの理解がかなり楽になります。 ER図は「どのテーブルが、どのキーで、どんな関係で結ばれているか」を示す地図です。

ECデータの基本構造は、次のようなイメージです。

このER図では、JOIN経路を読みやすくするため 主キー（PK）と外部キー（FK）だけ を表示しています。注文日、数量、商品カテゴリなどの分析列は各テーブルに存在しますが、関係を結ぶキーとは分けて読みます。

この図を読むときは、次の3点を確認します。

order_items では、order_id と product_id が 複合主キー です。 同時に order_id は orders へ、product_id は products への外部キーでもあります。 ER図では両列に PK/FK を付け、2列で複合主キーを作りながら、それぞれが orders と products を参照することを示しています。

ER図があると、「この指標はどのテーブルから作るのか」「このJOINは行数を増やすのか」が先に見えます。 文法を覚える前に、データ構造を読む 習慣をつけることが、実務での分析力につながります。

6. 粒度：1行が何を表すか

粒度（grain） とは、テーブルの1行が何を表すかです。 同じECデータでも、テーブルごとに粒度が違います。

「顧客ごとの購入回数」を数えたいとき、顧客と注文だけをJOINする場合は次のSQLで足ります。

-- 顧客ごとの購入回数（customers + orders のみ）
          SELECT
            c.customer_id,
            c.customer_name,
            COUNT(o.order_id) AS order_count
          FROM customers AS c
          LEFT JOIN orders AS o
            ON c.customer_id = o.customer_id
            AND o.status = 'completed'
          GROUP BY c.customer_id, c.customer_name;
          

customers と orders は1対多ですが、JOIN後も 注文1件につき行は1つ です。 LEFT JOIN で注文がない顧客は o.order_id が NULL になり、COUNT(o.order_id) はその行を数えません。 この段階では DISTINCT は不要です。

一方、order_items までJOINすると粒度が明細に落ち、同じ order_id が複数行に増えます。 そのときは COUNT(DISTINCT o.order_id) が必要になります。

-- order_items までJOINすると DISTINCT が必要
          SELECT
            c.customer_id,
            COUNT(DISTINCT o.order_id) AS order_count
          FROM customers AS c
          LEFT JOIN orders AS o
            ON c.customer_id = o.customer_id
          LEFT JOIN order_items AS oi
            ON o.order_id = oi.order_id
          GROUP BY c.customer_id;
          

orders だけのときは COUNT(o.order_id)、order_items を足したら COUNT(DISTINCT o.order_id) — 粒度が下がったかどうかで数え方が変わります。 集計の前に「今、何の単位で数えているか」を確認しましょう。

7. つなぐ列を間違えると結果も間違う

JOINでは、どの列とどの列を対応させるかが重要です。 たとえば顧客と注文をつなぐなら、次の対応を使います。

SELECT
            customers.customer_id,
            customers.customer_name,
            orders.order_id,
            orders.order_date
          FROM customers
          JOIN orders
            ON customers.customer_id = orders.customer_id;
          

ON customers.customer_id = orders.customer_id が、テーブル同士のつなぎ方です。

列名が同じでも、意味が違う列をつなぐと誤った結果になります。 また、IDではなく名前でつなぐと、同姓同名や表記ゆれで結果がずれることがあります。

分析では、まず次の順番で確認しましょう。

1. 各テーブルの1行が何を表すか確認する
2. 主キーにあたる列を確認する
3. 外部キーにあたる列を確認する
4. どの列でJOINするか決める

実務での使いどころ: JOIN前にテーブルの地図を読む

主キーと外部キーは、テーブル同士を安全につなぐための地図です。 分析でJOINを始める前に、どの列が一意で、どの列が別テーブルを参照しているかを確認します。

JOIN条件を間違えると、行数が不自然に増えたり、関係ないデータがつながったりします。 特に、名前が似ている列をなんとなくつなぐのは危険です。

SELECT COUNT(*) AS joined_rows
          FROM orders AS o
          JOIN customers AS c
            ON o.customer_id = c.customer_id;
          

JOINした後は、行数やサンプル行を確認して、想定した関係になっているかを見ましょう。

ミニ演習

次のJOINで、つなぐ列を考えてください。

1. 顧客と注文をつなぐ。
2. 注文と注文明細をつなぐ。
3. 注文明細と商品をつなぐ。
4. JOIN条件を間違えたときに起きる問題を説明する。

まとめ

この章のキーメッセージ： JOINは、分かれて保存されたデータを分析目的に合わせて組み合わせるための基本です。SQLを書く前に、主キー・外部キー・1行の意味を確認しましょう。

この章の確認

1. 主キーと外部キーの違いを説明してください。
2. orders.customer_id は、どのテーブルのどの列を参照していると考えられますか？
3. 「顧客の都道府県別に売上を見たい」とき、どのテーブルをつなぐ必要がありますか？
4. テーブルをJOINする前に、なぜ「1行が何を表すか」を確認する必要がありますか？

関連演習

• SQL JOIN基礎
• SQL基礎（SELECTとフィルタリング）