練習：データ分析

Q: 質問：CTE（WITH文）とサブクエリの違いは何ですか？

回答： CTEは可読性が高く、同じクエリ内で複数回参照できます；サブクエリは参照されるたびに再実行されます。CTEは再帰クエリもサポートしています。複雑なクエリにはCTEを推奨します。

Q: 質問：ウィンドウ関数とGROUP BYの違いは何ですか？

回答： GROUP BYは複数の行を1行にマージします（集約後に減少）；ウィンドウ関数はすべての元の行を保持し、各行に集約結果を追加します。集約と詳細の両方が必要な場合は、ウィンドウ関数を使用してください。

Q: 質問：CASE WHENのネスト深度に制限はありますか？

回答： 理論的にはありませんが、過度なネストはロジックが複雑すぎることを示しています。CTEを使用してステップを分解するか、アプリケーション層で複雑なロジックを処理することを検討してください。

Q: 質問：大量データの集約クエリを最適化するにはどうすればいいですか？

回答： GROUP BYやWHEREで使用するカラムにインデックスを作成し、CTEを使用して複雑なクエリを分解し、集約カラムに関数を使用することを避け（例：WHERE YEAR(date) = 2026をWHERE date >= '2026-01-01' AND date < '2027-01-01'に変更）、EXPLAINを使用して実行計画を分析してください。

📋 プロジェクト要件

このレッスンでは2つの実践シナリオ — 売上レポート分析と従業員パフォーマンス統計 — を扱い、これまでのレッスンで学んだ集約関数、グループ化クエリ、条件式、JOIN、サブクエリを応用します。

データベーステーブル構造

統一された4テーブルのデータベースを使用します：

SQL

-- 部門テーブル
CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    city            VARCHAR(50),
    budget          DECIMAL(12,2)
);

-- 従業員テーブル
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100),
    salary        DECIMAL(10,2),
    department_id INT,
    hire_date     DATE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 注文テーブル
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    employee_id  INT,
    order_date   DATE,
    total_amount DECIMAL(12,2),
    status       VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- 製品テーブル
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price        DECIMAL(10,2),
    stock        INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

テストデータ

SQL

-- 部門データ
INSERT INTO departments VALUES (1, 'Technology', 'Beijing', 500000);
INSERT INTO departments VALUES (2, 'Sales', 'Shanghai', 300000);
INSERT INTO departments VALUES (3, 'Marketing', 'Guangzhou', 200000);
INSERT INTO departments VALUES (4, 'Finance', 'Shenzhen', 150000);

-- 従業員データ
INSERT INTO employees VALUES (1, 'Zhang', 'Wei', 'zhangwei@co.com', 12000, 1, '2023-03-15');
INSERT INTO employees VALUES (2, 'Li', 'Na', 'lina@co.com', 9500, 1, '2023-06-20');
INSERT INTO employees VALUES (3, 'Wang', 'Qiang', 'wangqiang@co.com', 8000, 2, '2022-11-10');
INSERT INTO employees VALUES (4, 'Zhao', 'Min', 'zhaomin@co.com', 11000, 2, '2024-01-05');
INSERT INTO employees VALUES (5, 'Liu', 'Yang', 'liuyang@co.com', 7500, 3, '2024-04-18');
INSERT INTO employees VALUES (6, 'Chen', 'Jing', 'chenjing@co.com', 9000, NULL, '2025-02-28');

-- 注文データ
INSERT INTO orders VALUES (1001, 101, 3, '2026-01-10', 2500.00, 'completed');
INSERT INTO orders VALUES (1002, 102, 3, '2026-02-15', 1800.00, 'completed');
INSERT INTO orders VALUES (1003, 103, 4, '2026-02-20', 3200.00, 'shipped');
INSERT INTO orders VALUES (1004, 101, 1, '2026-03-05', 950.00, 'pending');
INSERT INTO orders VALUES (1005, 104, 4, '2026-03-10', 4100.00, 'completed');
INSERT INTO orders VALUES (1006, 105, 3, '2026-04-01', 2800.00, 'cancelled');
INSERT INTO orders VALUES (1007, 101, 1, '2026-04-15', 5600.00, 'completed');
INSERT INTO orders VALUES (1008, 106, 4, '2026-05-02', 1500.00, 'completed');
INSERT INTO orders VALUES (1009, 102, 3, '2026-05-20', 3800.00, 'shipped');
INSERT INTO orders VALUES (1010, 107, 1, '2026-06-01', 2200.00, 'pending');
INSERT INTO orders VALUES (1011, 103, 4, '2026-06-10', 6300.00, 'completed');
INSERT INTO orders VALUES (1012, 108, 3, '2026-06-15', 1900.00, 'completed');

