SQLで再帰CTEを使用して階層データを照会するにはどうすればよいですか？

この記事では、階層データを照会するためのSQLの再帰的共通テーブル式（CTE）について説明します。組織チャートの例を使用して構造を詳しく説明し、無限の再帰や誤った結合などの一般的な落とし穴に対処します。オプティ

階層データに再帰CTEを使用します

再帰的な一般的なテーブル式（CTE）は、組織チャート、ファイルシステム、材料紙幣などの階層データを照会するためのSQLの強力なツールです。それらは、その定義内でCTE自体を繰り返し参照することにより、あなたが木のような構造を横断することを可能にします。基本構造には、アンカーメンバー（初期クエリ）と再帰メンバー（自己参照部分）が含まれます。

employeesという名前のテーブルで表される組織チャートの簡単な例で説明しましょう。

 <code class="sql">CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255), manager_id INT ); INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1, 'CEO', NULL), (2, 'VP Sales', 1), (3, 'Sales Rep 1', 2), (4, 'Sales Rep 2', 2), (5, 'VP Marketing', 1), (6, 'Marketing Manager', 5);</code>

CEO（Employee_id 1）の下で階層全体を取得するには、再帰CTEを使用します。

 <code class="sql">WITH RECURSIVE EmployeeHierarchy AS ( -- Anchor member: Selects the CEO SELECT employee_id, employee_name, manager_id, 0 as level FROM employees WHERE employee_id = 1 UNION ALL -- Recursive member: Joins with itself to find subordinates SELECT e.employee_id, e.employee_name, e.manager_id, eh.level 1 FROM employees e INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM EmployeeHierarchy;</code>

このクエリはCEOから始まり、従業員が既に含まれている従業員に報告しなくなるまで、部下を再帰的に追加します。 level列は、階層の深さを示します。 UNION ALLアンカーと再帰メンバーの結果を組み合わせています。重要なのは、各従業員をマネージャーにリンクするemployeesとEmployeeHierarchyとの間の自己結合と再帰メンバーの自己結合です。

再帰的なCTEを使用する際に避けるべき一般的な落とし穴

再帰的なCTEを使用すると、いくつかの落とし穴が誤った結果やパフォーマンスの問題につながる可能性があります。

• 無限の再帰：最も一般的な間違いは、データにサイクルを作成するか、適切な終了条件を持たない再帰クエリを作成することです。これにより、クエリが無期限に実行されます。データが非環状であることを確認し（従業員は自分自身に直接または間接的に報告していません）、再帰メンバーが最終的に終了することを確認します（たとえば、階層の葉のノードに到達することにより）。
• 誤った結合条件：再帰メンバーの誤った結合条件を使用すると、欠落または追加のデータが発生します。結合条件を注意深く確認して、データの階層的な関係を正確に反映していることを確認してください。
• 終了条件の欠如：再帰的なCTEには、無限のループを防ぐために明確な終了条件が必要です。これは通常、特定の値（親ID列のNULLなど）をチェックするか、再帰深度を制限することによって行われます。
• データの複製を無視する： UNION ALL UNIONを使用するには、階層に存在する場合は重複する行が含まれます。重複を排除する必要がある場合は、 UNION使用してください。ただし、 UNION ALLすべて一般的に高速です。

大規模なデータセットの再帰CTEクエリの最適化

再帰的なCTEは、非常に大きな階層データセットで遅くなる可能性があります。いくつかの最適化戦略により、パフォーマンスを改善できます。

• インデックス作成：結合条件（通常は親子関係列）で使用される列に適切なインデックスが存在するようにします。インデックスは、再帰CTE内の結合を大幅に高速化します。
• フィルタリング：アンカーおよび/または再帰メンバーにWHEREを追加して、階層の不要な枝を除外することにより、再帰の範囲を制限します。これにより、処理されたデータの量が減少します。
• 具体化されたビュー：頻繁に実行される再帰クエリについては、階層データを事前にコンピューターする具体化されたビューを作成することを検討してください。これにより、ストレージスペースといくつかのデータのlust性のコストでクエリのパフォーマンスを大幅に改善できます。
• 代替アプローチ：非常に大きなデータセットの場合、特定の階層クエリのパフォーマンスを向上させることができる隣接リストやネストされたセットを使用するなどの代替アプローチを検討してください。再帰的なCTEは、常にすべてのシナリオに最適なソリューションではありません。
• バッチ処理：階層全体を1回のクエリで処理する代わりに、それを小さなバッチに分割することを検討してください。

さまざまなデータベースシステムの再帰CTE

再帰的なCTEは、ほとんどの主要なデータベースシステムでサポートされていますが、構文はわずかに異なる場合があります。

• SQL Server： WITH RECURSIVE使用します（ただし、 RECURSIVEキーワードはオプションです）。
• PostgreSQL： WITH RECURSIVE使用します。
• MySQL：バージョン8.0からの再帰CTEをサポートします。構文はPostgreSQLに似ています。
• Oracle：最初のCTEをサポートし、 START WITH CONNECT BY 。これは、わずかに異なる構文を持っていますが、同じ機能を実現します。

コア概念は異なるシステムで同じままですが、正しい構文とシステム固有の制限または最適化については、特定のデータベースシステムのドキュメントを必ず参照してください。クエリを徹底的にテストし、パフォーマンスをプロファイルしてボトルネックを識別および対処することを忘れないでください。

以上がSQLで再帰CTEを使用して階層データを照会するにはどうすればよいですか？の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。