The road to C# master trapemiya

RDBのデータ型
http://blogs.wankuma.com/ognac/archive/2009/10/28/182484.aspx

で、ついhierarchyid型について口走ってしまったので、以下にまとめてみました。どうせまとめようと思っていたので、良いきっかけになって良かったです。

SQL Server 2008でhierarchyid型が追加されました。これによってレコードを階層構造で管理できるようになります。
ここでは詳しく説明しませんので、ご存じない方はまず以下をご覧になると良いと思います。

hierarchyid データ型の使用 (データベース エンジン)
http://msdn.microsoft.com/ja-jp/library/bb677173.aspx

ここではhierarchyid型によって管理された階層構造毎に集計するデモを掲載します。ある会社の営業売上を階層毎に集計してみましょう。以下のような売上があるとします。

目標は以下のように階層毎に集計することです。

以上を実現するために組織と組織の売上という二つのテーブルを作成します。

まずは組織テーブルからです。hierarchyid型を使用して作成します。

create table OR01Organization
(
    OR01OrganizationID       int            identity (1,1)        not null  primary key,  
    OR01Hierarchyid            hierarchyid                          not null,  
    OR01HierarchyLevel       as OR01Hierarchyid.GetLevel() ,         
    OR01OrganizationName   nvarchar(30)                        not null  
)

ここではOR01Hierarchyidを主キーにしていませんので、インデックスを作成しておきます。

create unique index OR01OrganizationIK01 on OR01Organization (OR01Hierarchyid)

続いて売上テーブルを作成します。組織テーブルとの間に参照整合性制約を作成しています。

create table SA01Sales
(
    SA01SalesID            int  identity (1,1)  not null primary key,  
    SA01OrganizationID   int                  not null,
    SA01Sales               int  
)

alter table SA01Sales
add constraint SA01SalesFK01 foreign key (SA01OrganizationID)
    references OR01Organization(OR01OrganizationID)

データを作成します。

insert into OR01Organization (OR01Hierarchyid, OR01OrganizationName) values
(hierarchyid::GetRoot(), '全社'),
('/1/', '東京支社'),
('/1/1/', '統括事業部'),
('/1/1/1/', '営業１課'),
('/1/1/2/', '営業２課'),
('/2/', '大阪支社'),
('/2/1/', '営業部'),
('/2/1/1/', '第１営業課'),
('/2/1/2/', '第２営業課')

ここまで出来たら一度組織図を表示してみましょう。

select OR01Hierarchyid.ToString() as Path,
       OR01OrganizationName as Name,
       OR01HierarchyLevel as Level
  from OR01Organization order by OR01Hierarchyid

以下のように表示されればＯＫです。

Path       Name            Level
/            全社               0
/1/         東京支社          1
/1/1/       統括事業部       2
/1/1/1/    営業１課          3
/1/1/2/    営業２課          3
/2/          大阪支社         1
/2/1/       営業部            2
/2/1/1/    第１営業課        3
/2/1/2/    第２営業課        3

Nameにインデントが無くて見難い場合には、以下のようにLevel毎に適当に頭に空白を入れると良いでしょう。

space(OR01HierarchyLevel * 5) + OR01OrganizationName as Name

では次に売上データを作成していきます。

SA01OrganizationIDはOR01OrganizationIDを指定しますが、OR01OrganizationIDは自動インクリメント（オートナンバー）なので、レコードが追加された順に1, 2, 3, ・・・となります。

insert into SA01Sales (SA01OrganizationID, SA01Sales) values
(4, 10000),   --東京支社　営業１課
(5, 50000),   --東京支社　営業２課
(8, 20000),   --大阪支社　第１営業課
(9, 30000)    --大阪支社　第２営業課

さて、いよいよ階層毎の集計をします。階層の集計は、自分にぶら下がっている全ての子供の売上データの合計になります。OR01Hierarchyidというhierarchyid型を辿れば、自分にぶら下がっている全ての子供である組織データが見つかります。
その見つかった組織データにぶら下がっている売上データを合計すれば目的が達せられます。
これを実現した関数が以下になります。
CTE内では外部結合ができませんので、select文を使って売上データを求めています。

create function SumAllSalesUnderMe
(
    @hierarchyid as hierarchyid
)
returns bigint
as
begin

declare @compwk bigint;

with CTE(CTEHierarchyid, CTESales)
AS
(
    select  OR01Hierarchyid, (select SA01Sales from SA01Sales as Sa1 where Sa1.SA01OrganizationID = OR01OrganizationID)
                                    from OR01Organization
                                    where OR01Hierarchyid = @hierarchyid
    union all
    select  OR01Hierarchyid, (select SA01Sales from SA01Sales as Sa2 where Sa2.SA01OrganizationID = Or1.OR01OrganizationID)
                                    from OR01Organization as Or1
                                    inner join CTE as c on c.CTEHierarchyid = Or1.OR01Hierarchyid.GetAncestor(1)
)

select @compwk = sum(CTESales) from CTE

return @compwk

end

これで全ての準備が整いました。以下のようにselect文を発行すれば、階層毎に集計された結果が得られます。

select OR01Hierarchyid.ToString() as Path,
       space(OR01HierarchyLevel * 5) + OR01OrganizationName as Name,
       OR01HierarchyLevel as Level,
       dbo.SumAllSalesUnderMe(OR01Hierarchyid) as SubTotal
  from OR01Organization order by OR01Hierarchyid