レッスン 2: 階層テーブルでデータを作成して管理する

[アーティクル]
07/31/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

レッスン 1 では、既存のテーブルを変更して hierarchyid データ型を使用し、 hierarchyid 列に既存のデータ表現でデータを設定しました。このレッスンでは、まず新しいテーブルを作成し、次に階層的な手法を使用してデータを挿入します。その後、階層的な手法を使用して、データのクエリおよび操作を実行します。

前提条件

このチュートリアルを実行するには、SQL Server Management Studio、SQL Server を実行しているサーバーへのアクセス、および AdventureWorks2022 データベースが必要です。

SQL Server Management Studio (SSMS) をインストールする
SQL Server 2022 Developer Edition をインストールします。
AdventureWorks サンプルデータベースをダウンロードします。

SSMS でデータベースを復元する手順については、「SSMS を使用してデータベースバックアップを復元する」を参照してください。

hierarchyid データ型を使用してテーブルを作成する

次の例では、従業員データとその報告階層を含む EmployeeOrg という名前のテーブルを作成します。この例では、テーブルを AdventureWorks2022 データベースに作成しますが、これは任意です。例をわかりやすくするために、このテーブルには 5 つの列のみ含まれています。

OrgNode は階層関係を格納するhierarchyid 列です。
OrgLevel は、階層内の各ノードレベルを格納する OrgNode 列に基づいて計算された列です。これは、幅優先のインデックスに使用されます。
EmployeeID には、給与処理などのアプリケーションで使用される通常の従業員識別番号が含まれます。新しいアプリケーションの開発では、アプリケーションは OrgNode 列を使用でき、この個別の EmployeeID 列は不要です。
EmpName には、従業員の名前が含まれます。
Title には、従業員の役職が含まれます。

EmployeeOrg テーブルを作成する

クエリエディターウィンドウで、次のコードを実行し、 EmployeeOrg テーブルを作成します。 OrgNode 列を、クラスター化インデックスのある主キーとして指定すると、深さ優先のインデックスが作成されます。

USE AdventureWorks2022;
GO

IF OBJECT_ID('HumanResources.EmployeeOrg') IS NOT NULL
    DROP TABLE HumanResources.EmployeeOrg

CREATE TABLE HumanResources.EmployeeOrg (
    OrgNode HIERARCHYID PRIMARY KEY CLUSTERED,
    OrgLevel AS OrgNode.GetLevel(),
    EmployeeID INT UNIQUE NOT NULL,
    EmpName VARCHAR(20) NOT NULL,
    Title VARCHAR(20) NULL
);
GO

次のコードを実行して OrgLevel 列と OrgNode 列に複合インデックスを作成し、効率的な幅優先の検索をサポートします。
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

これでテーブルはデータを受け入れる準備ができました。次に、階層的な手法を使用してテーブルにデータを入力します。

階層的な手法を使用して階層テーブルにデータを入力する

AdventureWorks2022 にはマーケティング部門で働く従業員が 8 人います。従業員の階層は、次のようになります。

David( EmployeeID 6) はマーケティングマネージャーです。 3 人のマーケティングスペシャリストが David に報告します。

Sariya, EmployeeID 46
John, EmployeeID 271
Jill, EmployeeID 119

マーケティングアシスタント Wanida (EmployeeID 269)、 Sariyaへのレポート、およびマーケティングアシスタント Mary (EmployeeID 272)、 Johnへのレポート。

階層ツリーのルートを挿入する

