再帰クエリを使い、親子関係で結ばれた階層構造のデータをフラットにするSQLの例です
本ページは対象をSQLServerとして記述しています
階層化されたデータ
データを親子関係で結んだデータを階層化されたデータといいます。
例えばディレクトリ(c:\windows等)は良い例です。
「Cドライブ」の下に「Windows」フォルダや「Program Files」フォルダがあり、また、「Windows」フォルダの下には「System32」フォルダがあります。
このようなデータ構造のことを言います。
C:\
+-- Windows
+-- System32
+-- Drivers
+-- etc
+-- Microsoft.NET
+-- : : :
+-- Program Files
+-- : : :
+-- : : :
階層構造のテーブル定義
これから説明するSQLで利用するテーブルの定義です
これは、前述のディレクトリの構造を持つテーブルとなります
テーブル名：FOLDER
| 列名 | 属性 | 説明 |
|---|---|---|
| ID | BigInt | ユニークな値(プライマリキー) |
| PATH | nvarchar(MAX) | “Windows”,”Program Files”等、フォルダ名 |
| PARENT_ID | BigInt | 親フォルダのID値 |
いわゆる「隣接リストモデル」のテーブル構造です。
上記テーブルに投入したデータは以下です。
| ID | PATH | PARENT_ID |
|---|---|---|
| 1 | C: | NULL |
| 2 | Windows | 1 |
| 3 | System | 2 |
| 4 | System32 | 2 |
| 5 | drivers | 4 |
| 6 | etc | 5 |
| 7 | Microsoft.NET | 2 |
| 8 | Program Files | 1 |
| 9 | Common Files | 8 |
| 10 | Internet Explorer | 8 |
例えば、ID=4のデータは、親(PARENT_ID)が「2」です。更に、ID=2のPARNT_IDは「1」です。ID=1のPARENT_IDはNULLですので、ここが最上位となります。
結果、ID=4のPATHは「System32」ですが、フルパスだとID=4→2→1と辿って「C:\Windows\System32」となります。
SQLでフルパスを求める
データの構造が分かれば、データを辿ることによってフルパスは求められます。
これをSQL一発で取得するにはどうすればよいでしょうか。
ここで登場するのが再帰クエリです。
データを再帰的に辿ることにより、フルパスを求められます。
実際のSQLは以下となります。
WITH rec(DEPTH, ID, BASE_PATH, PATH, PARENT_ID, FULLPATH) as ( SELECT 1 AS DEPTH ,ID ,PATH AS BASE_PATH ,PATH ,PARENT_ID ,PATH AS FULLPATH FROM FOLDER WHERE PARENT_ID is NULL UNION ALL SELECT a.DEPTH + 1 ,b.ID ,a.BASE_PATH ,b.PATH ,b.PARENT_ID ,a.FULLPATH + '\' + b.PATH FROM rec a ,FOLDER b WHERE a.ID = b.PARENT_ID) SELECT * FROM rec
このSQLを実行した結果は以下となります
DEPTH ID BASE_PATH PATH PARENT_ID FULL_PATH 1 1 C: C: NULL C: 2 2 C: Windows 1 C:\Windows 2 8 C: Program Files 1 C:\Program Files 3 9 C: Common Files 8 C:\Program Files\Common Files 3 10 C: Internet Explorer 8 C:\Program Files\Internet Explorer 3 3 C: System 2 C:\Windows\System 3 4 C: System32 2 C:\Windows\System32 3 7 C: Microsoft.NET 2 C:\Windows\Microsoft.NET 4 5 C: drivers 4 C:\Windows\System32\drivers 5 6 C: etc 5 C:\Windows\System32\drivers\etc
ID=4のFULL_PATH列は頭で考えたのと同じ「C:\WIndows\System32」となっています。
ちなみに「DEPTH」列は、階層の深さを表しています
応用
これを使えば、例えば出発地点から目的地までのルート検索や最短ルートを求めたりすることができると思います
まぁ、その為にはテーブル構造がしっかりしていないとダメなんですが・・・