SQLite CTE クエリ

はじめに

この実験（Lab）では、SQLite における Common Table Expressions (CTE) の強力な機能を探求します。CTE を定義および使用して、クエリの可読性と保守性を向上させる方法を学びます。簡単な CTE から始めて、再帰 CTE に進みます。この実験（Lab）を終える頃には、CTE を使用して、よりクリーンで効率的で、理解しやすい SQL コードを作成できるようになります。

Skills Graph

データベースとテーブルの作成

このステップでは、SQLite データベースと employees テーブルを作成します。このテーブルには従業員情報が格納され、後のステップで CTE クエリを練習するために使用します。

まず、LabEx VM でターミナルを開きます。デフォルトのパスは /home/labex/project です。

次に、company.db という名前の SQLite データベースを作成しましょう。次のコマンドを実行して、データベースファイルを作成し、SQLite コマンドラインツールを開きます。

sqlite3 company.db

SQLite シェル内に入ったことを示すプロンプトが表示されます。

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

次に、基本的な従業員情報を格納するための employees という名前のテーブルを作成します。このテーブルには、id、name、department、および salary の 4 つの列があります。sqlite> プロンプトで次の SQL コマンドを入力し、Enter キーを押します。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

このコマンドは、employees テーブルを次のように設定します。

• id は、各従業員の主キーとして機能する整数です。
• name は、従業員の名前のテキストフィールドです。
• department は、従業員の部署のテキストフィールドです。
• salary は、従業員の給与の整数フィールドです。

コマンドが正常に実行された場合、出力は表示されません。

テーブルへのデータの挿入

employees テーブルを作成したので、いくつかのデータを追加しましょう。5 つの従業員レコードをテーブルに挿入します。

sqlite> プロンプトで次のコマンドを 1 つずつ実行して、5 つの従業員レコードを employees テーブルに挿入します。

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Sales', 50000);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 60000);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Engineering', 70000);
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000);

これらのコマンドは、employees テーブルに 5 つの行を追加します。

• INSERT INTO employees (name, department, salary) は、employees テーブルの name、department、および salary 列にデータを挿入することを指定します。
• VALUES ('Alice', 'Sales', 50000) は、各レコードに挿入される値を提供します。

データが正しく追加されたことを確認するには、次のコマンドを実行して、テーブル内のすべてのレコードを表示します。

SELECT * FROM employees;

期待される出力：

1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000

この出力は、各レコードの id、name、department、および salary を示しています。SELECT * コマンドは、指定されたテーブルからすべての列を取得します。

簡単な CTE の定義

このステップでは、SQLite で簡単な Common Table Expression (CTE) を定義して使用する方法を学びます。CTE は、単一の SQL ステートメント内で参照できる一時的な名前付きの結果セットです。これらは、複雑なクエリをより小さく、管理しやすい部分に分割し、可読性と保守性を向上させるのに役立ちます。

CTE は基本的に、単一のクエリの期間のみ存在する名前付きサブクエリです。WITH 句を使用して CTE を定義し、名前を付けて、結果セットを生成するクエリを指定します。その後、CTE の名前を通常のテーブルであるかのようにメインクエリで参照できます。

基本的な構文：

WITH
    cte_name AS (
        SELECT column1, column2
        FROM table1
        WHERE condition
    )
SELECT column1, column2
FROM cte_name
WHERE another_condition;

'Sales' 部署の従業員を選択する CTE を定義しましょう。sqlite> プロンプトで次の SQL ステートメントを実行します。

WITH
    SalesEmployees AS (
        SELECT id, name, salary
        FROM employees
        WHERE department = 'Sales'
    )
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;

このクエリは、まず、'Sales' 部署のすべての従業員の id、name、および salary を選択する SalesEmployees という名前の CTE を定義します。次に、メインクエリは、給与が 52000 より大きい SalesEmployees CTE から id、name、および salary を選択します。

期待される出力：

3|Charlie|55000

これは、Charlie が給与が 52000 より大きい Sales 部署の唯一の従業員であることを示しています。

再帰 CTE の構築

このステップでは、SQLite で再帰 Common Table Expression (CTE) を構築して使用する方法を学びます。再帰 CTE は、階層構造またはツリー構造のデータをクエリするために使用されます。これらを使用すると、テーブル内のリレーションシップをトラバースし、階層のさまざまなレベルでデータを取得できます。

再帰 CTE は、それ自体を参照する CTE です。これは、次の 2 つの部分で構成されています。

• アンカーメンバー (Anchor Member): 再帰のベースケースまたは開始点を定義する最初の SELECT ステートメント。
• 再帰メンバー (Recursive Member): CTE 自体を参照する SELECT ステートメント。この部分は再帰ステップを実行し、前のイテレーションの結果に基づいて構築します。

アンカーメンバーと再帰メンバーは、UNION ALL 演算子を使用して結合されます。再帰は、再帰メンバーが空の結果セットを返すまで続行されます。

まず、次の構造を持つ employees_hierarchy という名前のテーブルを作成しましょう。

CREATE TABLE employees_hierarchy (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER,
    title TEXT
);

このテーブルは従業員階層を表しており、manager_id は従業員の上司の id を参照します。上記のコマンドを sqlite> プロンプトで実行します。

次に、いくつかのサンプルデータを employees_hierarchy テーブルに挿入します。

INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Alice', NULL, 'CEO');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Bob', 1, 'VP of Marketing');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Charlie', 1, 'VP of Sales');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('David', 2, 'Marketing Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Eve', 3, 'Sales Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Frank', 4, 'Marketing Specialist');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Grace', 5, 'Sales Representative');

ここでは、Alice が CEO (上司なし)、Bob と Charlie が Alice に報告し、David が Bob に報告し、Eve が Charlie に報告し、Frank が David に報告し、Grace が Eve に報告します。上記のコマンドを sqlite> プロンプトで実行します。

次に、再帰 CTE を構築して、Alice (CEO) の下の階層全体を取得しましょう。次の SQL ステートメントを実行します。

WITH RECURSIVE
    EmployeeHierarchy(id, name, manager_id, title, level) AS (
        -- Anchor member: Select the CEO
        SELECT id, name, manager_id, title, 0 AS level
        FROM employees_hierarchy
        WHERE manager_id IS NULL

        UNION ALL

        -- Recursive member: Select employees reporting to the current level
        SELECT e.id, e.name, e.manager_id, e.title, eh.level + 1
        FROM employees_hierarchy e
        JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    )
SELECT id, name, title, level
FROM EmployeeHierarchy;

このクエリは、EmployeeHierarchy という名前の再帰 CTE を定義します。アンカーメンバーは CEO を選択します (manager_id が NULL の場合)。再帰メンバーは、employees_hierarchy テーブルを EmployeeHierarchy CTE と結合して、前のレベルで選択された従業員に報告する従業員を見つけます。level 列は、階層内の深さを追跡します。

期待される出力：

1|Alice|CEO|0
2|Bob|VP of Marketing|1
3|Charlie|VP of Sales|1
4|David|Marketing Manager|2
5|Eve|Sales Manager|2
6|Frank|Marketing Specialist|3
7|Grace|Sales Representative|3

これは、従業員階層全体を示しており、level は報告レベルを示しています。

複雑なクエリへの CTE の統合

このステップでは、SQLite で CTE をより複雑なクエリに統合する方法を学びます。単一のクエリ内で複数の CTE を使用する方法を見ていきます。

コンマで区切ることで、単一のクエリで複数の CTE を定義できます。これにより、複雑なクエリをいくつかの論理的なステップに分割でき、それぞれが CTE で表されます。

まず、次の構造を持つ department_salaries という名前の新しいテーブルを作成しましょう。

CREATE TABLE department_salaries (
    department TEXT,
    total_salary INTEGER
);

このテーブルには、各部署の給与の合計が格納されます。上記のコマンドを sqlite> プロンプトで実行します。

次に、CTE を使用して各部署の給与の合計を計算し、その結果を department_salaries テーブルに挿入しましょう。次の SQL ステートメントを実行します。

WITH
    DepartmentTotalSalaries AS (
        SELECT department, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department
    )
INSERT INTO department_salaries (department, total_salary)
SELECT department, total_salary
FROM DepartmentTotalSalaries;

SELECT * FROM department_salaries;

このクエリは、まず、employees テーブルを使用して各部署の給与の合計を計算する DepartmentTotalSalaries という名前の CTE を定義します。次に、DepartmentTotalSalaries CTE からの結果を department_salaries テーブルに挿入します。最後に、結果を表示するために department_salaries テーブルからすべてのデータを選択します。

期待される出力：

Sales|105000
Marketing|122000
Engineering|70000

これは、各部署の給与の合計を示しています。

まとめ

この実験（Lab）では、SQLite で Common Table Expression (CTE) を定義して使用する方法を学びました。テーブルからデータを選択する単純な CTE から始めて、階層データをクエリする再帰 CTE に進みました。最後に、CTE をより複雑なクエリに統合する方法を学びました。CTE は、よりクリーンで効率的で、理解しやすい SQL コードを作成するための強力なツールです。