10-322 行を補完しつつ累計を求める

SQLパズル

TranTable
DayNum  tranType  amount
------  --------  ------
     1  CR           200
     1  DR          -300
     2  CR           800
     2  DR          -200
     3  CR           900
     3  DR          -500

DayNumが1から4までで、tranTypeがOpeningとClosingの行を補完する。
Openingのamountは、その行未満のDayNumでtranTypeがCRとDRの行のamountの合計とし、
Closingのamountは、その行以下のDayNumでtranTypeがCRとDRの行のamountの合計とする。

出力結果
DayNum  tranType  amount
------  --------  ------
     1  Opening        0
     1  CR           200
     1  DR          -300
     1  Closing     -100
     2  Opening     -100
     2  CR           800
     2  DR          -200
     2  Closing      500
     3  Opening      500
     3  CR           900
     3  DR          -500
     3  Closing      900
     4  Opening      900
     4  Closing      900

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

データ作成スクリプト

create table TranTable(DayNum,tranType,amount) as
select 1,'CR', 200 from dual union
select 1,'DR',-300 from dual union
select 2,'CR', 800 from dual union
select 2,'DR',-200 from dual union
select 3,'CR', 900 from dual union
select 3,'DR',-500 from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select DayNum,tranType,nvl(amount,0) as amount
  from TranTable
 model
dimension by(DayNum,cast(tranType as varchar2(15)) as tranType)
measures(amount)
rules(
amount[for DayNum in(1,2,3,4),'Opening']
= sum(amount)[DayNum< cv(),tranType in('CR','DR')],
amount[for DayNum in(1,2,3,4),'Closing']
= sum(amount)[DayNum<=cv(),tranType in('CR','DR')])
order by DayNum,
decode(tranType,'Opening',1,'CR',2,'DR',3,'Closing',4);

--■■■表関数を使う方法■■■
create or replace type ReturnType as object(
DayNum   number(1),
tranType varchar2(15),
amount   number(4));
/

create or replace type ReturnTypeSet as table of ReturnType;
/

create or replace function FuncTranTable return ReturnTypeSet PipeLined IS
    outR ReturnType := ReturnType(NULL,NULL,NULL);
    type saveDataDef is table of ReturnType index by binary_integer;
    saveData saveDataDef;
    OpeningVal TranTable.amount%Type;
    ClosingVal TranTable.amount%Type;
begin
    select ReturnType(DayNum,tranType,amount) bulk collect into saveData from TranTable;

    for newDayNum in 1..4 Loop
        OpeningVal:=0;
        ClosingVal:=0;
        for J in 1..saveData.Count Loop
            if ( saveData(J).tranType in('CR','DR')
             and saveData(J).DayNum < newDayNum) then
                OpeningVal := OpeningVal+saveData(J).amount;
            end if;

            if ( saveData(J).tranType in('CR','DR')
             and saveData(J).DayNum <= newDayNum) then
                ClosingVal := ClosingVal+saveData(J).amount;
            end if;
        end Loop;

        saveData(saveData.Count+1) := ReturnType(newDayNum,'Opening',OpeningVal);
        saveData(saveData.Count+1) := ReturnType(newDayNum,'Closing',ClosingVal);
    end Loop;

    for I in 1..saveData.Count Loop
        outR.DayNum  := saveData(I).DayNum;
        outR.tranType := saveData(I).tranType;
        outR.amount := saveData(I).amount;
        pipe row(outR);
    end Loop;
end;
/

sho err

select DayNum,tranType,amount from table(FuncTranTable)
order by DayNum,decode(tranType,'Opening',1,'CR',2,'DR',3,'Closing',4);

解説

model句の使いどころでしょうねぇ

---