組織のデータを扱う際、階層構造を理解し、それをデータベースで表現することがよくあります。
しかし、階層的なデータの探索は一見複雑に見えることがあります。今日は、その一つの解決策として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クエリがどのように動作するかの詳細な説明です。各ステップで何が起こるかを理解することで、このクエリがどのように組織の階層を探索し、パスを生成するかを理解することができるはずです。
このテクニックは、階層的なデータを持つ任意のシナリオに適用でき、特に組織構造やファイルシステム、ソーシャルネットワークの関係などで有用です。