【SQL】 SQL Server 2008のhierarchyidデータ型を使って階層毎に集計する。
http://blogs.wankuma.com/trapemiya/archive/2009/10/29/182544.aspx
の続きです。
前回はSumAllSalesUnderMeという関数を用意し、自分の下のノードにぶら下がっている売上を全て集計していました。
この関数を使うことによって任意のノードの総売り上げを簡単に求めることができます。これはこれは便利なのですが、組織全体について集計する際に、ノード毎にSumAllSalesUnderMeを用いて計算するのは非効率です。効率が良いのは、下の階層から上の階層に向かってノードの売上を順に足し込んでいくことでしょう。これを実現したのが以下になります。
/* ノード毎に売上を集計し、ノード毎に売上を保持する一時テーブルを作成する。 */
create table #temp
(
TOrganizationID int not null,
THierarchyid hierarchyid not null,
THierarchyLevel int not null,
TOrganizationName nvarchar(30) not null,
TSales int not null --このノードにぶら下がっている売上合計
)
create unique clustered index tempIK01 on #temp(THierarchyLevel, THierarchyid);
declare @maxLevel int
declare @level int;
/* ノード毎に売上を集計し、#tempに放り込む。 */
select @maxLevel = MAX(OR01HierarchyLevel) from OR01Organization
set @level = 0;
while @level <= @maxLevel
begin
insert into #temp (TOrganizationID, THierarchyid, THierarchyLevel, TOrganizationName, TSales)
select OR01OrganizationID, OR01Hierarchyid, @level, OR01OrganizationName,
(select isnull(SUM(SA01Sales),0) from SA01Sales where SA01OrganizationID = OR01OrganizationID)
from OR01Organization
where OR01HierarchyLevel = @level;
set @level = @level + 1;
end
/* 下の階層から上に向かってノードを順に足し込んでいく。 */
set @level = @maxLevel;
while @level > 0
begin
with CTE as
(
select p.TSales pSales, c.ChildrenSales cSales
from #temp p
join (select t.THierarchyid.GetAncestor(1) hid, SUM(t.TSales) ChildrenSales
from #temp t
where t.THierarchyLevel = @level
group by t.THierarchyid.GetAncestor(1)) c
on p.THierarchyLevel =@level - 1 and p.THierarchyid = hid
)
update CTE set pSales = pSales + cSales;
SET @level = @level - 1;
end
/* 集計結果を表示する。 */
select THierarchyid.ToString(),
space(THierarchyLevel * 5) + TOrganizationName,
THierarchyLevel,
TSales
from #temp
order by THierarchyid;
drop table #temp;
go
前回と全く同じ結果が得られます。
| 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 |
本当はCTEの再帰でやりたかったんですが、CTEの再帰部分ではsumなどの集計関数が使えないため断念しました。orz
CTEの再帰部分で集計関数とか外部結合とかできるようになるといいなぁ。細かい制約については以下を読んでください。
WITH common_table_expression (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms175972.aspx