🔗 Step 11: 複雑な結合パターン

実務で使える高度な結合テクニック!

📋 このステップで学ぶこと

複数条件での結合（ANDで条件を組み合わせる）
不等号を使った結合（範囲、期間、大小関係）
結合の順序とパフォーマンス
期間の重複判定
実務で頻出する複雑なクエリパターン

🎯 1. 複数条件での結合とは

1-1. これまでの結合との違い

これまで学んだ結合（INNER JOIN、LEFT JOINなど）では、1つの条件で結合していました。

※横にスクロールできます

— これまでの結合（1つの条件）
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

しかし実務では、複数の条件を同時に満たす行だけを結合したいケースが多くあります。

💡 複数条件が必要な場面

期間指定：注文日がキャンペーン期間内の場合だけ結合
カテゴリ一致：同じカテゴリの商品同士を結合
複合キー：「年月 + 商品ID」の組み合わせで結合
範囲マッチ：価格が一定範囲内の商品を結合

1-2. 複数条件の基本構文

📌 複数条件での結合の書き方

※横にスクロールできます

SELECT *
FROM テーブル1 t1
JOIN テーブル2 t2 
    ON t1.列1 = t2.列1      — 条件1
    AND t1.列2 = t2.列2     — 条件2
    AND t1.列3 = t2.列3;    — 条件3（必要に応じて追加）

ポイント:

AND で条件を繋げる
すべての条件が同時に満たされる行だけが結合される
条件の順序は結果に影響しない（どの順番で書いても同じ）

📚 2. 複数条件の具体例

2-1. 例1：3つのテーブルを結合する

やりたいこと：注文情報に、顧客名と商品名を追加して表示する

これは複数のJOINを繋げる基本パターンです。

※横にスクロールできます

SELECT 
    o.order_id,
    c.customer_name,
    p.product_name,
    o.order_date,
    o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
ORDER BY o.order_date DESC
LIMIT 10;

クエリの解説:

orders o → 注文テーブル（基点）
JOIN customers c ON ... → 顧客情報を追加
JOIN products p ON ... → 商品情報を追加
JOINを繰り返すことで、3つ以上のテーブルを結合できる

結果イメージ:

order_id	customer_name	product_name	order_date	total_amount
101	山田太郎	りんご	2024-12-01	1,500
100	鈴木花子	バナナ	2024-11-30	800

2-2. 例2：期間でマッチングする（注文時点の会員ランク）

やりたいこと：各注文に、その注文日時点での会員ランクを表示する

会員ランクは期間によって変わります。例えば、山田さんは1月〜6月は「ゴールド」、7月以降は「プラチナ」になったとします。注文日に応じて、その時点のランクを取得したい場合です。

会員ランク履歴テーブルのイメージ:

customer_id	rank	start_date	end_date
1（山田さん）	ゴールド	2024-01-01	2024-06-30
1（山田さん）	プラチナ	2024-07-01	2024-12-31
2（鈴木さん）	シルバー	2024-01-01	2024-12-31

注文日が「ランクの開始日以上」かつ「ランクの終了日以下」の行を結合します。

※横にスクロールできます

SELECT 
    o.order_id,
    c.customer_name,
    o.order_date,
    o.total_amount,
    r.rank AS order_time_rank
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN rank_history r 
    ON o.customer_id = r.customer_id          — 条件1: 同じ顧客
    AND o.order_date >= r.start_date          — 条件2: 開始日以上
    AND o.order_date <= r.end_date            -- 条件3: 終了日以下
ORDER BY o.order_date
LIMIT 10;

クエリの解説:

o.customer_id = r.customer_id → 同じ顧客で結合
o.order_date >= r.start_date → 注文日がランク開始日以上
o.order_date <= r.end_date → 注文日がランク終了日以下
3つの条件をすべて満たす行だけが結合される

なぜLEFT JOIN?:

ランク履歴がない顧客の注文も表示するため。INNER JOINだと履歴がない顧客は除外されます。

結果イメージ:

order_id	customer_name	order_date	total_amount	order_time_rank
1	山田太郎	2024-03-15	5,000	ゴールド
2	鈴木花子	2024-04-20	8,000	シルバー
3	山田太郎	2024-08-10	12,000	プラチナ

山田さんの3月の注文は「ゴールド」、8月の注文は「プラチナ」と、注文日に応じたランクが表示されています。

2-3. 例3：複合キーでの結合（年月 + 商品ID）

やりたいこと：月次在庫データと月次売上データを、年月と商品IDの組み合わせで結合する

