10-337 子ノードでの総和を計算

SQLパズル

treeNodeT
ID  oyaID  quantity  cost
--  -----  --------  ----
AA  null          2  null
BB  AA            3  null
CC  BB            4  null
DD  CC            2    10
EE  CC            1     5

costがnullであれば、
cost = それぞれの子ノードのquantity*costの、合計
としてレベルの高いノードから順にcostを求めていく。
また、quantity*cost の値を"Total Unit Cost"として求める。

出力結果
ID  oyaID  quantity  cost  Total Unit Cost
--  -----  --------  ----  ---------------
AA  null          2   300              600
BB  AA            3   100              300
CC  BB            4    25              100
EE  CC            1     5                5
DD  CC            2    10               20

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table treeNodeT(ID,oyaID,quantity,cost) as
select 'AA',null,2,null from dual union all
select 'BB','AA',3,null from dual union all
select 'CC','BB',4,null from dual union all
select 'DD','CC',2,  10 from dual union all
select 'EE','CC',1,   5 from dual;

SQL

col oyaID for a10

--■■■model句を使う方法(10g以降)■■■
select ID,oyaID,quantity,cost,
quantity*cost as "Total Unit Cost"
  from treeNodeT
start with oyaID is null
connect by prior ID = oyaID
model
dimension by(ID,oyaID)
measures(quantity,cost,Row_Number() over(order by Level desc) as rn)
rules(cost[any,any] order by rn=
case when cost[cv(),cv()] is null
     then sum(cost*quantity)[any,cv(ID)]
     else cost[cv(),cv()] end)
order by rn desc;

--■■■表関数を使う方法■■■
create or replace package Pack10_337 Is
    type PrintRType Is record(
    ID       treeNodeT.ID%type,
    oyaID    treeNodeT.ID%type,
    quantity number(1),
    cost     number(3),
    SortKey  number(1));

    type PrintRTypeSet Is table of PrintRType;
end;
/

create or replace function PrintR10337 return Pack10_337.PrintRTypeSet PipeLined IS
    outR Pack10_337.PrintRType;
    cursor cur is
    select ID,oyaID,quantity,cost
      from treeNodeT
    start with oyaID is null
    connect by prior ID = oyaID
    order by Level desc;

    type saveDataDef is table of cur%rowType index by binary_integer;
    saveData saveDataDef;

    wkCost treeNodeT.cost%type;
begin
    open cur;
    fetch cur bulk collect into saveData;
    close cur;

    for I in 1..saveData.Last Loop
        if saveData(I).cost is null then
            wkCost := 0;
            for J in reverse saveData.First..I Loop
                if saveData(I).ID = saveData(J).oyaID then
                    wkCost := wkCost + saveData(J).cost*saveData(J).quantity;
                end if;
            end Loop;
            saveData(I).cost := wkCost;
        end if;
    end Loop;

    for I in 1..saveData.Last Loop
        outR.ID       := saveData(I).ID;
        outR.oyaID    := saveData(I).oyaID;
        outR.quantity := saveData(I).quantity;
        outR.cost     := saveData(I).cost;
        outR.SortKey  := I;
        pipe row(outR);
    end Loop;
end;
/

sho err

col oyaID for a10

select ID,oyaID,quantity,cost,
quantity*cost as "Total Unit Cost"
  from table(PrintR10337) order by SortKey desc;

解説

model句のdimension指定で子にアクセスしてます。
親子関係においてレベルが1違うだけならmodel句のdimension指定が便利なこともあります。