🎯 Step 5: スカラーサブクエリと応用

SELECT句でサブクエリを使いこなそう!

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

スカラーサブクエリとは何か
SELECT句でのサブクエリの使い方
相関スカラーサブクエリ（行ごとに異なる計算）
CASE式との組み合わせ
サブクエリの最適化テクニック
実務での活用パターン

🎯 1. スカラーサブクエリとは?

1-1. これまでのサブクエリの復習

これまで学んだサブクエリは、主に以下の場所で使っていました。

WHERE句（Step 2）: 条件の値を計算 → WHERE price > (SELECT AVG(price) ...)
FROM句（Step 3）: 一時的なテーブルを作成 → FROM (SELECT ...) AS サブクエリ名

今回学ぶスカラーサブクエリは、SELECT句の中でサブクエリを使う方法です。

1-2. スカラーサブクエリとは

スカラー（Scalar）とは「1つの値」という意味です。スカラーサブクエリは、1つの値だけを返すサブクエリのことを指します。

💡 スカラーサブクエリのイメージ

日常生活で例えると、「全体の平均点は?」という質問に対して「75点」と1つの値で答えるようなものです。

「全体の平均価格は?」 → 「1,500円」（1つの値）
「最高価格は?」 → 「10,000円」（1つの値）
「商品数は?」 → 「100個」（1つの値）

このように、1つの値を返すサブクエリをSELECT句に書くことで、各行にその値を追加できます。

1-3. スカラーサブクエリの基本構文

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

— スカラーサブクエリの基本形
SELECT 
    列名1,
    列名2,
    (SELECT 集計関数 FROM テーブル WHERE 条件) AS 別名
FROM テーブル名;

構文の解説:

( ) → サブクエリを括弧で囲む
SELECT 集計関数 → AVG、SUM、COUNT、MAX、MINなど、1つの値を返す
AS 別名 → サブクエリの結果に名前をつける

重要なルール:

スカラーサブクエリは必ず1行1列（1つの値）を返す必要があります。2行以上返すとエラーになります。

1-4. スカラーサブクエリが使える場所

スカラーサブクエリは、SELECT句だけでなく、1つの値が必要な場所ならどこでも使えます。

スカラーサブクエリが使える場所:

SELECT句: 各行に計算結果を追加（今回の主なテーマ）
WHERE句: 条件の値として使用（Step 2で学習済み）
CASE式の条件: 分岐の条件として使用
計算式の中: price - (SELECT AVG(price) ...)

📊 2. 基本的なスカラーサブクエリ

2-1. 例1：各商品に平均価格を追加

やりたいこと：各商品の情報と一緒に、全商品の平均価格を表示する

まず、通常のクエリで商品情報を取得します。

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

— 通常のクエリ（平均価格なし）
SELECT product_name, price
FROM products
LIMIT 5;

結果イメージ（平均価格なし）:

product_name	price
りんご	150
バナナ	120
みかん	180

ここに「全商品の平均価格」を追加したい場合、スカラーサブクエリを使います。

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

— スカラーサブクエリで平均価格を追加
SELECT 
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price
FROM products
LIMIT 5;

クエリの解説:

SELECT AVG(price) FROM products → 全商品の平均価格を計算（1つの値）
AS avg_price → その結果に「avg_price」という名前をつける
この平均価格は全ての行に同じ値で表示される

結果イメージ（平均価格あり）:

product_name	price	avg_price
りんご	150	324.5
バナナ	120	324.5
みかん	180	324.5

全ての行に同じ平均価格（324.5円）が表示されます。

💡 これが便利な理由

各商品の価格と平均価格が同じ行に表示されるので、「この商品は平均より高いか低いか」がすぐに分かります!

2-2. 例2：平均との差額を計算

やりたいこと：各商品の価格と、平均価格との差額を計算する

スカラーサブクエリの結果は、計算式の中でも使えます。

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

