MySQL 共通テーブル式 (CTE) | クエリを簡略化

はじめに

この実験 (Lab) では、MySQL で共通テーブル式 (CTE: Common Table Expressions) を使用する方法を学びます。CTE は、複雑なクエリを論理的で読みやすいステップに分割することで、クエリを簡略化するのに役立ちます。CTE は、単一の SQL ステートメント内で参照できる一時的な名前付き結果セットを作成します。

まず、データベースをセットアップし、従業員データをフィルタリングするための簡単な CTE を作成します。次に、組織図のような階層データを処理するための再帰 CTE を探求します。最後に、CTE をテーブルと結合して、より高度なデータ分析を実行する方法を学びます。

データベースと単純な CTE のセットアップ

この最初のステップでは、データベース環境をセットアップし、シンプルな共通テーブル式 (CTE: Common Table Expression) を定義します。CTE は WITH 句を使用して定義され、単一のクエリの実行期間のみ存在する一時的なビューとして機能します。これにより、複雑なクエリの読みやすさと管理性が向上します。

まず、デスクトップからターミナルを開きます。

root ユーザーとして MySQL サーバーに接続します。この実験 (Lab) 環境では、パスワードなしで接続するために sudo を使用できます。

sudo mysql -u root

接続すると、MySQL プロンプト (mysql>) が表示されます。次に、labex_db という名前のデータベースを作成し、それに切り替えます。

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;

次に、employees テーブルを作成し、サンプルデータを挿入します。このテーブルには、部署や給与を含む従業員の基本情報が格納されます。

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);

テーブルの準備ができたので、'Sales' 部署の従業員のみを選択するシンプルな CTE を定義しましょう。

WITH SalesEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;

このクエリの内訳を見てみましょう。

WITH SalesEmployees AS (...): これは SalesEmployees という名前の CTE を定義します。
括弧内のクエリは、department が 'Sales' である employees テーブルから従業員を選択します。
SELECT * FROM SalesEmployees;: これは、一時的な SalesEmployees 結果セットからすべてのデータを取得するメインクエリです。

Sales 部署の従業員のみが表示され、以下の出力が表示されるはずです。

+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary   |
+-------------+------------+-----------+----------+
|           1 | John       | Doe       | 60000.00 |
|           3 | Robert     | Jones     | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)

これにより、最初の CTE が正しく機能していることが確認できます。次のステップのために、MySQL シェルに留まってください。

階層データのための再帰 CTE を作成する

再帰 CTE は、組織図やネストされたカテゴリのような階層データをクエリするための強力な機能です。再帰 CTE は、ツリー構造をレベルごとに処理するために、自身を参照します。これは、「アンカーメンバー」（開始点）と「再帰メンバー」（繰り返し）で構成されます。

このステップでは、従業員の階層を表すテーブルを作成し、再帰 CTE を使用してそれを表示します。

まず、MySQL シェル内で employee_hierarchy テーブルを作成します。このテーブルには、他の従業員の employee_id を指す manager_id が含まれており、階層が作成されます。

CREATE TABLE IF NOT EXISTS employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);

この構造では、「David」は manager_id が NULL であるため、最上位のマネージャーです。

次に、この階層をたどる再帰 CTE を記述します。RECURSIVE キーワードが必要です。

WITH RECURSIVE EmployeeHierarchyCTE AS (
    -- アンカーメンバー: 最上位のマネージャーを選択
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰メンバー: 自身と結合して部下を見つける
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employee_hierarchy e
    INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;

このクエリを分析しましょう。

アンカーメンバー: 最初の SELECT ステートメントは、階層のルート（manager_id が NULL の場所）を見つけ、それに level 0 を割り当てます。
再帰メンバー: 2 番目の SELECT ステートメントは、employee_hierarchy を CTE 自体 (EmployeeHierarchyCTE) と結合します。CTE 内に既に存在する employee_id と一致する manager_id を持つすべての従業員を見つけ、level をインクリメントします。
UNION ALL: この演算子は、アンカーメンバーと再帰メンバーの結果を結合します。

クエリは、レベル付きの完全な組織図を示す以下の出力を生成します。

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | David         |       NULL |     0 |
|           2 | Emily         |          1 |     1 |
|           3 | Frank         |          1 |     1 |
|           4 | Grace         |          2 |     2 |
|           5 | Henry         |          2 |     2 |
|           6 | Ivy           |          3 |     2 |
|           7 | Jack          |          3 |     2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)

再帰 CTE を使用して階層データを正常にクエリできました。最終ステップのために、MySQL シェルに留まってください。

CTE とテーブルを結合する

CTE は、通常のテーブルと同様に他のテーブルと結合できます。これは、CTE から集計されたデータを別のテーブルの詳細データと組み合わせる場合に便利です。このステップでは、部署ごとの平均給与を計算する CTE を作成し、それを employees テーブルに結合します。

MySQL シェル内で、次のクエリを実行します。これは AvgSalaryByDepartment という名前の CTE を定義し、それを employees テーブルに結合します。

WITH AvgSalaryByDepartment AS (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.employee_id,
    e.first_name,
    e.department,
    e.salary,
    a.avg_salary
FROM
    employees e
JOIN
    AvgSalaryByDepartment a ON e.department = a.department;

以下にその内訳を示します。

AvgSalaryByDepartment CTE は、各部署の平均給与を計算します。
メインクエリは、employees テーブル（エイリアス e）をこの CTE（エイリアス a）と department 列で結合します。
これにより、各従業員の給与を部署の平均給与と並べて表示できます。

期待される出力は次のようになります。

+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary   | avg_salary   |
+-------------+------------+------------+----------+--------------+
|           1 | John       | Sales      | 60000.00 | 57500.000000 |
|           2 | Jane       | Marketing  | 75000.00 | 72500.000000 |
|           3 | Robert     | Sales      | 55000.00 | 57500.000000 |
|           4 | Emily      | IT         | 90000.00 | 90000.000000 |
|           5 | Michael    | Marketing  | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)

結果を手動で確認できます。たとえば、「Sales」部署の平均給与は (60000 + 55000) / 2 = 57500 であり、出力と一致します。これにより、クエリが正しく機能していることが確認できます。

CTE とテーブルの結合に成功しました。これで MySQL シェルを終了できます。

exit;

まとめ

この実験では、MySQL で共通テーブル式 (CTE) を効果的に使用する方法を学びました。データベースとテーブルのセットアップから始め、さまざまな種類の CTE の作成に進みました。

以下のことを学びました。

WITH 句を使用して CTE を定義し、データをフィルタリングしてクエリの可読性を向上させる方法。
再帰 CTE を作成して、テーブルの階層データをナビゲートおよび表示する方法。
CTE とテーブルを結合して、集計結果と詳細な行レベルデータを組み合わせて、より複雑な分析を行う方法。

これらのスキルは、特に複雑なロジックやデータ構造を扱う場合に、クリーンで保守可能で強力な SQL クエリを作成するための基本となります。