man standing in front of people sitting beside table with laptop computers

SQL

SQLの再帰と組織階層の探索：WITH RECURSIVEとUNION ALLを理解する

組織のデータを扱う際、階層構造を理解し、それをデータベースで表現することがよくあります。

しかし、階層的なデータの探索は一見複雑に見えることがあります。今日は、その一つの解決策として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クエリがどのように動作するかの詳細な説明です。各ステップで何が起こるかを理解することで、このクエリがどのように組織の階層を探索し、パスを生成するかを理解することができるはずです。

このテクニックは、階層的なデータを持つ任意のシナリオに適用でき、特に組織構造やファイルシステム、ソーシャルネットワークの関係などで有用です。