組織のデータを扱う際、階層構造を理解し、それをデータベースで表現することがよくあります。
しかし、階層的なデータの探索は一見複雑に見えることがあります。今日は、その一つの解決策としてSQLの再帰クエリを使った方法を解説します。
create or replace TABLE Organization (
Org_Code VARCHAR(11) NOT NULL,
Org_Name VARCHAR(400),
Parent_Org_Code VARCHAR(11),
Layer_Number NUMBER(2),
constraint Org_PK primary key (Org_Code)
);
データは以下の通り入っているものとします。
| Org_Code | Org_Name | Parent_Org_Code | Layer_Number |
|---|---|---|---|
| ORG-001 | Org A | ORG-001 | 1 |
| ORG-002 | Org B | ORG-001 | 2 |
| ORG-003 | Org C | ORG-001 | 2 |
| ORG-004 | Org D | ORG-002 | 3 |
| ORG-005 | Org E | ORG-002 | 3 |
| ORG-006 | Org F | ORG-003 | 3 |
| ORG-007 | Org G | ORG-004 | 4 |
| ORG-008 | Org H | ORG-004 | 4 |
| ORG-009 | Org I | ORG-005 | 4 |
| ORG-010 | Org J | ORG-006 | 4 |
このテーブルは、組織コード Org_Code、組織の名称 Org_Name、親組織のコード Parent_Org_Code、およびその組織が階層の何層目に位置するか Layer_Number を格納しています。
組織の階層を表示したい場合、親組織から子組織までのパスを表現するために、SQLの再帰クエリを使用します。
再帰クエリは、自身を呼び出すクエリであり、これにより階層データのような複雑な構造を効果的に解析できます。
以下のクエリでは、WITH RECURSIVE 句を使用して、階層構造を再帰的に探索します：
WITH RECURSIVE org_path AS (
SELECT
Org_Code,
Org_Name,
Parent_Org_Code,
CAST(Org_Code AS VARCHAR(500)) AS Org_Path,
Layer_Number,
Org_Name AS Layer_Name,
Layer_Number AS Layer_Num
FROM
Organization
WHERE
Org_Code = Parent_Org_Code
UNION ALL
SELECT
o.Org_Code,
o.Org_Name,
o.Parent_Org_Code,
CONCAT(r.Org_Path, ' -> ', o.Org_Code),
o.Layer_Number,
CONCAT(r.Layer_Name, ' -> ', o.Org_Name),
o.Layer_Number
FROM
Organization o
JOIN
org_path r
ON
o.Parent_Org_Code = r.Org_Code
WHERE
o.Org_Code <> o.Parent_Org_Code
)
SELECT * FROM org_path;
この再帰クエリは2部構成で、最初の部分（ベースケース）では自身が親組織である組織（つまり最上位の組織）を選択しています。ここでは、Org_CodeとParent_Org_Codeが同じ行を選択します。これが再帰の開始点となります。
次にUNION ALLでベースケースと再帰ケースを結合します。再帰ケースでは、再帰CTE org_path を再帰的に結合して、親組織から次の子組織へと移動します。この部分ではParent_Org_Code が先ほど選択した行のOrg_Code と一致する行を選択します。
この操作を行うことで、親組織から子組織、孫組織、さらにその下の組織へとパスをたどることができます。
そして最終的にSELECT * FROM org_pathで再帰CTEからすべての行を選択します。
これにより、親組織から各組織へのパスを含む結果セットが得られます。
| Org_Code | Org_Name | Parent_Org_Code | Org_Path | Layer_Number | Layer_Name | Layer_Num |
|---|---|---|---|---|---|---|
| ORG-001 | Org A | ORG-001 | ORG-001 | 1 | (1) Org A | 1 |
| ORG-002 | Org B | ORG-001 | ORG-001 -> ORG-002 | 2 | (1) Org A -> (2) Org B | 2 |
| ORG-003 | Org C | ORG-001 | ORG-001 -> ORG-003 | 2 | (1) Org A -> (2) Org C | 2 |
| ORG-004 | Org D | ORG-002 | ORG-001 -> ORG-002 -> ORG-004 | 3 | (1) Org A -> (2) Org B -> (3) Org D | 3 |
| ORG-005 | Org E | ORG-002 | ORG-001 -> ORG-002 -> ORG-005 | 3 | (1) Org A -> (2) Org B -> (3) Org E | 3 |
| ORG-006 | Org F | ORG-003 | ORG-001 -> ORG-003 -> ORG-006 | 3 | (1) Org A -> (2) Org C -> (3) Org F | 3 |
| ORG-007 | Org G | ORG-004 | ORG-001 -> ORG-002 -> ORG-004 -> ORG-007 | 4 | (1) Org A -> (2) Org B -> (3) Org D -> (4) Org G | 4 |
| ORG-008 | Org H | ORG-004 | ORG-001 -> ORG-002 -> ORG-004 -> ORG-008 | 4 | (1) Org A -> (2) Org B -> (3) Org D -> (4) Org H | 4 |
| ORG-009 | Org I | ORG-005 | ORG-001 -> ORG-002 -> ORG-005 -> ORG-009 | 4 | (1) Org A -> (2) Org B -> (3) Org E -> (4) Org I | 4 |
| ORG-010 | Org J | ORG-006 | ORG-001 -> ORG-003 -> ORG-006 -> ORG-010 | 4 | (1) Org A -> (2) Org C -> (3) Org F -> (4) Org J | 4 |
以上がSQLの再帰クエリを使った組織の階層構造探索の基本的な考え方です。
振る舞いについてもう少し踏み込む
基本ケース
基本ケースは、再帰が始まる地点を定義します。このクエリでは、基本ケースは自己参照（Org_Code = Parent_Org_Code）の組織を見つけます。これは通常、組織階層のルートを示します。
SELECT
Org_Code,
Org_Name,
Parent_Org_Code,
CAST(Org_Code AS VARCHAR(500)) AS Org_Path,
Layer_Number,
Org_Name AS Layer_Name,
Layer_Number AS Layer_Num
FROM
Organization
WHERE
Org_Code = Parent_Org_Code
この部分のクエリを実行すると、以下の行が得られます。
Org_Code Org_Name Parent_Org_Code Org_Path Layer_Number Layer_Name Layer_Num
ORG-001 Org A ORG-001 ORG-001 1 Org A 1
再帰ステップ
再帰ステップは、基本ケースから始まり、その結果を用いて組織の子ノードを見つけます。このステップは、自己参照でないすべての組織（つまり、Org_Code <> Parent_Org_Code）を見つけます。
SELECT
o.Org_Code,
o.Org_Name,
o.Parent_Org_Code,
CONCAT(r.Org_Path, ' -> ', o.Org_Code),
o.Layer_Number,
CONCAT(r.Layer_Name, ' -> ', o.Org_Name),
o.Layer_Number
FROM
Organization o
JOIN
org_path r
ON
o.Parent_Org_Code = r.Org_Code
WHERE
o.Org_Code <> o.Parent_Org_Code
この部分のクエリは、基本ケースから得られた結果（つまり、ORG-001）を用いて、その子ノード（ORG-002とORG-003）を見つけます。この結果は以下のようになります。
Org_Code Org_Name Parent_Org_Code Org_Path Layer_Number Layer_Name Layer_Num
ORG-002 Org B ORG-001 ORG-001 -> ORG-002 2 Org A -> Org B 2
ORG-003 Org C ORG-001 ORG-001 -> ORG-003 2 Org A -> Org C 2
このステップは、すべての組織が見つかるまで再帰的に繰り返されます。つまり、ORG-002とORG-003の子ノード（ORG-004, ORG-005, ORG-006）、その子ノード（ORG-007, ORG-008, ORG-009, ORG-010）を見つけます。
最終的な結果は、基本ケースとすべての再帰ステップの結果を連結したものになります。これは、UNION ALLによって行われます。UNION ALLは、2つのSELECT文の結果を一つのテーブルに結合します。
このクエリの結果は、組織の階層を表現するパスを生成します。各行は、特定の組織へのパス（Org_Path）、その組織の名前（Layer_Name）、およびその組織が階層の何層目に位置しているか（Layer_Num）を示します。
以上が、このSQLクエリがどのように動作するかの詳細な説明です。各ステップで何が起こるかを理解することで、このクエリがどのように組織の階層を探索し、パスを生成するかを理解することができるはずです。
このテクニックは、階層的なデータを持つ任意のシナリオに適用でき、特に組織構造やファイルシステム、ソーシャルネットワークの関係などで有用です。