再帰的WITH句で階層データを読み解く！MySQLとMariaDBの実践ガイド

MySQLとMariaDBにおける再帰的WITH句の分かりやすい解説

MySQL 8.0以降とMariaDB 10.2以降では、再帰的Common Table Expression（CTE）と呼ばれる機能が導入されました。これは、階層構造データを効率的に処理するための強力なツールです。

本記事では、MySQLとMariaDBにおける再帰的WITH句について、分かりやすくかつ詳細に解説します。

階層データと再帰クエリ

階層データとは、ツリー状に構造化されたデータです。例えば、以下のようなデータが考えられます。

- カテゴリ1
    - サブカテゴリ1-1
        - アイテム1-1-1
        - アイテム1-1-2
    - サブカテゴリ1-2
        - アイテム1-2-1
- カテゴリ2
    - サブカテゴリ2-1
        - アイテム2-1-1

このような階層データを扱う場合、従来のクエリでは複数のサブクエリを繋ぎ合わせるなど、複雑な記述が必要でした。一方、再帰クエリを用いると、1つのクエリで効率的に処理することができます。

再帰的WITH句の構文

再帰的WITH句は、以下の構文で記述されます。

WITH RECURSIVE cte_name (column1, column2, ...) AS (
  -- 非再帰部分
  SELECT ...
  UNION ALL
  -- 再帰部分
  SELECT ...
)
SELECT * FROM cte_name;

構成要素

• SELECT * FROM cte_name;: CTEの結果をSELECTします。
• -- 再帰部分: 再帰的に処理するSELECTステートメントを記述します。
• UNION ALL: 非再帰部分と再帰部分を繋ぎ合わせます。
• (column1, column2, ...): CTEの列を定義します。
• cte_name: CTEに名前を付けます。
• WITH RECURSIVE: 再帰的CTEであることを示します。

動作原理

1. 非再帰部分のSELECTステートメントを実行し、最初のデータセットを取得します。
2. 再帰部分のSELECTステートメントを、取得したデータセットに対して繰り返し実行します。
4. すべての処理が終わったら、CTEの結果として取得されたすべてのデータセットをSELECTします。

実行例

以下の例では、categoriesテーブルとitemsテーブルを用いて、カテゴリとサブカテゴリのツリー構造を取得します。

WITH RECURSIVE category_tree (category_id, parent_id, category_name) AS (
  -- 非再帰部分: ルートカテゴリを取得
  SELECT category_id, parent_id, category_name
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- 再帰部分: サブカテゴリを取得
  SELECT c.category_id, c.parent_id, c.category_name
  FROM categories c
  JOIN category_tree p ON c.parent_id = p.category_id
)
SELECT * FROM category_tree;

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

category_id | parent_id | category_name
------- | -------- | --------
1          | NULL      | カテゴリ1
2          | 1        | サブカテゴリ1-1
3          | 1        | サブカテゴリ1-2
4          | 2        | アイテム1-1-1
5          | 2        | アイテム1-1-2
6          | NULL      | カテゴリ2
7          | 6        | サブカテゴリ2-1
8          | 7        | アイテム2-1-1

利点

再帰的WITH句を用いることで、以下の利点が得られます。

• 柔軟性: さまざまな階層構造データに対応できます。
• 効率的な処理: 従来のサブクエリを繋ぎ合わせる方法よりも、効率的に処理できます。
• 簡潔な記述: 複雑な階層構造データでも、1つのクエリで記述できます。

カテゴリとサブカテゴリのツリー構造を取得

WITH RECURSIVE category_tree (category_id, parent_id, category_name) AS (
  -- 非再帰部分: ルートカテゴリを取得
  SELECT category_id, parent_id, category_name
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- 再帰部分: サブカテゴリを取得
  SELECT c.category_id, c.parent_id, c.category_name
  FROM categories c
  JOIN category_tree p ON c.parent_id = p.category_id
)
SELECT * FROM category_tree;

説明:

• 最後に、SELECT * FROM category_tree; で category_tree テーブルのすべてのレコードを取得します。
• 再帰部分は、JOIN category_tree p ON c.parent_id = p.category_id で category_tree テーブルと結合し、親カテゴリIDと一致するサブカテゴリレコードを取得して category_tree に追加します。
• 非再帰部分は、WHERE parent_id IS NULL で親カテゴリIDがNULLのレコードのみを取得し、ルートカテゴリを抽出して category_tree に格納します。
• category_tree には、category_id、parent_id、category_name の3つの列があります。
• WITH RECURSIVE category_treeで、再帰的CTE category_tree を定義します。
• itemsテーブル: アイテムID、カテゴリID、アイテム名の情報を持つ
• categoriesテーブル: カテゴリID、親カテゴリID、カテゴリ名の情報を持つ

