トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-127 クロス集計して空白行を追加

SQLパズル

DENテーブル
CD     EDA  KIN
-----  ---  ----
00001    1   100
00001    2   200
00001    3   300
00002    1    10
00002    2    20
00002    3    30
00002    4    40
00002    5    50
00002    6    60

(1) 1ページは5明細
(2) ソート順はCD,EDA順
(3) CDが変わった場合には改ページ
(4) CDごとの最後のデータの次の行には、CDのKINの合計のための小計行を出力
(5) 最後のCDの小計のあとに、全レコードのKINの合計を出力
(6) 1ページが5明細に満たない場合は、5明細になるように空白行を出力
(7) 各レコードは総ページ数、ページ数及び何行目かのデータを持つ

出力結果
CD      EDA   KIN  行  PAGE  総ページ数
-----  ----  ----  --  ----  ----------
00001     1   100   1     1           3
00001     2   200   2     1           3
00001     3   300   3     1           3
小計   NULL   600   4     1           3
NULL   NULL  NULL   5     1           3
00002     1    10   1     2           3
00002     2    20   2     2           3
00002     3    30   3     2           3
00002     4    40   4     2           3
00002     5    50   5     2           3
00002     6    60   1     3           3
小計   NULL   210   2     3           3
合計   NULL   810   3     3           3
NULL   NULL  NULL   4     3           3
NULL   NULL  NULL   5     3           3

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


データ作成スクリプト

create table DEN(
CD  char(5),
EDA number(1),
KIN number(3),
primary key(CD, EDA));

insert into DEN values('00001',1,100);
insert into DEN values('00001',2,200);
insert into DEN values('00001',3,300);
insert into DEN values('00002',1,10);
insert into DEN values('00002',2,20);
insert into DEN values('00002',3,30);
insert into DEN values('00002',4,40);
insert into DEN values('00002',5,50);
insert into DEN values('00002',6,60);
commit;


SQL

--■■■表関数を使う方法■■■
create or replace Package Pack10_127 Is
    type PrintType is record(
    CD  DEN.CD%type,
    EDA DEN.EDA%type,
    KIN DEN.KIN%type,
    LINE number(1),
    PAGE number(1));

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function PrintRecord return Pack10_127.PrintTypeSet PipeLined IS
    現在行 pls_Integer := 1;
    out_rec Pack10_127.PrintType;
begin
    for rec in (select CD,EDA,sum(KIN) as KIN,
                       Lead(CD) over(order by CD,EDA) as LeadCD,
                       grouping(CD) as groupingCD,
                       grouping(EDA) as groupingEDA
                  from DEN
                 group by RollUp(CD,EDA)
                 order by CD,EDA) Loop

        out_rec.CD := case when rec.groupingCD  = 1 then '合計'
                           when rec.groupingEDA = 1 then '小計'
                           else rec.CD end;
        out_rec.EDA  := rec.EDA;
        out_rec.KIN  := rec.KIN;
        out_rec.LINE := case mod(現在行,5) when 0 then 5 else mod(現在行,5) end;
        out_rec.PAGE := ceil(現在行/5);
        pipe row(out_rec);

        現在行 := 現在行+1;

        if rec.CD != rec.LeadCD or rec.groupingCD = 1 then
            while mod(現在行,5) != 1 Loop
                out_rec.CD   := null;
                out_rec.EDA  := null;
                out_rec.KIN  := null;
                out_rec.LINE := case mod(現在行,5) when 0 then 5 else mod(現在行,5) end;
                out_rec.PAGE := ceil(現在行/5);
                pipe row(out_rec);

                現在行 := 現在行+1;
            end loop;
        end if;
    end loop;
end;
/

sho err

select a.*,count(*) over()/5 as 総ページ数 from table(PrintRecord) a;

--■■■select文を使う方法■■■
select decode(b.Counter,1,a.CD) as CD,
decode(b.Counter,1,a.EDA) as EDA,
decode(b.Counter,1,a.KIN) as KIN,
case mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5)
when 0 then 5
else mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5) end as 行,
ceil(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2))/5) as PAGE,
count(*) over()/5 as 総ページ数
from (select
      case when grouping(CD) = 1 then max(CD) over() else CD end as ソート用CD,
      count(EDA) over(partition by CD) as 同一コード件数,
      case when grouping(CD)  = 1 then '合計'
           when grouping(EDA) = 1 then '小計'
           else CD end as CD,
      EDA,sum(KIN) as KIN,
      Row_Number() over(order by CD desc nulls Last,EDA nulls first) as 最後の小計
      from DEN
      group by RollUp(CD,EDA)) a,
      (select RowNum as Counter from all_catalog where RowNum <= 5) b
where (a.CD !='小計' and b.Counter = 1)
   or (a.CD  ='小計' and a.最後の小計 != 1 and b.Counter <= 5-mod(a.同一コード件数,5))
   or (a.CD  ='小計' and a.最後の小計  = 1 and b.Counter <= 5-mod(a.同一コード件数+1,5))
order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2);


解説

クロス集計の結果に対して、
空白行を追加してます

case mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5)
when 0 then 5
else mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5) end as 行,
は
nvl(nullif(
     mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5)
,0),5) as 行,
で書き換えることができますが、
逆に分かりにくいかもしれません