[SQLServer]階層データを再帰的CTEを使って簡単にするテクニック方法カテゴリーやフォルダ

SQL Serverを使ってて、組織図、カテゴリー構造、タスク管理などの階層データ・親子関係データを扱う際には、一般的なSQLクエリでは複雑さと冗長性が問題となりがちです。しかし、Common Table Expressions（CTE）の導入により、これらの課題を解決し、階層データの取得を効率化する方法が提供されています。この記事では、CTEの基礎から応用までを解説し、階層データを簡潔かつ効果的に管理するテクニックを紹介します。

再帰的SQLを使うと、どんなケースが解決出来るの？
実例：カテゴリーとサブカテゴリー
まとめ
1. おすすめ情報

再帰的SQLを使うと、どんなケースが解決出来るの？

組織図の表示
- 組織内の従業員とその上司（マネージャー）の関係を表す階層的なデータ構造が必要な場合、CTEを使用してこれらの関係を簡単にクエリできます。例えば、企業の全従業員の組織図を生成する際に役立ちます。
カテゴリーとサブカテゴリーのリストアップ
- 製品や記事が複数レベルのカテゴリーに分類されている場合（例えば、大カテゴリー、中カテゴリー、小カテゴリー）、CTEを使って各カテゴリーとサブカテゴリーの関係を展開し、一覧表示することができます。
プロジェクトのタスク管理
- プロジェクトのタスクやサブタスクが階層的に管理されている場合、CTEを使用して、タスクの依存関係や実行順序を示すことができます。これにより、プロジェクトマネージャーはプロジェクトの全体的な進行状況を把握しやすくなります。
フォルダとサブフォルダの構造の表示
- ファイルシステムのように、フォルダがさらにサブフォルダを持ち、その中に更にサブフォルダがあるという階層構造を持つデータがある場合、CTEを使用してその階層構造を簡単に掘り下げて表示することができます。
掲示板やコメントのスレッド表示
- 掲示板やブログのコメントのように、メインの投稿に対する返信がさらに返信を受ける、という階層的な構造を持つ場合、CTEを使って各投稿の階層構造を表示することができます。これにより、ユーザーはディスカッションの流れを容易に追跡できます。
パンくずリストの生成
- ウェブサイトでのナビゲーションを容易にするために、ページ間の階層関係を示すパンくずリストを生成する場合にもCTEが役立ちます。ユーザーが現在いるページとその上位のカテゴリーを表示することで、サイト内での位置把握とナビゲーションが簡単になります。例えば、商品ページにいるユーザーに対して、「ホーム > カテゴリー > サブカテゴリー > 商品」というパスを表示し、それぞれのセクションに簡単に戻れるようにすることが可能です。。

実例：カテゴリーとサブカテゴリー

よくある例としてカテゴリーとサブカテゴリーの親子関係ですよね。この階層データを扱うためにCTE（Common Table Expressions）を使用する場合、以下のようなステップで具体的な説明を行います。まず、サンプルデータ構造として、カテゴリーを表すテーブルがあると仮定しましょう。

サンプルデータ

テーブル名: Categories

CategoryID	CategoryName	ParentCategoryID
1	Electronics	NULL
2	Computers	1
3	Smartphones	1
4	Laptops	2
5	Tablets	2

このテーブルでは、CategoryIDは各カテゴリーのユニークな識別子、CategoryNameはカテゴリー名、ParentCategoryIDはそのカテゴリーの親カテゴリーのIDを示しています。親カテゴリーがない場合（最上位レベルのカテゴリーの場合）はParentCategoryIDがNULLになります。

SQLクエリ

次のSQLクエリは、CTEを使用して上記の階層構造を持つカテゴリーのリストを取得します。

WITH CategoryHierarchy AS (
    -- ベースケース: 親カテゴリーがNULLの、つまり最上位のカテゴリーを選択
    SELECT CategoryID, CategoryName, ParentCategoryID, CAST(CategoryName AS VARCHAR(MAX)) AS Path
    FROM Categories
    WHERE ParentCategoryID IS NULL
    UNION ALL
    -- 再帰的ケース: 上で選択したカテゴリーを親とするサブカテゴリーを選択し、パスに追加
    SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID, CAST(p.Path + ' -> ' + c.CategoryName AS VARCHAR(MAX)) AS Path
    FROM Categories c
    INNER JOIN CategoryHierarchy p ON c.ParentCategoryID = p.CategoryID
)
SELECT * FROM CategoryHierarchy;

WITH CategoryHierarchy AS (

-- ベースケース: 親カテゴリーがNULLの、つまり最上位のカテゴリーを選択

SELECT CategoryID, CategoryName, ParentCategoryID, CAST(CategoryName AS VARCHAR(MAX)) AS Path

FROM Categories

WHERE ParentCategoryID IS NULL

UNION ALL

-- 再帰的ケース: 上で選択したカテゴリーを親とするサブカテゴリーを選択し、パスに追加

SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID, CAST(p.Path + ' -> ' + c.CategoryName AS VARCHAR(MAX)) AS Path

FROM Categories c

INNER JOIN CategoryHierarchy p ON c.ParentCategoryID = p.CategoryID

)

SELECT * FROM CategoryHierarchy;

SQLクエリの結果

このクエリの結果は、各カテゴリーとそのフルパスを示します。

CategoryID	CategoryName	ParentCategoryID	Path
1	Electronics	NULL	Electronics
2	Computers	1	Electronics -> Computers
3	Smartphones	1	Electronics -> Smartphones
4	Laptops	2	Electronics -> Computers -> Laptops
5	Tablets	2	Electronics -> Computers -> Tablets

この結果から、Path列を使用して各カテゴリーの階層的な位置を一目で確認できます。たとえば、LaptopsカテゴリーはElectronicsの下のComputersカテゴリーに属していることがわかります。

CTEを使うことで、階層構造を持つデータのクエリや、その構造内での各要素の位置関係を効率的に把握できるようになります。特に、カテゴリーとサブカテゴリーのような親子関係を持つデータモデルでは、この手法が大変有効です。

まとめ

CTEを使った階層データの取得は、SQL Serverでのデータ管理を効率化し、複雑なデータ構造を簡単に扱うことを可能にします。組織図、カテゴリー構造、タスク管理など、多層的な情報を扱う際にCTEは大変役立ちます。このテクニックを身につけることで、データベースの潜在能力を最大限に引き出し、より洗練されたデータ操作が可能になります。

おすすめ情報

ある程度SQLに慣れてくるとより複雑なSQLを組むようになり、分析関数（ウインドウ関数）の壁にぶち当たります。本書は私が初級から抜け出すのになったきっかけの本です。比較的新しい関数なので古いSQLの本には載ってません。昔ながらのSQLを書く人はウインドウ関数の理解に苦しむケースが多いと思います。一歩先に進むためにも苦手意識を克服したいですね。