全従業員とそのマネージャーのリストを取得

この例では、employeesテーブルを用いて、全従業員とそのマネージャーのリストを取得します。

WITH RECURSIVE employee_tree (employee_id, manager_id, employee_name, manager_name) AS (
  -- 非再帰部分: 最初のレベルの従業員を取得
  SELECT e.employee_id, e.manager_id, e.employee_name, NULL AS manager_name
  FROM employees e

  UNION ALL

  -- 再帰部分: マネージャーとその部下を取得
  SELECT e.employee_id, e.manager_id, e.employee_name, m.employee_name
  FROM employees e
  JOIN employee_tree m ON e.manager_id = m.employee_id
)
SELECT * FROM employee_tree;

• 再帰部分は、SELECT e.employee_id, e.manager_id, e.employee_name, m.employee_name FROM employees e JOIN employee_tree m ON e.manager_id = m.employee_id で employee_tree テーブルと結合し、従業員のマネージャーIDと一致するマネージャーレコードを取得して employee_tree に追加します。マネージャー名の取得には m.employee_name を使用します。
• 非再帰部分は、SELECT e.employee_id, e.manager_id, e.employee_name, NULL AS manager_name FROM employees e で最初のレベルの従業員のみを取得し、employee_tree に格納します。
• employee_tree には、employee_id、manager_id、employee_name、manager_name の4つの列があります。
• employeesテーブル: 従業員ID、マネージャーID、従業員名、マネージャー名の情報を持つ

ファイルシステムのディレクトリツリーを取得

サブクエリ

サブクエリを用いる方法は、再帰的WITH句よりもシンプルですが、複雑な階層構造データの場合には、クエリが冗長になり、可読性が低下する可能性があります。

SELECT *
FROM categories c
WHERE c.parent_id IS NULL

UNION ALL

SELECT *
FROM categories c
JOIN (
  SELECT *
  FROM categories
  WHERE c.parent_id IS NULL

  UNION ALL

  SELECT *
  FROM categories c
  JOIN categories p ON c.parent_id = p.category_id
) AS parent_categories
ON c.parent_id = parent_categories.category_id;

カーソル

カーソルを用いる方法は、複雑な階層構造データでも柔軟に処理できますが、コーディングが複雑になり、パフォーマンスが低下する可能性があります。

DECLARE cursor_categories IS
  FOR SELECT category_id, parent_id, category_name
  FROM categories;
BEGIN
  OPEN cursor_categories;

  LOOP
    FETCH cursor_categories INTO @category_id, @parent_id, @category_name;

    IF @parent_id IS NULL THEN
      INSERT INTO category_tree (category_id, parent_id, category_name)
      VALUES (@category_id, NULL, @category_name);
    ELSE
      INSERT INTO category_tree (category_id, parent_id, category_name)
      VALUES (@category_id, @parent_id, @category_name);

      DECLARE cursor_subcategories IS
        FOR SELECT category_id, parent_id, category_name
        FROM categories
        WHERE parent_id = @category_id;
      BEGIN
        OPEN cursor_subcategories;

        LOOP
          FETCH cursor_subcategories INTO @sub_category_id, @sub_parent_id, @sub_category_name;

          INSERT INTO category_tree (category_id, parent_id, category_name)
          VALUES (@sub_category_id, @category_id, @sub_category_name);
        END LOOP;

        CLOSE cursor_subcategories;
      END;
    END IF;
  END LOOP;

  CLOSE cursor_categories;
END;

階層データ専用のライブラリ

階層データ専用のライブラリを使用すると、複雑な階層構造データを効率的に処理できます。ライブラリによって機能や使い勝手が異なるため、事前に調査が必要です。

ビュー

ビューを用いる方法は、複雑なクエリを抽象化し、可読性を向上させることができます。ただし、ビューの更新には注意が必要です。

マテリアライズドビュー

マテリアライズドビューを用いる方法は、クエリのパフォーマンスを向上させることができます。ただし、マテリアライズドビューの更新には時間がかかる場合があります。

最適な方法の選択

最適な方法は、処理するデータ構造やパフォーマンス要件によって異なります。

• 可読性が重要な場合: ビュー
• パフォーマンスが重要な場合: 階層データ専用のライブラリ、マテリアライズドビュー
• 複雑な階層構造データ: 再帰的WITH句、階層データ専用のライブラリ
• シンプルな階層構造データ: サブクエリ

それぞれの方法の特徴を理解し、状況に応じて適切な方法を選択することが重要です。