-- 製品データ
INSERT INTO products VALUES (1, 'Laptop', 6999.00, 50, 1);
INSERT INTO products VALUES (2, 'Wireless Mouse', 129.00, 200, 1);
INSERT INTO products VALUES (3, 'Office Desk', 899.00, 30, 2);
INSERT INTO products VALUES (4, 'Projector', 3500.00, 15, 3);
INSERT INTO products VALUES (5, 'Printer', 2200.00, 25, 1);
INSERT INTO products VALUES (6, 'Meeting Chair', 599.00, 40, 2);
INSERT INTO products VALUES (7, 'Whiteboard', 450.00, 60, 3);

🏗️ クエリ練習

クエリ1：従業員売上パフォーマンスレポート

要件：各従業員の売上パフォーマンスレポートを生成し、注文数、総売上、平均注文金額、ステータス別の注文数、パフォーマンス評価を含めます。

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS employee_name,
    d.department_name AS department,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_sales,
    COALESCE(ROUND(AVG(o.total_amount), 2), 0) AS avg_order_amount,
    SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN o.status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN o.status = 'pending'   THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    CASE
        WHEN COALESCE(SUM(o.total_amount), 0) >= 10000 THEN 'S - 優秀'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 5000  THEN 'A - 素晴らしい'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 2000  THEN 'B - 良好'
        WHEN COUNT(o.order_id) > 0                      THEN 'C - 改善が必要'
        ELSE '実績なし'
    END AS performance_rating
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY total_sales DESC;

結果：

TEXT

employee_name | department | total_orders | total_sales | avg_order_amount | completed | shipped | pending | cancelled | performance_rating
--------------+------------+--------------+-------------+------------------+-----------+---------+---------+-----------+-------------------
ZhaoMin       | Sales      |            4 |    15100.00 |          3775.00 |         3 |       1 |       0 |         0 | S - 優秀
WangQiang     | Sales      |            4 |    10900.00 |          2725.00 |         2 |       1 |       0 |         1 | S - 優秀
ZhangWei      | Technology |            3 |     8750.00 |          2916.67 |         1 |       0 |       2 |         0 | A - 素晴らしい
LiNa          | Technology |            0 |        0.00 |             0.00 |         0 |       0 |       0 |         0 | 実績なし
LiuYang       | Marketing  |            0 |        0.00 |             0.00 |         0 |       0 |       0 |         0 | 実績なし
ChenJing      | NULL       |            0 |        0.00 |             0.00 |         0 |       0 |       0 |         0 | 実績なし

コードの説明：

LEFT JOIN — 注文がない従業員も含め、すべての従業員を保持
COALESCE(SUM(...), 0) — 注文がない従業員に0を表示
SUM(CASE WHEN ... THEN 1 ELSE 0 END) — ステータス別の条件付きカウント（行から列へのピボット）
CASE — 総売上に基づいてパフォーマンス評価を付与
ROUND(AVG(...), 2) — 平均を小数点以下2桁に四捨五入

クエリ2：部門四半期売上比較

要件：部門と四半期ごとに売上を集約し、各部門の売上パーセンテージを計算し、各四半期のトップ販売部門を検索します。

SQL

SELECT 
    d.department_name AS department,
    CONCAT('Q', QUARTER(o.order_date)) AS quarter,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS quarterly_sales,
    ROUND(
        SUM(o.total_amount) / 
        SUM(SUM(o.total_amount)) OVER (PARTITION BY QUARTER(o.order_date)) * 100, 
    2) AS percentage,
    RANK() OVER (
        PARTITION BY QUARTER(o.order_date) 
        ORDER BY SUM(o.total_amount) DESC
    ) AS quarter_rank
FROM orders o
INNER JOIN employees e ON o.employee_id = e.employee_id
INNER JOIN departments d ON e.department_id = d.department_id
WHERE o.status != 'cancelled'
GROUP BY d.department_name, QUARTER(o.order_date)
ORDER BY quarter, quarterly_sales DESC;

結果：

TEXT

department | quarter | order_count | quarterly_sales | percentage | quarter_rank
-----------+---------+-------------+-----------------+------------+-------------
Sales      | Q1      |           2 |         4300.00 |      69.35 |            1
Technology | Q1      |           1 |          950.00 |      15.32 |            2
Sales      | Q2      |           2 |         5600.00 |      26.54 |            1
Technology | Q2      |           1 |         5600.00 |      26.54 |            1
Marketing  | Q2      |           1 |         3800.00 |      18.01 |            3

コードの説明：

QUARTER(o.order_date) — 注文日から四半期を抽出（Q1-Q4）
SUM(o.total_amount) OVER (PARTITION BY ...) — 四半期ごとの総売上を計算するウィンドウ関数
percentage — 各部門の四半期売上 ÷ 四半期総売上 × 100
RANK() OVER (PARTITION BY ... ORDER BY ...) — 各四半期内でランク付け

