The road to C# master trapemiya

【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

前回と全く同じ結果が得られます。

本当はCTEの再帰でやりたかったんですが、CTEの再帰部分ではsumなどの集計関数が使えないため断念しました。orz
CTEの再帰部分で集計関数とか外部結合とかできるようになるといいなぁ。細かい制約については以下を読んでください。

WITH common_table_expression (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms175972.aspx