「年月」だけでも「商品ID」だけでも一意にならないが、2つを組み合わせれば一意になるケースです。

コードを段階的に見ていきましょう。

ステップ1：月次売上を集計するCTEを作成

※横にスクロールできます

WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', order_date) AS year_month,
        product_id,
        SUM(total_amount) AS total_sales,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY strftime('%Y-%m', order_date), product_id
)

ステップ2：在庫データと結合（2つの列で結合）

※横にスクロールできます

SELECT 
    s.year_month,
    p.product_name,
    s.stock,
    ms.total_sales,
    ms.order_count
FROM stock_history s
JOIN products p ON s.product_id = p.product_id
LEFT JOIN monthly_sales ms 
    ON s.year_month = ms.year_month           -- 条件1: 年月が一致
    AND s.product_id = ms.product_id          -- 条件2: 商品IDが一致

ポイント:

year_month と product_id の両方が一致する行だけが結合される
どちらか一方だけが一致しても結合されない
これを複合キーでの結合と呼ぶ

完成コード:

※横にスクロールできます

WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', order_date) AS year_month,
        product_id,
        SUM(total_amount) AS total_sales,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY strftime('%Y-%m', order_date), product_id
)
SELECT 
    s.year_month AS 年月,
    p.product_name AS 商品名,
    s.stock AS 在庫数,
    COALESCE(ms.total_sales, 0) AS 月次売上,
    COALESCE(ms.order_count, 0) AS 注文数
FROM stock_history s
JOIN products p ON s.product_id = p.product_id
LEFT JOIN monthly_sales ms 
    ON s.year_month = ms.year_month
    AND s.product_id = ms.product_id
ORDER BY s.year_month, p.product_name;

活用シーン:

在庫管理：月次在庫と月次出荷の比較
予算管理：部門 + 月の組み合わせで予算と実績を結合
店舗分析：店舗ID + 日付の組み合わせで売上を結合

📐 3. 不等号を使った結合

3-1. 等号以外での結合

これまでの結合は =（等号）を使っていましたが、不等号（<、>、<=、>=、BETWEEN）を使った結合も可能です。

💡 不等号結合が必要な場面

期間マッチング：注文日がキャンペーン期間内かどうか
価格帯マッチング：価格が一定範囲内の商品を探す
累積計算：「この行より前」の行を集計
期間の重複検出：2つの期間が重なっているか

3-2. 例1：キャンペーン期間内の注文を探す

やりたいこと：各注文が、どのキャンペーン期間内に行われたかを調べる

キャンペーンテーブルのイメージ:

campaign_name	start_date	end_date	discount_rate
新春セール	2024-01-01	2024-01-31	10%
夏祭りセール	2024-07-01	2024-08-31	15%
年末セール	2024-12-01	2024-12-31	20%

※横にスクロールできます

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    c.campaign_name,
    c.discount_rate
FROM orders o
LEFT JOIN campaigns c 
    ON o.order_date >= c.start_date    -- 注文日がキャンペーン開始日以上
    AND o.order_date <= c.end_date     -- 注文日がキャンペーン終了日以下
ORDER BY o.order_date
LIMIT 15;

クエリの解説:

o.order_date >= c.start_date → 注文日がキャンペーン開始日以上
o.order_date <= c.end_date → 注文日がキャンペーン終了日以下
両方の条件を満たす = 注文日がキャンペーン期間内
LEFT JOINなので、キャンペーン期間外の注文も表示される（campaign_nameはNULL）

結果イメージ:

order_id	order_date	total_amount	campaign_name	discount_rate
5	2024-01-15	3,000	新春セール	10%
10	2024-03-20	5,000	NULL（期間外）	NULL
25	2024-12-25	15,000	年末セール	20%

3-3. 例2：価格帯が近い商品を探す

やりたいこと：ある商品と価格が±20%以内の同カテゴリ商品を探す

※横にスクロールできます

SELECT 
    p1.product_name AS base_product,
    p1.price AS base_price,
    p2.product_name AS similar_product,
    p2.price AS similar_price,
    ABS(p2.price - p1.price) AS price_diff
FROM products p1
JOIN products p2 
    ON p1.category = p2.category                           -- 同じカテゴリ
    AND p2.price BETWEEN p1.price * 0.8 AND p1.price * 1.2 -- 価格が±20%以内
    AND p1.product_id != p2.product_id                     -- 自分自身は除外
WHERE p1.product_name = 'りんご'
ORDER BY price_diff;

クエリの解説:

p1.category = p2.category → 同じカテゴリの商品
BETWEEN p1.price * 0.8 AND p1.price * 1.2 → 価格が基準の80%〜120%
p1.product_id != p2.product_id → 自分自身との比較を除外
ABS(p2.price - p1.price) → 価格差の絶対値

3-4. 例3：累積合計を不等号結合で計算する

やりたいこと：各注文に対して、その注文までの累積購入額を計算する

ウィンドウ関数（SUM() OVER）でも同じことができますが、不等号結合でも実現できます。

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.order_date,
    o1.total_amount,
    SUM(o2.total_amount) AS cumulative_total
FROM orders o1
LEFT JOIN orders o2 
    ON o1.customer_id = o2.customer_id     -- 同じ顧客
    AND o2.order_date <= o1.order_date     -- この注文以前の注文
WHERE o1.customer_id = 1
GROUP BY o1.order_id, o1.order_date, o1.total_amount
ORDER BY o1.order_date;

クエリの解説:

o1 → 基準となる注文
o2 → 累積対象の注文（o1以前の全注文）
o2.order_date <= o1.order_date → o1以前の注文を結合
SUM(o2.total_amount) → 結合されたo2の合計 = 累積合計

結果イメージ:

order_id	order_date	total_amount	cumulative_total
1	2024-01-10	1,000	1,000
5	2024-02-15	2,000	3,000
12	2024-03-20	1,500	4,500

📌 ウィンドウ関数との比較

同じ累積計算は、ウィンドウ関数でより簡潔に書けます:

※横にスクロールできます

-- ウィンドウ関数版（推奨）
SELECT 
    order_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS cumulative_total
FROM orders
WHERE customer_id = 1;

ウィンドウ関数の方がシンプルで高速です。不等号結合は結合パターンの理解のために知っておくと良いですが、実務ではウィンドウ関数を推奨します。

📅 4. 期間の重複判定

4-1. 期間が重なっているかどうかを判定する

実務では「2つのキャンペーンの期間が重なっていないか」「予約が重複していないか」をチェックすることがよくあります。

💡 期間の重複判定の条件

2つの期間が重なっている条件は:

開始1 ≤ 終了2 かつ終了1 ≥ 開始2

逆に言うと、重なっていないのは「終了1 < 開始2」または「開始1 > 終了2」の場合です。

4-2. 図で理解する期間の重複

期間の重複パターン:

パターン	期間1	期間2	重複?
完全に離れている	1/1〜1/31	3/1〜3/31	❌ 重複なし
一部重なっている	1/1〜1/31	1/15〜2/15	✅ 重複あり
片方が包含	1/1〜3/31	2/1〜2/28	✅ 重複あり
境界で接触	1/1〜1/31	1/31〜2/28	✅ 重複あり（1/31）

4-3. 例：キャンペーン期間の重複を検出

やりたいこと：期間が重なっているキャンペーンのペアを見つける

※横にスクロールできます

WITH campaigns AS (
    SELECT 1 AS id, '新春セール' AS name, '2024-01-01' AS start_date, '2024-01-31' AS end_date
    UNION ALL SELECT 2, '冬の大感謝祭', '2024-01-15', '2024-02-15'
    UNION ALL SELECT 3, '春の新生活セール', '2024-03-01', '2024-03-31'
)
SELECT 
    c1.name AS campaign_1,
    c1.start_date || ' 〜 ' || c1.end_date AS period_1,
    c2.name AS campaign_2,
    c2.start_date || ' 〜 ' || c2.end_date AS period_2
FROM campaigns c1
JOIN campaigns c2 
    ON c1.id < c2.id                       -- 重複ペアを避ける
    AND c1.start_date <= c2.end_date       -- 条件1: 開始1 ≤ 終了2
    AND c1.end_date >= c2.start_date;      -- 条件2: 終了1 ≥ 開始2

クエリの解説:

c1.id < c2.id → 同じペアを2回カウントしない
c1.start_date <= c2.end_date → 期間1の開始が期間2の終了以前
c1.end_date >= c2.start_date → 期間1の終了が期間2の開始以後
この2つの条件を両方満たす場合、期間が重複している

結果イメージ:

campaign_1	period_1	campaign_2	period_2
新春セール	2024-01-01 〜 2024-01-31	冬の大感謝祭	2024-01-15 〜 2024-02-15

「新春セール」と「冬の大感謝祭」は1/15〜1/31で期間が重なっています。「春の新生活セール」は他と重なっていないので結果に含まれません。

⚠️ 不等号結合の注意点