クエリ3：顧客価値セグメンテーション（簡易RFMモデル）

要件：Recency（最終購入日）、Frequency（購入回数）、Monetary（総支出額）に基づいて顧客をセグメント化します。

SQL

WITH customer_stats AS (
    SELECT 
        customer_id,
        MAX(order_date) AS last_purchase_date,
        COUNT(order_id) AS purchase_count,
        SUM(total_amount) AS total_spend
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
)
SELECT 
    customer_id,
    last_purchase_date,
    purchase_count,
    total_spend,
    CASE
        WHEN purchase_count >= 3 AND total_spend >= 5000 THEN 'VIP顧客'
        WHEN purchase_count >= 2 AND total_spend >= 3000 THEN '重要顧客'
        WHEN purchase_count >= 2                       THEN 'アクティブ顧客'
        WHEN total_spend >= 2000                       THEN '潜在顧客'
        ELSE '一般顧客'
    END AS customer_tier
FROM customer_stats
ORDER BY total_spend DESC;

結果：

TEXT

customer_id | last_purchase_date | purchase_count | total_spend | customer_tier
------------+-------------------+----------------+-------------+---------------
        103 | 2026-06-10        |              2 |     9500.00 | VIP顧客
        101 | 2026-04-15        |              2 |     3450.00 | 重要顧客
        104 | 2026-03-10        |              1 |     4100.00 | 潜在顧客
        102 | 2026-05-20        |              2 |     5600.00 | VIP顧客
        106 | 2026-05-02        |              1 |     1500.00 | 一般顧客
        105 | 2026-04-01        |              0 |        0.00 | 一般顧客
        107 | 2026-06-01        |              1 |     2200.00 | 潜在顧客
        108 | 2026-06-15        |              1 |     1900.00 | 一般顧客

コードの説明：

WITH customer_stats AS (...) — CTE（共通テーブル式）がまず各顧客の統計を計算
CTE内：MAX(order_date)で最終購入、COUNTで購入回数、SUMで総支出額
外部のCASEで多次元条件に基づいて顧客をセグメント化
キャンセル済み注文を除外（status != 'cancelled'）

クエリ4：製品在庫と売上の相関分析

要件：部門別の製品在庫を分析し、売上データと組み合わせて在庫回転率を評価します。

SQL

SELECT 
    d.department_name AS department,
    COUNT(DISTINCT p.product_id) AS product_count,
    SUM(p.stock) AS total_stock,
    COALESCE(SUM(p.price * p.stock), 0) AS inventory_value,
    COUNT(DISTINCT o.order_id) AS related_orders,
    COALESCE(SUM(o.total_amount), 0) AS order_total,
    CASE 
        WHEN SUM(p.price * p.stock) > 0 
        THEN ROUND(COALESCE(SUM(o.total_amount), 0) / SUM(p.price * p.stock) * 100, 2)
        ELSE 0
    END AS turnover_rate_pct
FROM departments d
LEFT JOIN products p ON d.department_id = p.department_id
LEFT JOIN employees e ON d.department_id = e.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
GROUP BY d.department_id, d.department_name
ORDER BY inventory_value DESC;

結果：

TEXT

department | product_count | total_stock | inventory_value | related_orders | order_total | turnover_rate_pct
-----------+---------------+-------------+-----------------+----------------+-------------+------------------
Technology |             3 |         275 |       369945.00 |              4 |      8750.00 |             2.36
Sales      |             2 |          70 |        68930.00 |              8 |     26000.00 |            37.71
Marketing  |             2 |          75 |        86250.00 |              0 |         0.00 |             0.00

コードの説明：

複数テーブルのLEFT JOIN：部門 → 製品 + 従業員 → 注文
COUNT(DISTINCT ...) — 重複を排除したカウント
p.price * p.stock — 製品の在庫価値を計算
回転率 = 注文合計 ÷ 在庫価値 × 100%

クエリ5：従業員パフォーマンスランキングと部門比較

要件：総合パフォーマンスで従業員をランク付けし、部門平均と比較します。

SQL