次の例では、Marketing Manager である David を、階層のルートに位置するテーブルに挿入します。 OrdLevel 列は計算列です。したがって、INSERT ステートメントの一部ではありません。この最初のレコードでは、 GetRoot(データベースエンジン) メソッドを使用して、この最初のレコードを階層のルートとして挿入します。
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
```

テーブルの最初の行を調べるには、次のコードを実行します。

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;

結果セットは次のとおりです。

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager

前のレッスンと同様、 ToString() メソッドを使用して、 hierarchyid データ型をよりわかりやすい形式に変換します。

部下を挿入する

Sariya は David に報告します。 Sariya's のノードを挿入するには、 hierarchyid データ型の適切な OrgNode値を作成する必要があります。次のコードでは、 hierarchyid データ型の変数を作成して、テーブルのルート OrgNode 値をその変数に代入します。次に、その変数を GetDescendant(データベースエンジン) メソッドと共に使用して、部下のノードである行を挿入します。 GetDescendant は、2 つの引数を受け取ります。引数値の次のオプションを確認してください。
- 親が NULL場合、 GetDescendant は NULLを返します。
- 親が NULLされず、 child1 と child2 の両方が NULL場合、 GetDescendant は親の子を返します。
- 親と child1 が NULLされず、child2 が NULL場合、 GetDescendant は child1より大きい親の子を返します。
- 親と child2 が NULL されず、 child1 が NULL場合、 GetDescendant は child2未満の親の子を返します。
- 親、‭child1‬、‭child2‬ のすべてが NULL でない場合、GetDescendant‬ は ‭child1‬ より大きく ‭child2 より小さい親の子を返します。
テーブルにはルート以外にまだ 1 行もないため、次のコードではルート親の ‭(NULL, NULL)‬ 引数を使用します。 Sariya を挿入するには、次のコードを実行します。
```
DECLARE @Manager HIERARCHYID
SELECT @Manager = HIERARCHYID::GetRoot()
FROM HumanResources.EmployeeOrg;

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES
(@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist');
```

テーブルにクエリを実行してエントリがどのように表示されるかを確認するには、最初の手順のクエリを繰り返します。

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;

結果セットは次のとおりです。

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist

新しいノードを入力するためのプロシージャを作成する

データの入力を簡略化するには、次のストアドプロシージャを作成して、 EmployeeOrg テーブルに従業員を追加します。このプロシージャは、追加される従業員に関する入力値を受け取ります。これには、新しい従業員の管理者の EmployeeID、新しい従業員の EmployeeID 番号、従業員の名と役職が含まれます。このプロシージャでは、 GetDescendant() と GetAncestor(データベースエンジン) メソッドも使用します。プロシージャを作成するには、次のコードを実行します。

CREATE PROCEDURE AddEmp (
    @mgrid INT,
    @empid INT,
    @e_name VARCHAR(20),
    @title VARCHAR(20)
)
AS
BEGIN
    DECLARE @mOrgNode HIERARCHYID, @lc HIERARCHYID;

    SELECT @mOrgNode = OrgNode
    FROM HumanResources.EmployeeOrg
    WHERE EmployeeID = @mgrid;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;

    SELECT @lc = max(OrgNode)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @mOrgNode;

    INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
    VALUES (@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title);

    COMMIT;
END;
GO

次の例では、 David の直接または間接的な部下である残り 4 人の従業員を追加します。

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist';
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist';
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant';
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant';

EmployeeOrg テーブルの行を調べるには、再び次のクエリを実行します。

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

結果セットは次のとおりです。

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- --------------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

テーブルに、マーケティング組織が完全に作成されました。

階層的な手法を使用して階層テーブルをクエリする

HumanResources.EmployeeOrg テーブルに完全にデータが設定されたので、ここでは階層的な手法のいくつかを使用して階層をクエリする方法を示します。

下位ノードを検索する

Sariya には 1 人の部下がいます。 Sariya の部下に対するクエリを実行するには、 IsDescendantOf (データベースエンジン) メソッドを使用する次のクエリを実行します。
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
結果には、 Sariya と Wanidaの両方が一覧表示されます。 Sariya は、0 レべルの子孫であるために表示されています。 Wanida は 1 レベルの子孫です。
GetAncestor (データベースエンジン) メソッドを使用してこの情報に対してクエリを実行することもできます。 GetAncestor が返そうとしているレベルの引数を受け取ります。 Wanida は Sariya よりも 1 つ下のレベルであるため、次のコードに示すように GetAncestor(1) を使用します。
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee
```
この場合、結果には Wanida のみが表示されます。
次に、 @CurrentEmployee を David (EmployeeID 6) に、レベルを 2 にそれぞれ変更します。次のコードを実行した場合も Wanida が返されます。
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 6;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee
```
この場合は、David に直属する 2 つ下のレベルの Mary も返されます。

GetRoot と GetLevel を使用する

階層が大きくなるにつれ、メンバーが階層内のどこに位置するのかを確認するのが難しくなります。各行が階層内の何レベル下にあるかを検索するには、 GetLevel (データベースエンジン) メソッドを使用します。すべての行のレベルを表示するには次のコードを実行します。
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
階層内のルートノードを検索するには、 GetRoot (データベースエンジン) メソッドを使用します。次のコードは、ルートである単一の行を返します。
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

階層的な手法を使用して階層テーブルのデータを並べ替える

適用対象: SQL Server

階層の再編成は、一般的なメンテナンスタスクです。ここでは、UPDATE ステートメントを GetReparentedValue メソッド(データベースエンジン)と共に使用して、まず 1 つの行を階層内の新しい位置に移動します。次に、サブツリー全体を新しい場所に移動します。

GetReparentedValue メソッドは 2 つの引数を受け取ります。最初の引数は、変更する階層の一部を表します。たとえば、階層が /1/4/2/3/ で、/1/4/ セクションを変更する場合、階層は /2/1/2/3/ になり、最後の 2 つのノード (2/3/) は変更されずに残るため、変更するノード (/1/4/) を最初の引数として指定する必要があります。 2 番目の引数には、新しい階層レベルを指定します。この例では、/2/1/ です。 2 つの引数には同じ数のレベルが含まれている必要はありません。

単一行を階層内の新しい位置に移動する

現在、Wanida は Sariya に直属しています。この手順では、Wanida を現在のノード /1/1/ から移動して、Jill に直属するようにします。新しいノードは /3/1/ になるので、最初の引数は /1/ 、2 番目の引数は/3/ になります。これらは、Sariya と Jill の OrgNode 値に相当します。 Wanida を Sariya の組織から Jill の組織に移動するには、次のコードを実行します。

DECLARE @CurrentEmployee HIERARCHYID,
    @OldParent HIERARCHYID,
    @NewParent HIERARCHYID;

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 269;

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;

UPDATE HumanResources.EmployeeOrg
SET OrgNode = @CurrentEmployee.GetReparentedValue(@OldParent, @NewParent)
WHERE OrgNode = @CurrentEmployee;
GO

結果を表示するには、次のコードを実行します。

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

Wanida がノード /3/1/ に移動しています。

階層のセクションを再編成する

多数の人員を一度に移動する方法を示すため、まず次のコードを実行して Wanida に直属するインターンを追加します。
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Kevin が Wanida に直属するようになりました。Wanida は Jill に直属し、Jill は David に直属します。したがって、Kevin のレベルは /3/1/1/ です。 Jill の部下をすべて新しい管理者に移動するには、/3/ として OrgNode を持つすべてのノードを新しい値に更新します。 Kevin を Wanida に直属させたまま、Wanida を Sariya に直属するように更新するには、次のコードを実行します。

DECLARE @OldParent HIERARCHYID,
    @NewParent HIERARCHYID

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;-- Jill

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;-- Sariya

DECLARE children_cursor CURSOR
FOR
SELECT OrgNode
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @OldParent;

DECLARE @ChildId HIERARCHYID;

OPEN children_cursor

FETCH NEXT
FROM children_cursor
INTO @ChildId;

WHILE @@FETCH_STATUS = 0
BEGIN
    START:

    DECLARE @NewId HIERARCHYID;

    SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @NewParent;

    UPDATE HumanResources.EmployeeOrg
    SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)
    WHERE OrgNode.IsDescendantOf(@ChildId) = 1;

    IF @@error <> 0
        GOTO START -- On error, retry

    FETCH NEXT
    FROM children_cursor
    INTO @ChildId;
END

CLOSE children_cursor;

DEALLOCATE children_cursor;

結果を表示するには、次のコードを実行します。

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

結果セットは次のとおりです。

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/1/1/1/      0x5AD0  3        291        Kevin   Marketing Intern
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

Jill に直属していた組織のツリー全体 (Wanida と Kevin の両方) が Sariya に直属するようになりました。

階層のセクションを再構成するストアドプロシージャについては、階層データ (SQL Server)の「Move サブツリー」セクションを参照してください。

次の方法で共有

レッスン 2: 階層テーブルでデータを作成して管理する

前提条件

hierarchyid データ型を使用してテーブルを作成する

EmployeeOrg テーブルを作成する

階層的な手法を使用して階層テーブルにデータを入力する

階層ツリーのルートを挿入する

部下を挿入する

新しいノードを入力するためのプロシージャを作成する

階層的な手法を使用して階層テーブルをクエリする

下位ノードを検索する

GetRoot と GetLevel を使用する

階層的な手法を使用して階層テーブルのデータを並べ替える

単一行を階層内の新しい位置に移動する

階層のセクションを再編成する

フィードバック

その他のリソース