結果が膨大になりやすい：CROSS JOINに近い結果になることも
インデックスが効きにくい：等号結合より遅い傾向
ウィンドウ関数で代替できないか検討：累積計算などは特に
必ずWHERE句で絞り込む：全データを結合しないように

⚡ 5. 結合の順序とパフォーマンス

5-1. なぜ結合の順序が重要なのか

複数のテーブルを結合するとき、どの順序で結合するかによってパフォーマンス（実行速度）が大きく変わることがあります。

💡 基本原則

小さいテーブルから結合：データ量が少ない方を先に
絞り込み条件は早めに適用：不要なデータを除外してから結合
インデックスがある列で結合：高速に検索できる

5-2. 良い例と悪い例の比較

✅ 良い例：先に絞り込んでから結合

※横にスクロールできます

-- 先に注文を絞り込む（データ量を減らす）
SELECT 
    c.customer_name,
    o.order_date,
    p.product_name,
    o.total_amount
FROM (
    SELECT * FROM orders 
    WHERE order_date >= '2024-01-01'
      AND order_date < '2024-02-01'
) o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
ORDER BY o.order_date DESC
LIMIT 10;

なぜ良いか:

1ヶ月分の注文（少量）だけを先に抽出
少量のデータと顧客・商品を結合するので高速

❌ 悪い例：全データを結合してから絞り込み

※横にスクロールできます

-- 全データを結合してから絞り込み（遅い）
SELECT 
    c.customer_name,
    o.order_date,
    p.product_name,
    o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'    -- 最後に絞り込み
  AND o.order_date < '2024-02-01'
ORDER BY o.order_date DESC
LIMIT 10;

なぜ悪いか:

全注文（大量）と全顧客・全商品を結合
膨大な結合結果から後で絞り込むので遅い

📌 補足：最近のDBMSは賢い

実は、最近のデータベース（PostgreSQL、MySQL 8.0以降など）はクエリオプティマイザが優秀で、上記の2つのクエリを同じように最適化してくれることが多いです。しかし、複雑なクエリでは手動での最適化が必要な場合もあるため、基本原則を理解しておくことは重要です。

5-3. インデックスと結合

インデックスがある列で結合すると高速になります。

📌 結合とインデックス

結合条件	速度	理由
主キー・外部キーで結合	✅ 高速	自動でインデックスが作成されている
インデックスがある列で結合	✅ 高速	インデックスで素早く検索
インデックスがない列で結合	⚠️ 遅い	全件スキャンが必要
関数を使った結合	❌ 非常に遅い	インデックスが効かない

関数を使った結合の例（避けるべき）:

※横にスクロールできます

-- ❌ 避けるべき：関数を使った結合
SELECT *
FROM orders o
JOIN customers c ON UPPER(o.customer_name) = UPPER(c.customer_name);
-- UPPER()関数を使っているため、インデックスが効かない!

代替案:

※横にスクロールできます

-- ✅ 良い例：IDで結合
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- 主キー・外部キーで結合するのでインデックスが効く

💼 6. 実務で頻出するパターン

6-1. パターン1：よく一緒に買われる商品を見つける

やりたいこと：同じ注文で購入された商品のペアと、その回数を集計する

これは「この商品を買った人はこんな商品も買っています」という推薦システムの基礎になります。

コードを段階的に見ていきましょう。

ステップ1：同じ注文の商品同士を結合

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.product_id AS product_1,
    o2.product_id AS product_2
FROM order_items o1
JOIN order_items o2 
    ON o1.order_id = o2.order_id              -- 同じ注文
    AND o1.product_id < o2.product_id         -- 重複を避ける

ポイント:

o1.order_id = o2.order_id → 同じ注文内の商品
o1.product_id < o2.product_id → (商品A, 商品B)と(商品B, 商品A)の重複を防ぐ

ステップ2：商品名を取得し、ペアの回数をカウント

※横にスクロールできます

SELECT 
    p1.product_name AS product_1,
    p2.product_name AS product_2,
    COUNT(*) AS bought_together_count
FROM order_items o1
JOIN order_items o2 
    ON o1.order_id = o2.order_id
    AND o1.product_id < o2.product_id
JOIN products p1 ON o1.product_id = p1.product_id
JOIN products p2 ON o2.product_id = p2.product_id
GROUP BY p1.product_id, p2.product_id, p1.product_name, p2.product_name
HAVING COUNT(*) >= 3
ORDER BY bought_together_count DESC
LIMIT 10;

結果イメージ:

product_1	product_2	bought_together_count
りんご	バナナ	25
コーヒー	ケーキ	18
パン	牛乳	15

りんごとバナナは25回一緒に購入されています。

6-2. パターン2：初回購入から2回目購入までの日数

やりたいこと：各顧客の初回購入日と2回目購入日、その間隔（日数）を計算する

コードを段階的に見ていきましょう。

ステップ1：各注文に順位をつける

※横にスクロールできます

WITH ranked_orders AS (
    SELECT 
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date
        ) AS order_rank
    FROM orders
)

ポイント:

ROW_NUMBER() → 各行に連番をつける
PARTITION BY customer_id → 顧客ごとにリセット
ORDER BY order_date → 日付順で番号をつける
結果: 各顧客の1回目、2回目、3回目...の注文

ステップ2：1回目と2回目を別々に取得して結合

※横にスクロールできます

SELECT 
    c.customer_name,
    o1.order_date AS first_order,
    o2.order_date AS second_order,
    julianday(o2.order_date) - julianday(o1.order_date) AS days_between
FROM customers c
JOIN ranked_orders o1 
    ON c.customer_id = o1.customer_id 
    AND o1.order_rank = 1                -- 1回目
JOIN ranked_orders o2 
    ON c.customer_id = o2.customer_id 
    AND o2.order_rank = 2                -- 2回目

完成コード:

※横にスクロールできます

WITH ranked_orders AS (
    SELECT 
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date
        ) AS order_rank
    FROM orders
)
SELECT 
    c.customer_name AS 顧客名,
    o1.order_date AS 初回購入日,
    o2.order_date AS 二回目購入日,
    CAST(julianday(o2.order_date) - julianday(o1.order_date) AS INTEGER) AS 購入間隔日数
FROM customers c
JOIN ranked_orders o1 
    ON c.customer_id = o1.customer_id 
    AND o1.order_rank = 1
JOIN ranked_orders o2 
    ON c.customer_id = o2.customer_id 
    AND o2.order_rank = 2
ORDER BY 購入間隔日数
LIMIT 10;

結果イメージ:

顧客名	初回購入日	二回目購入日	購入間隔日数
鈴木花子	2024-01-10	2024-01-15	5
田中次郎	2024-02-01	2024-02-10	9
山田太郎	2024-01-05	2024-01-25	20

リピート購入までの日数が短い顧客ほど、エンゲージメントが高いと言えます。

6-3. パターン3：顧客の総合分析

やりたいこと：顧客ごとに、総購入額・購入回数・最終購入日・最頻購入カテゴリを表示する

コードを段階的に見ていきましょう。

ステップ1：顧客の基本統計を計算

※横にスクロールできます

WITH customer_stats AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id) AS order_count,
        SUM(o.total_amount) AS total_amount,
        MAX(o.order_date) AS last_order_date
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
)

ステップ2：最頻購入カテゴリを計算

※横にスクロールできます

favorite_category AS (
    SELECT 
        o.customer_id,
        p.category,
        COUNT(*) AS category_count,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id 
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY o.customer_id, p.category
)

ステップ3：2つのCTEを結合

※横にスクロールできます

SELECT 
    cs.customer_name,
    cs.order_count,
    cs.total_amount,
    cs.last_order_date,
    fc.category AS favorite_category
FROM customer_stats cs
LEFT JOIN favorite_category fc 
    ON cs.customer_id = fc.customer_id 
    AND fc.rank = 1                         -- ランク1位（最頻）だけ

完成コード:

※横にスクロールできます

WITH customer_stats AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS total_amount,
        MAX(o.order_date) AS last_order_date
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
),
favorite_category AS (
    SELECT 
        o.customer_id,
        p.category,
        COUNT(*) AS category_count,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id 
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY o.customer_id, p.category
)
SELECT 
    cs.customer_name AS 顧客名,
    cs.order_count AS 購入回数,
    cs.total_amount AS 総購入額,
    cs.last_order_date AS 最終購入日,
    fc.category AS 最頻購入カテゴリ
FROM customer_stats cs
LEFT JOIN favorite_category fc 
    ON cs.customer_id = fc.customer_id 
    AND fc.rank = 1
ORDER BY cs.total_amount DESC
LIMIT 20;

活用シーン:

顧客セグメント分析：優良顧客の特徴を把握
マーケティング施策：最頻カテゴリに基づくレコメンド
離脱予防：最終購入日が古い顧客へのアプローチ

📝 練習問題

複雑な結合パターンをマスターしましょう!

問題 1基本

3つのテーブルを結合

注文テーブル、顧客テーブル、商品テーブルの3つを結合し、顧客名、商品名、金額を表示してください。

※横にスクロールできます

SELECT 
    c.customer_name AS 顧客名,
    p.product_name AS 商品名,
    o.order_date AS 注文日,
    o.total_amount AS 金額
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
ORDER BY o.order_date DESC
LIMIT 10;

解説:

JOINを繰り返すことで3つ以上のテーブルを結合できる
orders → customers → products の順に結合

問題 2基本

期間での絞り込み結合

2024年1月の注文だけを、顧客テーブルと結合して表示してください。

※横にスクロールできます

SELECT 
    c.customer_name AS 顧客名,
    o.order_date AS 注文日,
    o.total_amount AS 金額
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01' 
  AND o.order_date < '2024-02-01'
ORDER BY o.order_date;

解説:

WHERE句で期間を絞り込む
< '2024-02-01' で1月末までを指定

問題 3基本

価格範囲での結合

りんごと価格が±50円以内の商品を探してください。（りんご自身は除外）

※横にスクロールできます

SELECT 
    p1.product_name AS 基準商品,
    p1.price AS 基準価格,
    p2.product_name AS 類似商品,
    p2.price AS 類似価格,
    ABS(p2.price - p1.price) AS 価格差
FROM products p1
JOIN products p2 
    ON p2.price BETWEEN p1.price - 50 AND p1.price + 50
    AND p1.product_id != p2.product_id
WHERE p1.product_name = 'りんご'
ORDER BY 価格差;

解説:

BETWEEN ... AND ... で範囲指定
!= p2.product_id で自分自身を除外
ABS() で価格差の絶対値を計算

問題 4応用

同じカテゴリで価格比較

各商品に対して、同じカテゴリで次に高い価格の商品を表示してください。

※横にスクロールできます

SELECT 
    p1.product_name AS 商品,
    p1.price AS 価格,
    p1.category AS カテゴリ,
    (
        SELECT p2.product_name
        FROM products p2
        WHERE p2.category = p1.category
          AND p2.price > p1.price
        ORDER BY p2.price
        LIMIT 1
    ) AS 次のグレード商品
FROM products p1
ORDER BY p1.category, p1.price;

解説:

相関サブクエリで「同カテゴリ & より高い」商品を探す
ORDER BY p2.price LIMIT 1 で最も近い価格を取得

問題 5応用

複合キーでの結合

月次売上データを作成し、年月とproduct_idの組み合わせで商品テーブルと結合してください。

※横にスクロールできます

WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', order_date) AS year_month,
        product_id,
        SUM(total_amount) AS total_sales,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY strftime('%Y-%m', order_date), product_id
)
SELECT 
    ms.year_month AS 年月,
    p.product_name AS 商品名,
    ms.total_sales AS 月次売上,
    ms.order_count AS 注文数
FROM monthly_sales ms
JOIN products p ON ms.product_id = p.product_id
ORDER BY ms.year_month DESC, ms.total_sales DESC
LIMIT 20;

解説:

CTEで月次売上を事前に集計
product_idで商品テーブルと結合

問題 6応用

累積計算（不等号結合）

顧客ID = 1 の各注文に対して、その注文までの累積購入額を計算してください。

※横にスクロールできます

SELECT 
    o1.order_id AS 注文ID,
    o1.order_date AS 注文日,
    o1.total_amount AS 金額,
    SUM(o2.total_amount) AS 累積購入額
FROM orders o1
JOIN orders o2 
    ON o1.customer_id = o2.customer_id
    AND o2.order_date <= o1.order_date
WHERE o1.customer_id = 1
GROUP BY o1.order_id, o1.order_date, o1.total_amount
ORDER BY o1.order_date;

解説:

o2.order_date <= o1.order_date → この注文以前を結合
ウィンドウ関数の方が効率的（SUM() OVER）

問題 7チャレンジ

よく一緒に買われる商品

同じ注文で購入された商品のペアを見つけ、同時購入回数を表示してください。（2回以上）

※横にスクロールできます

SELECT 
    p1.product_name AS 商品1,
    p2.product_name AS 商品2,
    COUNT(DISTINCT o1.order_id) AS 同時購入回数
FROM order_items o1
JOIN order_items o2 
    ON o1.order_id = o2.order_id
    AND o1.product_id < o2.product_id
JOIN products p1 ON o1.product_id = p1.product_id
JOIN products p2 ON o2.product_id = p2.product_id
GROUP BY p1.product_id, p2.product_id, p1.product_name, p2.product_name
HAVING COUNT(DISTINCT o1.order_id) >= 2
ORDER BY 同時購入回数 DESC
LIMIT 10;

解説:

o1.order_id = o2.order_id → 同じ注文内
o1.product_id < o2.product_id → 重複ペアを避ける
レコメンドシステムの基礎!

問題 8チャレンジ

初回と2回目の購入間隔

各顧客の初回購入日と2回目購入日、その間隔（日数）を表示してください。

※横にスクロールできます

WITH ranked_orders AS (
    SELECT 
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date
        ) AS order_rank
    FROM orders
)
SELECT 
    c.customer_name AS 顧客名,
    o1.order_date AS 初回購入日,
    o2.order_date AS 二回目購入日,
    CAST(julianday(o2.order_date) - julianday(o1.order_date) AS INTEGER) AS 購入間隔日数
FROM customers c
JOIN ranked_orders o1 
    ON c.customer_id = o1.customer_id 
    AND o1.order_rank = 1
JOIN ranked_orders o2 
    ON c.customer_id = o2.customer_id 
    AND o2.order_rank = 2
ORDER BY 購入間隔日数
LIMIT 10;

解説:

ROW_NUMBER()で各顧客の注文に順位をつける
rank = 1（初回）と rank = 2（2回目）を別々に結合

問題 9チャレンジ

期間重複の検出

キャンペーン期間が重複しているペアを見つけてください。

※横にスクロールできます

WITH campaigns AS (
    SELECT 1 AS id, '新春セール' AS name, '2024-01-01' AS start_date, '2024-01-31' AS end_date
    UNION ALL SELECT 2, '冬の大感謝祭', '2024-01-15', '2024-02-15'
    UNION ALL SELECT 3, '春の新生活セール', '2024-03-01', '2024-03-31'
)
SELECT 
    c1.name AS キャンペーン1,
    c1.start_date || ' 〜 ' || c1.end_date AS 期間1,
    c2.name AS キャンペーン2,
    c2.start_date || ' 〜 ' || c2.end_date AS 期間2
FROM campaigns c1
JOIN campaigns c2 
    ON c1.id < c2.id
    AND c1.start_date <= c2.end_date
    AND c1.end_date >= c2.start_date;

解説:

期間重複の条件: 開始1 ≤ 終了2 かつ終了1 ≥ 開始2
c1.id < c2.id で同じペアの重複を防ぐ

問題 10チャレンジ

在庫切れ予測

在庫が10未満で、直近30日の販売ペースだと10日以内に在庫切れになりそうな商品を探してください。

※横にスクロールできます

WITH recent_sales AS (
    SELECT 
        product_id,
        COUNT(*) AS sales_count,
        COUNT(*) * 1.0 / 30 AS daily_avg
    FROM orders
    WHERE order_date >= DATE('now', '-30 days')
    GROUP BY product_id
)
SELECT 
    p.product_name AS 商品名,
    p.stock AS 現在庫,
    ROUND(rs.daily_avg, 2) AS 日平均販売数,
    ROUND(p.stock / rs.daily_avg, 1) AS 在庫日数
FROM products p
JOIN recent_sales rs ON p.product_id = rs.product_id
WHERE p.stock < 10
  AND p.stock / rs.daily_avg < 10
ORDER BY 在庫日数;

解説:

直近30日の販売数から日平均を計算
在庫 ÷ 日平均 = 何日で在庫切れか
実務でよく使う分析パターン!

問題 11チャレンジ

顧客の総合分析

各顧客の総購入額、購入回数、最終購入日、最頻購入カテゴリを表示してください。

※横にスクロールできます

WITH customer_stats AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS total_amount,
        MAX(o.order_date) AS last_order_date
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
),
favorite_category AS (
    SELECT 
        o.customer_id,
        p.category,
        COUNT(*) AS category_count,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id 
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY o.customer_id, p.category
)
SELECT 
    cs.customer_name AS 顧客名,
    cs.order_count AS 購入回数,
    cs.total_amount AS 総購入額,
    cs.last_order_date AS 最終購入日,
    fc.category AS 最頻購入カテゴリ
FROM customer_stats cs
LEFT JOIN favorite_category fc 
    ON cs.customer_id = fc.customer_id 
    AND fc.rank = 1
ORDER BY cs.total_amount DESC
LIMIT 20;

解説:

CTEを2つ使って複雑な分析を分割
最頻カテゴリはROW_NUMBER()でランク1位を取得
実務で非常によく使うパターン!

問題 12チャレンジ

キャンペーン効果分析

キャンペーン期間内の注文とキャンペーン期間外の注文を比較し、平均購入額の違いを分析してください。

※横にスクロールできます

WITH campaigns AS (
    SELECT '新春セール' AS name, '2024-01-01' AS start_date, '2024-01-31' AS end_date
    UNION ALL SELECT '夏祭りセール', '2024-07-01', '2024-08-31'
    UNION ALL SELECT '年末セール', '2024-12-01', '2024-12-31'
),
order_campaign AS (
    SELECT 
        o.order_id,
        o.total_amount,
        CASE 
            WHEN c.name IS NOT NULL THEN 'キャンペーン中'
            ELSE '通常期間'
        END AS period_type,
        c.name AS campaign_name
    FROM orders o
    LEFT JOIN campaigns c 
        ON o.order_date >= c.start_date 
        AND o.order_date <= c.end_date
)
SELECT 
    period_type AS 期間区分,
    COUNT(*) AS 注文数,
    ROUND(AVG(total_amount), 0) AS 平均購入額,
    SUM(total_amount) AS 合計売上
FROM order_campaign
GROUP BY period_type;

解説:

LEFT JOINでキャンペーン期間を結合
CASE式で「キャンペーン中」「通常期間」に分類
キャンペーンの効果を数値で確認できる

📝 Step 11 のまとめ

✅ 学んだこと

複数条件での結合：ANDで条件を組み合わせる
不等号での結合：範囲、期間、大小関係でマッチング
期間の重複判定：開始1 ≤ 終了2 かつ終了1 ≥ 開始2
結合の順序：先に絞り込んでから結合すると高速
複雑なクエリ：CTEで段階的に構築

📌 重要な構文パターン

※横にスクロールできます

-- 複数条件での結合
SELECT * FROM t1
JOIN t2 
    ON t1.col1 = t2.col1 
    AND t1.col2 = t2.col2;

-- 期間内の結合
SELECT * FROM orders o
JOIN campaigns c 
    ON o.order_date >= c.start_date 
    AND o.order_date <= c.end_date;

-- 期間の重複判定
SELECT * FROM c1
JOIN c2 
    ON c1.start_date <= c2.end_date 
    AND c1.end_date >= c2.start_date;

-- 先に絞り込んでから結合（高速）
SELECT * FROM (
    SELECT * FROM orders WHERE order_date >= '2024-01-01'
) o
JOIN customers c ON o.customer_id = c.customer_id;

⚠️ 注意点

不等号結合は結果が膨大になりやすい
関数を使った結合はインデックスが効かない
複雑すぎるクエリはCTEで分割する
ウィンドウ関数で代替できないか常に検討

❓ よくある質問

Q1: 複数条件のON句とWHERE句の違いは?

ON句の条件は結合の条件、WHERE句の条件は結果の絞り込みです。

INNER JOINでは結果は同じですが、LEFT JOINでは違いが出ます:

書き方	動作
ON句に条件	条件に合わない行もNULLで残る
WHERE句に条件	条件に合わない行は除外される

Q2: 不等号結合は遅いですか?

遅くなりやすいです。インデックスが効きにくく、結果の行数も増えやすいためです。累積計算などはウィンドウ関数で代替できないか検討しましょう。

Q3: 期間の重複判定の条件が覚えられません

「開始1 ≤ 終了2 かつ終了1 ≥ 開始2」と覚えてください。これは「期間1が始まる前に期間2が終わっていない」かつ「期間1が終わる前に期間2が始まっている」ことを確認しています。

Q4: JOINの順序は自分で決められますか?

最近のDBMSは自動で最適化してくれます。ただし、意図的に順序を制御したい場合はサブクエリやCTEを使って先に絞り込むことで実現できます。

Q5: CTEは必須ですか?

必須ではありませんが、複雑なクエリには強く推奨します。可読性が上がり、デバッグしやすく、メンテナンスしやすくなります。また、同じ計算を複数回使う場合にも便利です。

Q6: 実務で最も使う結合パターンは?

期間でのマッチング（キャンペーン、会員ランク、価格履歴）と複合キーでの結合（年月+商品ID、店舗ID+日付）が頻出します。不等号結合は特殊なケースで使います。

🎓 次のステップでは

Step 12: CASE文と条件分岐では、SQLで条件によって異なる値を返す方法を学びます。データの分類、集計の分岐、NULLの処理など、実務で必須のテクニックです!

📝

学習メモ

SQL応用・パフォーマンス最適化 - Step 11

📋 過去のメモ一覧 ▼