SELECT 
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,
    price – (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC
LIMIT 5;

クエリの解説:

(SELECT AVG(price) FROM products) → 平均価格を取得
price - (SELECT AVG(price) FROM products) → 各商品の価格から平均を引く
AS diff_from_avg → 差額に名前をつける
ORDER BY diff_from_avg DESC → 差額が大きい順（平均より高い順）に並べる

結果イメージ:

product_name	price	avg_price	diff_from_avg
高級メロン	5,000	324.5	4,675.5
まぐろ	3,000	324.5	2,675.5
和牛	2,500	324.5	2,175.5

📌 注意：同じサブクエリを2回書いている

上の例では、同じサブクエリを2回書いています。これは非効率で、実務ではWITH句（CTE）を使って1回だけ計算する方法があります（後のセクションで学びます）。

2-3. 例3：最大値・最小値との比較

やりたいこと：各商品と、最高価格・最低価格を比較する

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

SELECT 
    product_name,
    price,
    (SELECT MAX(price) FROM products) AS max_price,
    (SELECT MIN(price) FROM products) AS min_price,
    (SELECT MAX(price) FROM products) – price AS diff_from_max
FROM products
ORDER BY price DESC
LIMIT 5;

クエリの解説:

MAX(price) → 最高価格を取得
MIN(price) → 最低価格を取得
MAX(price) - price → 最高価格との差額を計算

使える集計関数:

AVG、SUM、COUNT、MAX、MIN などの集計関数は、必ず1つの値を返すのでスカラーサブクエリに最適です。

2-4. 例4：全体に対する割合を計算

やりたいこと：各商品の価格が、全体の合計に占める割合を計算する

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

SELECT 
    product_name,
    price,
    (SELECT SUM(price) FROM products) AS total_price,
    ROUND(price * 100.0 / (SELECT SUM(price) FROM products), 2) AS percentage
FROM products
ORDER BY percentage DESC
LIMIT 5;

クエリの解説:

SUM(price) → 全商品の価格の合計
price * 100.0 / ... → パーセンテージを計算（100.0をかけて小数点を含む計算にする）
ROUND(..., 2) → 小数点以下2桁に丸める

活用シーン:

売上全体に対する各商品の貢献度を分析
予算全体に対する各項目の割合を計算
全体の中での位置づけを把握

🔗 3. 相関スカラーサブクエリ

3-1. 相関スカラーサブクエリとは

これまでの例では、サブクエリは全ての行に対して同じ値（全体の平均など）を返していました。しかし、相関スカラーサブクエリを使うと、行ごとに異なる値を計算できます。

💡 相関スカラーサブクエリのイメージ

「全体の平均」ではなく「同じカテゴリの平均」を計算したい場合を考えてみましょう。

りんご（果物カテゴリ）→ 果物の平均価格と比較
牛肉（肉カテゴリ）→ 肉の平均価格と比較
パソコン（家電カテゴリ）→ 家電の平均価格と比較

このように、行ごとに異なる値を計算するのが相関スカラーサブクエリです。

3-2. 例1：同じカテゴリの平均価格を表示

やりたいこと：各商品と、その商品が属するカテゴリの平均価格を一緒に表示する

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

SELECT 
    p1.product_name,
    p1.category,
    p1.price,
    (
        SELECT AVG(p2.price) 
        FROM products p2 
        WHERE p2.category = p1.category
    ) AS category_avg_price
FROM products p1
LIMIT 10;

クエリの解説:

FROM products p1 → 外側のクエリ。productsテーブルに「p1」という別名をつける
FROM products p2 → サブクエリ内。同じテーブルに「p2」という別名をつける
WHERE p2.category = p1.category → 相関部分! 外側の行（p1）と同じカテゴリの商品だけを対象にする
AVG(p2.price) → そのカテゴリの平均価格を計算

なぜ別名（p1, p2）が必要か:

同じテーブル（products）を外側とサブクエリの両方で使っているため、区別するために別名が必要です。

結果イメージ:

product_name	category	price	category_avg_price
りんご	果物	150	250.0
バナナ	果物	120	250.0
牛肉	肉	800	650.0
豚肉	肉	500	650.0

果物カテゴリは「果物の平均」、肉カテゴリは「肉の平均」と、カテゴリごとに異なる平均が表示されています。

3-3. 例2：顧客ごとの注文統計

やりたいこと：各顧客の名前と、その顧客の注文回数・総購入額を表示する

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

SELECT 
    customer_name,
    (
        SELECT COUNT(*) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS order_count,
    (
        SELECT SUM(total_amount) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS total_spent
FROM customers
ORDER BY total_spent DESC NULLS LAST
LIMIT 10;

クエリの解説:

WHERE o.customer_id = customers.customer_id → 相関部分。外側の顧客と同じcustomer_idの注文を対象にする
COUNT(*) → その顧客の注文件数をカウント
SUM(total_amount) → その顧客の総購入額を計算
NULLS LAST → NULLを最後に表示（注文がない顧客を最後に）

結果イメージ:

customer_name	order_count	total_spent
田中太郎	25	350,000
鈴木花子	18	220,000
佐藤次郎	5	45,000

3-4. 例3：商品ごとの販売実績

やりたいこと：各商品の名前と、その商品の販売回数・総売上を表示する

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

SELECT 
    p.product_name,
    p.price,
    (
        SELECT COUNT(*) 
        FROM order_details od 
        WHERE od.product_id = p.product_id
    ) AS sales_count,
    (
        SELECT SUM(od.quantity * od.unit_price) 
        FROM order_details od 
        WHERE od.product_id = p.product_id
    ) AS total_sales
FROM products p
ORDER BY total_sales DESC NULLS LAST
LIMIT 10;

クエリの解説:

WHERE od.product_id = p.product_id → 外側の商品と同じproduct_idの注文明細を対象にする
COUNT(*) → 販売回数（何回注文されたか）
SUM(od.quantity * od.unit_price) → 総売上（数量 × 単価の合計）

活用シーン:

商品の売れ筋ランキングを作成
販売実績のない商品を特定（sales_countが0またはNULL）
価格と販売回数の関係を分析

🔗 4. JOINとの組み合わせ

4-1. JOINした結果にスカラーサブクエリを追加

スカラーサブクエリは、JOINと組み合わせることでさらに便利になります。複数のテーブルを結合した結果に、追加の統計情報を加えられます。

やりたいこと：各注文の情報と、その顧客の累計注文回数・累計購入額を表示する

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

SELECT 
    c.customer_name,
    o.order_date,
    o.total_amount,
    (
        SELECT COUNT(*) 
        FROM orders o2 
        WHERE o2.customer_id = c.customer_id
    ) AS total_order_count,
    (
        SELECT SUM(total_amount) 
        FROM orders o3 
        WHERE o3.customer_id = c.customer_id
    ) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC
LIMIT 10;

クエリの解説:

INNER JOIN orders o ON ... → customersとordersを結合
各注文行に対して、その顧客の「累計注文回数」と「累計購入額」を追加
結合結果の各行に、スカラーサブクエリで計算した値が追加される

結果イメージ:

customer_name	order_date	total_amount	total_order_count	total_spent
田中太郎	2024-03-15	15,000	25	350,000
鈴木花子	2024-03-14	8,000	18	220,000
田中太郎	2024-03-10	12,000	25	350,000

同じ顧客（田中太郎）の複数の注文が表示されていますが、累計は同じ値になっています。

4-2. 最終購入日を追加

やりたいこと：各顧客の情報と、最終購入日を表示する

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

SELECT 
    customer_name,
    email,
    (
        SELECT MAX(order_date) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS last_order_date
FROM customers
ORDER BY last_order_date DESC NULLS LAST;

クエリの解説:

MAX(order_date) → その顧客の最新の注文日を取得
注文がない顧客は last_order_date がNULLになる
NULLS LAST → 注文がない顧客を最後に表示

活用シーン:

休眠顧客の特定（最終購入日が古い顧客）
アクティブ顧客のリスト作成
顧客の購買サイクルの分析

4-3. 複数の統計を一度に追加

やりたいこと：各商品の情報と、販売回数、総売上、平均購入額を表示する

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

SELECT 
    p.product_name,
    p.category,
    p.price,
    (
        SELECT COUNT(*) 
        FROM order_details od 
        WHERE od.product_id = p.product_id
    ) AS sales_count,
    (
        SELECT SUM(od.quantity * od.unit_price) 
        FROM order_details od 
        WHERE od.product_id = p.product_id
    ) AS total_sales,
    (
        SELECT AVG(od.quantity * od.unit_price) 
        FROM order_details od 
        WHERE od.product_id = p.product_id
    ) AS avg_sales_per_order
FROM products p
ORDER BY total_sales DESC NULLS LAST
LIMIT 10;

クエリの解説:

1つのクエリで3つの統計（販売回数、総売上、平均売上）を計算
各スカラーサブクエリが異なる集計関数を使用
COUNT(*) → 販売回数
SUM(...) → 総売上
AVG(...) → 平均売上

💡 スカラーサブクエリのメリット

読みやすい：各統計が何を計算しているか明確
柔軟：複数の統計を自由に追加できる
複雑なJOINを避けられる：GROUP BYなしで統計を取得

📌 パフォーマンスの注意

スカラーサブクエリは便利ですが、各行ごとにサブクエリが実行されるため、データ量が多いと遅くなる可能性があります。パフォーマンスが気になる場合は、JOINやCTEを使った方法を検討しましょう（次のセクションで学びます）。

🎨 5. CASE式との組み合わせ

5-1. CASE式の条件にサブクエリを使う

CASE式の条件にスカラーサブクエリを使うと、動的な条件分岐ができます。「平均より高いか低いか」などの判定が可能です。

やりたいこと：各商品が平均価格以上なら「高価格」、未満なら「低価格」と表示する

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

SELECT 
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,
    CASE 
        WHEN price >= (SELECT AVG(price) FROM products) 
            THEN ‘高価格’
        ELSE ‘低価格’
    END AS price_category
FROM products
ORDER BY price DESC
LIMIT 10;

クエリの解説:

CASE WHEN ... THEN ... ELSE ... END → 条件分岐
WHEN price >= (SELECT AVG(price) FROM products) → 平均価格以上かどうかを判定
THEN '高価格' → 条件を満たす場合は「高価格」
ELSE '低価格' → それ以外は「低価格」

結果イメージ:

product_name	price	avg_price	price_category
高級メロン	5,000	324.5	高価格
りんご	150	324.5	低価格

5-2. 複数段階の分類

やりたいこと：価格を平均の倍率で5段階に分類する

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

SELECT 
    product_name,
    price,
    CASE 
        WHEN price >= (SELECT AVG(price) * 2 FROM products) 
            THEN ‘超高級品’
        WHEN price >= (SELECT AVG(price) * 1.5 FROM products) 
            THEN ‘高級品’
        WHEN price >= (SELECT AVG(price) FROM products) 
            THEN ‘標準より高め’
        WHEN price >= (SELECT AVG(price) * 0.5 FROM products) 
            THEN ‘標準’
        ELSE ‘お買い得’
    END AS price_rank
FROM products
ORDER BY price DESC
LIMIT 10;

クエリの解説:

AVG(price) * 2 → 平均の2倍以上 = 超高級品
AVG(price) * 1.5 → 平均の1.5倍以上 = 高級品
AVG(price) → 平均以上 = 標準より高め
AVG(price) * 0.5 → 平均の半分以上 = 標準
それ以外 = お買い得

注意:

CASEは上から順に評価されるので、条件の順番が重要です。最も厳しい条件（超高級品）を最初に書きます。

5-3. 顧客ランクの判定

やりたいこと：顧客の総購入額に応じてランクを判定する

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

SELECT 
    customer_name,
    (
        SELECT SUM(total_amount) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS total_spent,
    CASE 
        WHEN (
            SELECT SUM(total_amount) 
            FROM orders o 
            WHERE o.customer_id = customers.customer_id
        ) >= 100000 THEN ‘プラチナ’
        WHEN (
            SELECT SUM(total_amount) 
            FROM orders o 
            WHERE o.customer_id = customers.customer_id
        ) >= 50000 THEN ‘ゴールド’
        WHEN (
            SELECT SUM(total_amount) 
            FROM orders o 
            WHERE o.customer_id = customers.customer_id
        ) >= 10000 THEN ‘シルバー’
        ELSE ‘ブロンズ’
    END AS customer_rank
FROM customers
ORDER BY total_spent DESC NULLS LAST;

クエリの解説:

各顧客の総購入額を相関サブクエリで計算
10万円以上 = プラチナ
5万円以上 = ゴールド
1万円以上 = シルバー
それ以外 = ブロンズ

⚠️ 問題：同じサブクエリを4回書いている!

上の例では、同じサブクエリ SELECT SUM(total_amount) ... を4回書いています。これは非効率です。同じ計算が4回実行されてしまいます。次のセクションで、この問題を解決する方法を学びます。

5-4. 活動状況の判定（日付による分類）

やりたいこと：顧客の最終購入日に応じて活動状況を判定する

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

SELECT 
    customer_name,
    (
        SELECT MAX(order_date) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS last_order_date,
    CASE 
        WHEN (
            SELECT MAX(order_date) 
            FROM orders o 
            WHERE o.customer_id = customers.customer_id
        ) >= DATE(‘now’, ‘-30 days’) THEN ‘アクティブ’
        WHEN (
            SELECT MAX(order_date) 
            FROM orders o 
            WHERE o.customer_id = customers.customer_id
        ) >= DATE(‘now’, ‘-90 days’) THEN ‘休眠気味’
        WHEN (
            SELECT MAX(order_date) 
            FROM orders o 
            WHERE o.customer_id = customers.customer_id
        ) IS NOT NULL THEN ‘休眠中’
        ELSE ‘未購入’
    END AS activity_status
FROM customers
ORDER BY last_order_date DESC NULLS LAST;

クエリの解説:

DATE('now', '-30 days') → 30日前の日付（SQLiteの書き方）
最終購入が30日以内 = アクティブ
最終購入が90日以内 = 休眠気味
購入履歴あり = 休眠中
購入履歴なし = 未購入

MySQLの場合:

DATE_SUB(CURDATE(), INTERVAL 30 DAY) を使います。

⚡ 6. 最適化テクニック

6-1. 問題：同じサブクエリを何度も書く

これまでの例で見たように、同じサブクエリを複数回書くと非効率です。データベースは同じ計算を何度も実行してしまいます。

❌ 非効率な例（同じサブクエリを4回書いている）

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

SELECT 
    customer_name,
    (SELECT SUM(total_amount) FROM orders WHERE customer_id = customers.customer_id) AS total,
    CASE 
        WHEN (SELECT SUM(total_amount) FROM orders WHERE customer_id = customers.customer_id) >= 100000 THEN ‘VIP’
        WHEN (SELECT SUM(total_amount) FROM orders WHERE customer_id = customers.customer_id) >= 50000 THEN ‘優良’
        ELSE ‘一般’
    END AS rank
FROM customers;

6-2. 解決策1：WITH句（CTE）を使う

WITH句（Common Table Expression、CTE）を使うと、サブクエリの結果を一時的に保存して再利用できます。

✅ WITH句を使った効率的な書き方

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

WITH customer_totals AS (
    — 顧客ごとの総購入額を1回だけ計算
    SELECT 
        customer_id,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT 
    c.customer_name,
    COALESCE(ct.total_spent, 0) AS total_spent,
    CASE 
        WHEN ct.total_spent >= 100000 THEN ‘VIP’
        WHEN ct.total_spent >= 50000 THEN ‘優良’
        ELSE ‘一般’
    END AS customer_rank
FROM customers c
LEFT JOIN customer_totals ct ON c.customer_id = ct.customer_id
ORDER BY total_spent DESC;

クエリの解説:

WITH customer_totals AS (...) → 一時的なテーブル「customer_totals」を作成
サブクエリの計算は1回だけ実行される
LEFT JOIN customer_totals ct → その結果をJOINで使う
COALESCE(ct.total_spent, 0) → 注文がない顧客は0として扱う

メリット:

同じ計算を1回だけ実行 → 高速
コードが読みやすくなる
同じ結果を複数の場所で使える

6-3. 解決策2：JOINに置き換える

スカラーサブクエリをJOIN + GROUP BYに置き換えると、パフォーマンスが向上することがあります。

❌ 遅い可能性がある書き方（スカラーサブクエリ）

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

SELECT 
    c.customer_name,
    (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) AS order_count,
    (SELECT SUM(total_amount) FROM orders WHERE customer_id = c.customer_id) AS total
FROM customers c;

✅ 速い書き方（JOIN + GROUP BY）

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

SELECT 
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total DESC NULLS LAST;

なぜJOINの方が速いのか:

スカラーサブクエリ: 顧客数 × サブクエリ数回の計算が実行される
JOIN: 1回のJOINと1回のGROUP BYで完了
データベースの最適化機能が効きやすい

6-4. 解決策3：EXISTSを使う（存在チェック）

「存在するかどうか」だけをチェックしたい場合は、EXISTSが最適です。

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

SELECT 
    customer_name,
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM orders 
            WHERE customer_id = customers.customer_id
        ) THEN ‘購入あり’
        ELSE ‘未購入’
    END AS purchase_status
FROM customers;

クエリの解説:

EXISTS (...) → サブクエリの結果が1件でもあればTRUE
1件見つかった時点で処理を終了するので効率的
COUNTで件数を数えるより速い

6-5. 最適化の判断基準

📊 どの方法を使うべきか?

状況	推奨方法	理由
データ量が少ない（数百件）	スカラーサブクエリ	読みやすさを優先
データ量が多い（数万件以上）	JOIN + GROUP BY	パフォーマンス重視
同じ計算を複数回使う	WITH句（CTE）	計算を1回に減らせる
存在チェックのみ	EXISTS	最も効率的
行ごとに異なる計算が必要	相関スカラーサブクエリ	他の方法では難しい

💡 実務でのアドバイス

まずは読みやすさを優先してコードを書く
遅いと感じたら最適化を検討する
EXPLAINで実行計画を確認する（後のステップで学習）
同じ結果を得られる複数の書き方を知っておくことが大切

💼 7. 実務での活用パターン

7-1. ダッシュボード用の顧客統計

実務では、顧客ごとの詳細な統計をダッシュボードに表示することがよくあります。

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

— 顧客ダッシュボード用のデータ
SELECT 
    c.customer_name,
    c.prefecture,
    (
        SELECT COUNT(*) 
        FROM orders o 
        WHERE o.customer_id = c.customer_id
    ) AS order_count,
    (
        SELECT SUM(total_amount) 
        FROM orders o 
        WHERE o.customer_id = c.customer_id
    ) AS total_spent,
    (
        SELECT AVG(total_amount) 
        FROM orders o 
        WHERE o.customer_id = c.customer_id
    ) AS avg_order_amount,
    (
        SELECT MAX(order_date) 
        FROM orders o 
        WHERE o.customer_id = c.customer_id
    ) AS last_order_date
FROM customers c
ORDER BY total_spent DESC NULLS LAST
LIMIT 20;

活用シーン:

顧客管理システムのダッシュボード
営業担当者向けの顧客リスト
マーケティング分析用のデータ抽出

7-2. 商品分析レポート

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

— 商品分析レポート
SELECT 
    p.product_name,
    p.category,
    p.price,
    p.stock,
    (
        SELECT COUNT(*) 
        FROM order_details od 
        WHERE od.product_id = p.product_id
    ) AS sales_count,
    (
        SELECT SUM(od.quantity) 
        FROM order_details od 
        WHERE od.product_id = p.product_id
    ) AS total_quantity_sold,
    (
        SELECT AVG(p2.price) 
        FROM products p2 
        WHERE p2.category = p.category
    ) AS category_avg_price,
    CASE 
        WHEN p.stock = 0 THEN ‘在庫切れ’
        WHEN p.stock < 10 THEN '在庫少'
        ELSE '在庫あり'
    END AS stock_status
FROM products p
ORDER BY sales_count DESC NULLS LAST
LIMIT 20;

活用シーン:

在庫管理レポート
商品の売れ筋分析
発注判断のためのデータ

7-3. 比較分析レポート

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

— 全体平均とカテゴリ平均との比較
SELECT 
    product_name,
    category,
    price,
    (SELECT AVG(price) FROM products) AS overall_avg,
    (
        SELECT AVG(price) 
        FROM products p2 
        WHERE p2.category = products.category
    ) AS category_avg,
    ROUND(
        price * 100.0 / (SELECT AVG(price) FROM products), 
        1
    ) AS pct_of_overall,
    ROUND(
        price * 100.0 / (
            SELECT AVG(price) 
            FROM products p2 
            WHERE p2.category = products.category
        ), 
        1
    ) AS pct_of_category
FROM products
ORDER BY category, price DESC;

クエリの解説:

pct_of_overall → 全体平均に対する割合（%）
pct_of_category → カテゴリ平均に対する割合（%）
100%を超えていれば平均より高い、100%未満なら平均より低い

📝 練習問題

スカラーサブクエリを使いこなせるようになりましょう!

問題 1基本

平均価格の表示

全商品の情報と、全商品の平均価格を同時に表示してください。

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

SELECT 
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price
FROM products
LIMIT 10;

解説:

SELECT AVG(price) FROM products → 全商品の平均価格を計算
この値は全ての行に同じ値で表示される
スカラーサブクエリは必ず1つの値を返す

問題 2基本

最高価格との差

各商品の価格と、最高価格との差額を表示してください。

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

SELECT 
    product_name,
    price,
    (SELECT MAX(price) FROM products) AS max_price,
    (SELECT MAX(price) FROM products) – price AS diff_from_max
FROM products
ORDER BY price DESC;

解説:

MAX(price) → 最高価格を取得
MAX(price) - price → 最高価格からその商品の価格を引く
最高価格の商品は差が0になる

問題 3基本

商品の販売回数

各商品の名前と、その商品が注文された回数を表示してください。

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

SELECT 
    product_name,
    price,
    (
        SELECT COUNT(*) 
        FROM order_details od 
        WHERE od.product_id = products.product_id
    ) AS sales_count
FROM products
ORDER BY sales_count DESC;

解説:

WHERE od.product_id = products.product_id → 相関部分
外側の各商品に対して、その商品の注文件数をカウント
商品ごとに異なる値が計算される

問題 4応用

顧客の注文統計

各顧客の名前、注文回数、総購入額を表示してください。

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

SELECT 
    customer_name,
    (
        SELECT COUNT(*) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS order_count,
    (
        SELECT SUM(total_amount) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS total_spent
FROM customers
ORDER BY total_spent DESC NULLS LAST;

解説:

2つの相関スカラーサブクエリで異なる統計を計算
COUNT(*) → 注文件数
SUM(total_amount) → 総購入額
NULLS LAST → 注文がない顧客を最後に表示

問題 5応用

カテゴリ平均との比較

各商品の価格と、同じカテゴリの平均価格を表示してください。

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

SELECT 
    p1.product_name,
    p1.category,
    p1.price,
    (
        SELECT AVG(p2.price) 
        FROM products p2 
        WHERE p2.category = p1.category
    ) AS category_avg_price
FROM products p1
ORDER BY category, price DESC;

解説:

WHERE p2.category = p1.category → 同じカテゴリの商品だけを対象
果物は果物の平均、肉は肉の平均…と、カテゴリごとに異なる平均が表示される

問題 6応用

価格帯の判定

各商品が平均価格以上なら「高価格」、未満なら「低価格」と表示してください。

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

SELECT 
    product_name,
    price,
    CASE 
        WHEN price >= (SELECT AVG(price) FROM products) 
            THEN ‘高価格’
        ELSE ‘低価格’
    END AS price_category
FROM products
ORDER BY price DESC;

解説:

CASE WHEN ... THEN ... ELSE ... END → 条件分岐
サブクエリの結果（平均価格）と比較して分類

問題 7応用

最終購入日の表示

各顧客の名前と、その顧客の最終購入日を表示してください。

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

SELECT 
    customer_name,
    (
        SELECT MAX(order_date) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS last_order_date
FROM customers
ORDER BY last_order_date DESC NULLS LAST;

解説:

MAX(order_date) → 最新の注文日を取得
注文がない顧客はNULLになる

問題 8チャレンジ

顧客ランクの判定

各顧客の総購入額に応じて、10万円以上なら「VIP」、5万円以上なら「優良」、それ以外は「一般」と表示してください。

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

SELECT 
    customer_name,
    (
        SELECT SUM(total_amount) 
        FROM orders o 
        WHERE o.customer_id = customers.customer_id
    ) AS total_spent,
    CASE 
        WHEN (
            SELECT SUM(total_amount) 
            FROM orders o 
            WHERE o.customer_id = customers.customer_id
        ) >= 100000 THEN ‘VIP’
        WHEN (
            SELECT SUM(total_amount) 
            FROM orders o 
            WHERE o.customer_id = customers.customer_id
        ) >= 50000 THEN ‘優良’
        ELSE ‘一般’
    END AS customer_rank
FROM customers
ORDER BY total_spent DESC NULLS LAST;

解説:

同じサブクエリを複数回書いている（非効率）
実務ではWITH句（CTE）を使って最適化することを推奨

WITH句を使った最適化版:

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

WITH customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT 
    c.customer_name,
    COALESCE(ct.total_spent, 0) AS total_spent,
    CASE 
        WHEN ct.total_spent >= 100000 THEN ‘VIP’
        WHEN ct.total_spent >= 50000 THEN ‘優良’
        ELSE ‘一般’
    END AS customer_rank
FROM customers c
LEFT JOIN customer_totals ct ON c.customer_id = ct.customer_id
ORDER BY total_spent DESC;

問題 9チャレンジ

全体比率の計算

各商品の価格が、全商品の平均価格の何%かを表示してください。

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

SELECT 
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,
    ROUND(
        price * 100.0 / (SELECT AVG(price) FROM products), 
        1
    ) AS pct_of_avg
FROM products
ORDER BY pct_of_avg DESC;

解説:

price * 100.0 / ... → パーセンテージを計算
ROUND(..., 1) → 小数点以下1桁に丸める
100%なら平均と同じ、200%なら平均の2倍

問題 10チャレンジ

複合統計レポート

各商品の名前、価格、販売回数、総売上、平均購入額を表示してください。

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

SELECT 
    product_name,
    price,
    (
        SELECT COUNT(*) 
        FROM order_details od 
        WHERE od.product_id = products.product_id
    ) AS sales_count,
    (
        SELECT SUM(od.quantity * od.unit_price) 
        FROM order_details od 
        WHERE od.product_id = products.product_id
    ) AS total_sales,
    (
        SELECT AVG(od.quantity * od.unit_price) 
        FROM order_details od 
        WHERE od.product_id = products.product_id
    ) AS avg_sales_per_order
FROM products
ORDER BY total_sales DESC NULLS LAST
LIMIT 10;

解説:

3つの統計を相関スカラーサブクエリで計算
実務でよく使うダッシュボード用のパターン

📝 Step 5 のまとめ

✅ 学んだこと

スカラーサブクエリ: SELECT句でサブクエリを使える
1つの値を返す: 必ず1行1列の結果
相関スカラーサブクエリ: 外側のテーブルを参照して、行ごとに異なる値を計算
CASE式との組み合わせ: 動的な条件分岐が可能
最適化テクニック: WITH句（CTE）やJOINで高速化
実務パターン: ダッシュボードや分析レポートの作成

📌 スカラーサブクエリの基本パターン

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

— 基本形（全行に同じ値）
SELECT 
    列名,
    (SELECT 集計関数 FROM テーブル) AS 別名
FROM テーブル;

— 相関スカラーサブクエリ（行ごとに異なる値）
SELECT 
    t1.列名,
    (
        SELECT 集計関数 
        FROM テーブル t2 
        WHERE t2.列名 = t1.列名
    ) AS 別名
FROM テーブル t1;

— CASE式との組み合わせ
SELECT 
    列名,
    CASE 
        WHEN 値 >= (SELECT AVG(列名) FROM テーブル) THEN ‘ラベルA’
        ELSE ‘ラベルB’
    END AS 分類
FROM テーブル;

💡 実務での使いどころ

統計の追加: 各行に平均、最大、最小などを表示
ダッシュボード: 顧客統計、商品統計を一覧表示
ランク判定: 購入額に応じた顧客ランクなど
比較分析: 全体平均やカテゴリ平均との比較
活動状況: 最終購入日に基づく顧客分類

⚠️ 注意点

2行以上返すとエラーになる
大量データでは遅くなる可能性
同じ計算を複数回するのは非効率
WITH句（CTE）やJOINでの最適化を検討
結果がない場合はNULLになる（COALESCEで対処）

❓ よくある質問

Q1: スカラーサブクエリとJOINの使い分けは?

データ量が少ない場合や読みやすさ重視ならスカラーサブクエリ、データ量が多い場合やパフォーマンス重視ならJOINを使いましょう。

また、同じ計算を複数回行う場合はWITH句（CTE）が効率的です。

Q2: スカラーサブクエリが2行以上返すとどうなりますか?

エラーになります。「Subquery returns more than 1 row」というメッセージが出ます。

必ず集計関数（AVG、SUM、MAX、MIN、COUNT）を使って1つの値にするか、WHERE句で1行に絞り込む必要があります。

Q3: 同じサブクエリを何度も書くのは問題ですか?

はい、非効率です。同じ計算を何度も実行してしまいます。

WITH句（CTE）を使えば、1回の計算結果を再利用できます。コードも読みやすくなります。

Q4: NULLを返すサブクエリの扱いは?

サブクエリの結果が存在しない場合、NULLが返されます。

COALESCE関数を使って、NULLを0や他の値に置き換えることができます。

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

— NULLを0に置き換え
COALESCE((SELECT SUM(amount) …), 0)

Q5: 相関スカラーサブクエリはいつ使うべきですか?

各行ごとに異なる計算が必要な場合に使います。

「各商品の同カテゴリ平均価格」
「各顧客の注文回数」
「各注文の顧客累計購入額」

ただし、データ量が多い場合はJOINの方が高速なこともあるので、パフォーマンスを確認しましょう。

Q6: 実務でスカラーサブクエリはよく使いますか?

はい、ダッシュボードやレポート作成で頻繁に使います。

ただし、大量データを扱う場合は、JOIN、WITH句（CTE）、ウィンドウ関数など、より効率的な方法も検討します。次のステップで学ぶウィンドウ関数も、スカラーサブクエリの代替として使えることが多いです。

🎓 次のステップでは

Step 6: ウィンドウ関数入門では、集計しながら全ての行を保持できる強力な機能を学びます。スカラーサブクエリで行っていた累積計算やランキングが、より簡潔に書けるようになります!

📝

学習メモ

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

📋 過去のメモ一覧 ▼