SQL - 共通テーブル式 (CTE)

こんにちは、将来のSQLマスターさんたち！今日は、共通テーブル式、または略してCTEの世界に足を踏み入れる興奮人心的な旅を始めます。プログラミングが初めての人も心配しないでください。私はこれまでに多くの生徒を指導してきましたように、ステップバイステップで案内します。お気に入りの飲み物を手に取り、リラックスして、一緒に潜りましょう！

SQLの共通テーブル式

大きなパーティを計画していると想像してみてください（誰もが好きな比喩ですよね？）。あなたにはタスクのリストがあり、それを小さな、管理しやすいチャンクに分けたいと思っています。これがSQLにおけるCTEの基本的な役割です。CTEは複雑なクエリをよりシンプルで読みやすい部分に分解する手助けをしてくれます。

CTEは一時的な名前付きの結果セットで、SELECT、INSERT、UPDATE、DELETE、またはMERGE文内で参照できるものです。これは、単一のステートメントの実行範囲内で定義されます。一時的なテーブルを作成し、そのクエリ専用に存在するものと考えられます。

以下に簡単な例を見てみましょう：

WITH cte_example AS (
SELECT 'Hello, CTE!' AS greeting
)
SELECT greeting FROM cte_example;

この例では：

1. WITHキーワードでCTEの開始を示します。
2. CTEに名前を付けます：cte_example。
3. CTEに含める内容を定義します：この場合、'Hello, CTE!'という値を持つ'greeting'という列を作成するシンプルなSELECT文です。
4. CTEの定義の後には、CTEを使用するメインクエリがあります。

これを実行すると以下のようになります：

| greeting    |
|-------------|
| Hello, CTE! |

素晴らしいですね！私たちは初めてのCTEを作成しました！

MySQLのWITH句

では、MySQLにおけるWITH句について話しましょう。これがCTEを実現する魔法の杖です。一般的な構文は以下の通りです：

WITH cte_name [(column_list)] AS (query)
SELECT * FROM cte_name;

より実用的な例を見てみましょう。例えば、従業員のテーブルがあるとします：

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'HR', 52000);

さて、CTEを使って部署ごとの平均給与を見つけます：

WITH dept_avg_salary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM dept_avg_salary
ORDER BY avg_salary DESC;

このクエリは以下の結果を返します：

| department | avg_salary |
|------------|------------|
| IT         | 62500.00   |
| Finance    | 55000.00   |
| HR         | 51000.00   |

ここでは、CTEを使って各部署の平均給与を計算し、その結果をメインクエリで表示しています。まるで一時的なテーブルを作成し、その平均給与を使ったメインクエリを実行しているようなものです。

複数のテーブルからのCTE

CTEは単一のテーブルに限りません。通常のクエリと同様に、複数のテーブルをCTEの定義に使用できます。例えば、部署テーブルを追加してみましょう：

CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'HR', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

さて、CTEを使って両テーブルの情報を結合してみましょう：

WITH dept_info AS (
SELECT e.department,
AVG(e.salary) AS avg_salary,
d.location
FROM employees e
JOIN departments d ON e.department = d.name
GROUP BY e.department, d.location
)
SELECT * FROM dept_info
ORDER BY avg_salary DESC;

これにより以下の結果が得られます：

| department | avg_salary | location      |
|------------|------------|---------------|
| IT         | 62500.00   | San Francisco |
| Finance    | 55000.00   | Chicago       |
| HR         | 51000.00   | New York      |

この例では、CTEが従業員テーブルと部署テーブルを結合し、平均給与を計算し、所在地情報も含めています。

再帰的なCTE

そして、ここで非常に興味深い話題に移ります。再帰的なCTEです！これらはSQL世界のロシアン Nesting dollsに相当します。再帰的なCTEは自分自身を参照し、階層的または木構造のデータを扱うことができます。

簡単な従業員階層の例を作成してみましょう：

CREATE TABLE employee_hierarchy (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employee_hierarchy VALUES
(1, 'Big Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);

さて、再帰的なCTEを使って全体の階層を表示してみましょう：

WITH RECURSIVE emp_hierarchy AS (
SELECT id, name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee_hierarchy e
JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM emp_hierarchy
ORDER BY level, id;

このクエリは以下の結果を生成します：

| id | name       | manager_id | level |
|----|------------|------------|-------|
| 1  | Big Boss   | NULL       | 0     |
| 2  | Manager A  | 1          | 1     |
| 3  | Manager B  | 1          | 1     |
| 4  | Employee 1 | 2          | 2     |
| 5  | Employee 2 | 2          | 2     |
| 6  | Employee 3 | 3          | 2     |

この再帰的なCTEは、最上位の従業員（Big Boss）から始まり、各マネージャーに報告するすべての従業員を再帰的に見つけます。

CTEの利点

CTEにはいくつかの利点があります：

利点	説明
読みやすさ	CTEは複雑なクエリを名前付きのサブクエリに分解することで、読みやすさを向上させます。
再利用性	CTEはクエリ内で複数回参照できます。
再帰	CTEは再帰的なクエリを書くことを可能にし、階層データに非常に適しています。
簡素化	CTEは複雑な結合やサブクエリを簡素化できます。
メンテナンス	CTEはクエリをより簡単にメンテナンスし、修正できるようにします。

CTEの欠点

CTEは強力ですが、いくつかの制約もあります：

欠点	説明
性能	一部のケースでは、CTEは派生テーブルやビューほど性能が良くない場合があります。
範囲	CTEは定義された単一のステートメントの範囲内でのみ有効です。
複雑さ	非常にシンプルなクエリの場合、CTEは不要な複雑さを追加する可能性があります。
データベースサポート	すべてのデータベースシステムがCTEをサポートしているわけではありませんが、大多数の現代データベースはサポートしています。

そして、皆さん！私たちは共通テーブル式の世界を旅しました。基本的な概念から再帰的なクエリまでです。CTEをマスターするには練習が必要です。ですので、自分のクエリでCTEを試してみてください。間もなく、CTEをプロのように書けるようになり、同僚を感心させ、データベースクエリをよりクリーンで効率的にすることができるでしょう。快乐なクエリライティングを！

Credits: Image by storyset