WITH emp_performance AS (
    SELECT 
        e.employee_id,
        CONCAT(e.first_name, e.last_name) AS name,
        d.department_name AS department,
        e.salary AS base_salary,
        COUNT(o.order_id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS sales_amount,
        COALESCE(AVG(o.total_amount), 0) AS avg_order_amount
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name, e.salary
)
SELECT 
    name,
    department,
    base_salary,
    order_count,
    sales_amount,
    ROUND(avg_order_amount, 2) AS avg_order_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS company_rank,
    RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS dept_rank,
    ROUND(sales_amount - AVG(sales_amount) OVER (PARTITION BY department), 2) AS vs_dept_avg
FROM emp_performance
ORDER BY sales_amount DESC;

結果：

TEXT

name      | department | base_salary | order_count | sales_amount | avg_order_amount | company_rank | dept_rank | vs_dept_avg
----------+------------+-------------+-------------+--------------+------------------+--------------+-----------+------------
ZhaoMin   | Sales      |    11000.00 |           4 |     15100.00 |          3775.00 |            1 |         1 |     3550.00
WangQiang | Sales      |     8000.00 |           3 |     10900.00 |          3633.33 |            2 |         2 |     -650.00
ZhangWei  | Technology |    12000.00 |           2 |      8750.00 |          4375.00 |            3 |         1 |     8750.00
LiNa      | Technology |     9500.00 |           0 |         0.00 |             0.00 |            4 |         2 |    -8750.00
LiuYang   | Marketing  |     7500.00 |           0 |         0.00 |             0.00 |            4 |         1 |        0.00
ChenJing  | NULL       |     9000.00 |           0 |         0.00 |             0.00 |            4 |         1 |        0.00

コードの説明：

CTE emp_performance — まず各従業員の基本パフォーマンスデータを計算
RANK() OVER (ORDER BY ...) — 会社全体のランキング
RANK() OVER (PARTITION BY ... ORDER BY ...) — 部門内ランキング
AVG(sales_amount) OVER (PARTITION BY department) — 部門平均売上を計算
vs_dept_avg — 個人の売上と部門平均の差

❓ よくある質問

質問：CTE（WITH文）とサブクエリの違いは何ですか？ 回答： CTEは可読性が高く、同じクエリ内で複数回参照できます；サブクエリは参照されるたびに再実行されます。CTEは再帰クエリもサポートしています。複雑なクエリにはCTEを推奨します。

質問：ウィンドウ関数とGROUP BYの違いは何ですか？ 回答： GROUP BYは複数の行を1行にマージします（集約後に減少）；ウィンドウ関数はすべての元の行を保持し、各行に集約結果を追加します。集約と詳細の両方が必要な場合は、ウィンドウ関数を使用してください。

質問：CASE WHENのネスト深度に制限はありますか？ 回答： 理論的にはありませんが、過度なネストはロジックが複雑すぎることを示しています。CTEを使用してステップを分解するか、アプリケーション層で複雑なロジックを処理することを検討してください。

質問：大量データの集約クエリを最適化するにはどうすればいいですか？ 回答： GROUP BYやWHEREで使用するカラムにインデックスを作成し、CTEを使用して複雑なクエリを分解し、集約カラムに関数を使用することを避け（例：WHERE YEAR(date) = 2026をWHERE date >= '2026-01-01' AND date < '2027-01-01'に変更）、EXPLAINを使用して実行計画を分析してください。

📖 まとめ

スキル	このレッスンでの応用
集約関数	COUNT, SUM, AVG, MAX, MIN
GROUP BY	次元別のグループ統計
HAVING	集約結果のフィルタリング
CASE WHEN	条件付き統計（行から列へのピボット）、評価の付与
JOIN	複数テーブルのクエリ
サブクエリ / CTE	複雑なクエリをステップごとに構築
ウィンドウ関数	RANK()ランキング、AVG() OVER()比較分析
COALESCE	NULL値の処理

重要なポイント：

CTEを使用して複雑なクエリをステップごとに構築し、可読性を向上させる
CASE WHEN + 集約関数 = 条件付き統計（行から列へのピボット）
ウィンドウ関数は詳細を保持しながら集約情報を追加する
常にCOALESCEを使用してNULLを処理し、レポートの空の値を回避する
EXPLAINを使用してクエリパフォーマンスを分析し、重要なカラムにインデックスを作成する

📝 演習

演習1：「月次売上トレンドレポート」を生成するクエリを記述してください — 月ごとの注文数、総売上、平均注文金額を集約し、前月比成長率を計算してください：（今月 - 先月）/ 先月 × 100%。

演習2：「各部門の最も給与の高い従業員」とその「処理済み注文数」を検索するクエリを記述してください。CTE + ウィンドウ関数を使用してください。

演習3：支出額で顧客をセグメント化するクエリを記述してください（≥5000はVIP、≥3000は重要、≥1000は一般、<1000は新規顧客）、各帯の顧客数と総支出額を集計してください。

演習4：従業員名、部門、給与、注文数、総売上、パフォーマンス評価を含むビューv_employee_dashboardを作成してください。そのビューを使用して「営業」部門の従業員のパフォーマンスをクエリしてください。

次のレッスン

👉 19-window-functions - ウィンドウ関数：ROW_NUMBER、RANK、DENSE_RANK、LAG、LEADなどのウィンドウ関数を深く掘り下げ、高度なデータ分析テクニックをマスターしましょう！

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて