親子関係も複雑な階層構造もスッキリ！SQLで階層構造を処理する方法

2024-04-06

SQLで再帰クエリを作成することは可能です。

再帰クエリの仕組み

再帰クエリは、自分自身を呼び出すことで、テーブルを繰り返し処理します。これは、ループ処理に似ていますが、SQLの構文を使って記述されます。

PostgreSQLでは、WITH句を使って再帰クエリを作成できます。WITH句では、中間的な結果を保存する仮想テーブルを定義できます。

例：

WITH recursive cte AS (
    SELECT *
    FROM employees
    WHERE department_id = 1

    UNION ALL

    SELECT e.*
    FROM employees e
    INNER JOIN cte ON cte.manager_id = e.id
)

SELECT *
FROM cte;

この例では、employeesテーブルから部門IDが1の従業員を取得し、そのマネージャー情報も再帰的に取得しています。

再帰クエリの注意点

再帰クエリは、無限ループに陥る可能性があるため、注意が必要です。そのため、終了条件を明確に定義する必要があります。

また、再帰クエリは複雑になりやすいので、性能面にも注意が必要です。

上記はPostgreSQLでの例ですが、他のデータベースでも同様の機能が提供されています。
再帰クエリは複雑になりやすいので、必要に応じて他の方法を検討することも重要です。

従業員テーブル (employees)

id | name | department_id | manager_id
--- | --- | --- | ---
1 | 山田太郎 | 1 | 2
2 | 佐藤花子 | 1 | 1
3 | 田中一郎 | 2 | null

部門テーブル (departments)

id | name
--- | ---
1 | 営業部
2 | 開発部

SQLクエリ

WITH recursive cte AS (
    SELECT e.*, d.name AS department_name
    FROM employees e
    INNER JOIN departments d ON d.id = e.department_id
    WHERE e.manager_id IS NULL

    UNION ALL

    SELECT e.*, d.name AS department_name, cte.name AS manager_name
    FROM employees e
    INNER JOIN departments d ON d.id = e.department_id
    INNER JOIN cte ON cte.id = e.manager_id
)

SELECT *
FROM cte;

結果

id | name | department_id | manager_id | department_name | manager_name
--- | --- | --- | --- | --- | ---
1 | 山田太郎 | 1 | 2 | 営業部 | 佐藤花子
2 | 佐藤花子 | 1 | null | 営業部 | null
3 | 田中一郎 | 2 | null | 開発部 | null

このクエリは、WITH句を使って再帰的な中間テーブルcteを定義しています。cteテーブルには、従業員情報に加えて、部門名とマネージャー名も含まれています。

最初のSELECT文では、employeesテーブルからマネージャーがいない従業員を取得します。2番目のSELECT文では、employeesテーブルとcteテーブルを結合して、従業員とそのマネージャー情報を取得します。

このクエリを実行すると、従業員とそのマネージャー情報が階層的に表示されます。

SQLで階層構造を処理する他の方法

親子関係を列挙するテーブルを用意することで、階層構造を表現できます。例えば、以下のようなテーブルです。

親子関係テーブル (parent_child)

parent_id | child_id
--- | ---
1 | 2
2 | 3
1 | 4

このテーブルでは、parent_id列が親ノードのID、child_id列が子ノードのIDを表しています。

この方法のメリットは、シンプルな構造で理解しやすいことです。デメリットは、テーブルサイズが大きくなる可能性があることです。

Closure Tableは、親子関係だけでなく、祖先と子孫の関係も表現できるテーブル構造です。

Closure Table (closure)

ancestor_id | descendant_id
--- | ---
1 | 2
1 | 3
1 | 4
2 | 3
2 | 4

階層構造をJSON形式で表現することもできます。JSONは、データ構造を記述するのに適したフォーマットです。

{
  "id": 1,
  "name": "山田太郎",
  "children": [
    {
      "id": 2,
      "name": "佐藤花子",
      "children": []
    },
    {
      "id": 3,
      "name": "田中一郎",
      "children": []
    }
  ]
}

この例では、従業員情報と階層構造をJSON形式で表現しています。

この方法のメリットは、柔軟性が高く、様々なツールで扱えることです。デメリットは、SQLで直接処理できないことです。

<employees>
  <employee id="1">
    <name>山田太郎</name>
    <children>
      <employee id="2">
        <name>佐藤花子</name>
        <children/>
      </employee>
      <employee id="3">
        <name>田中一郎</name>
        <children/>
      </employee>
    </children>
  </employee>
</employees>