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型によって管理された階層構造毎に集計するデモを掲載します。ある会社の営業売上を階層毎に集計してみましょう。以下のような売上があるとします。
| 全社 |
支社 |
部 |
課 |
売上 |
| 全社 |
|
|
|
|
| |
東京支社 |
|
|
|
| |
|
統括事業部 |
|
|
| |
|
|
営業１課 |
10000 |
| |
|
|
営業２課 |
50000 |
| |
大阪支社 |
|
|
|
| |
|
営業部 |
|
|
| |
|
|
第１営業課 |
20000 |
| |
|
|
第２営業課 |
30000 |
目標は以下のように階層毎に集計することです。
| 全社 |
支社 |
部 |
課 |
売上 |
集計 |
| 全社 |
|
|
|
|
110000 |
| |
東京支社 |
|
|
|
60000 |
| |
|
統括事業部 |
|
|
60000 |
| |
|
|
営業１課 |
10000 |
10000 |
| |
|
|
営業２課 |
50000 |
50000 |
| |
大阪支社 |
|
|
|
50000 |
| |
|
営業部 |
|
|
50000 |
| |
|
|
第１営業課 |
20000 |
20000 |
| |
|
|
第２営業課 |
30000 |
30000 |
以上を実現するために組織と組織の売上という二つのテーブルを作成します。
まずは組織テーブルからです。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
| Path |
Name |
Level |
SubTotal |
| / |
全社 |
0 |
110000 |
| /1/ |
東京支社 |
1 |
60000 |
| /1/1/ |
統括事業部 |
2 |
60000 |
| /1/1/1/ |
営業１課 |
3 |
10000 |
| /1/1/2/ |
営業２課 |
3 |
50000 |
| /2/ |
大阪支社 |
1 |
50000 |
| /2/1/ |
営業部 |
2 |
50000 |
| /2/1/1/ |
第１営業課 |
3 |
20000 |
| /2/1/2/ |
第２営業課 |
3 |
30000 |