create table 在庫(
day date,
code char(1),
flg number(1),
stock number(2),
primary key (day,code,flg));
insert into 在庫 values(date '2002-12-21','A',1, 8);
insert into 在庫 values(date '2002-12-25','A',2, 5);
insert into 在庫 values(date '2002-12-25','A',3, 9);
insert into 在庫 values(date '2003-01-11','A',1, 5);
insert into 在庫 values(date '2003-01-21','A',2, 5);
insert into 在庫 values(date '2003-01-21','A',3, 5);
insert into 在庫 values(date '2003-02-01','A',1,10);
insert into 在庫 values(date '2003-02-11','A',2,10);
insert into 在庫 values(date '2003-02-21','A',3,10);
insert into 在庫 values(date '2002-12-31','B',1,15);
insert into 在庫 values(date '2002-12-31','B',2,20);
insert into 在庫 values(date '2002-12-31','B',3, 9);
insert into 在庫 values(date '2003-01-01','B',1, 8);
insert into 在庫 values(date '2003-01-11','B',2, 9);
insert into 在庫 values(date '2003-01-21','B',3, 5);
insert into 在庫 values(date '2003-02-11','B',1,10);
insert into 在庫 values(date '2003-02-11','B',2,50);
insert into 在庫 values(date '2003-02-21','B',3,10);
insert into 在庫 values(date '2002-02-21','C',3,11);
insert into 在庫 values(date '2002-02-23','C',3,22);
insert into 在庫 values(date '2003-10-10','C',3,33);
commit;
col "1月" for "99"
col "2月" for "99"
col "3月" for "99"
col "4月" for "99"
col "5月" for "99"
col "6月" for "99"
col "7月" for "99"
col "8月" for "99"
col "9月" for "99"
col "10月" for "99"
col "11月" for "99"
col "12月" for "99"
--■■■Withを使う方法■■■
with WorkView as (
select distinct code,to_char(day,'yyyymm') as 年月,
sum(decode(flg,1,-stock,stock))
over(partition by code order by to_char(day,'yyyymm')) as 累計
from 在庫)
select distinct code,to_char(day,'yyyy') as year,
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '01')),0) as "1月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '02')),0) as "2月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '03')),0) as "3月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '04')),0) as "4月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '05')),0) as "5月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '06')),0) as "6月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '07')),0) as "7月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '08')),0) as "8月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '09')),0) as "9月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '10')),0) as "10月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '11')),0) as "11月",
nvl((select b.累計 from WorkView b
where b.code = a.code
and b.年月 = (select max(c.年月) from WorkView c
where c.code = b.code
and c.年月 <= to_char(a.day,'yyyy') || '12')),0) as "12月"
from 在庫 a
order by code,year;
--■■■相関サブクエリを使う方法■■■
select distinct code,to_char(day,'yyyy') as year,
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '01') as "1月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '02') as "2月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '03') as "3月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '04') as "4月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '05') as "5月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '06') as "6月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '07') as "7月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '08') as "8月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '09') as "9月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '10') as "10月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '11') as "11月",
(select nvl(sum(decode(b.flg,1,-b.stock,b.stock)),0) from 在庫 b
where b.code = a.code
and to_char(b.day,'yyyymm') <= to_char(a.day,'yyyy') || '12') as "12月"
from 在庫 a
order by code,year;
--■■■表関数を使う方法■■■
drop type OutZaikoTypeSet;
drop type OutZaikoType;
create or replace type OutZaikoType as object(
code char(1),
year number(4),
m1 number(3),
m2 number(3),
m3 number(3),
m4 number(3),
m5 number(3),
m6 number(3),
m7 number(3),
m8 number(3),
m9 number(3),
m10 number(3),
m11 number(3),
m12 number(3));
/
create or replace type OutZaikoTypeSet as table of OutZaikoType;
/
create or replace function OutZaiko return OutZaikoTypeSet PipeLined IS
out_rec OutZaikoType := OutZaikoType(NULL,NULL,0,0,0,0,0,0,0,0,0,0,0,0);
begin
for rec in (select Code,day1,stock,
Lag(stock,1,0) over(partition by Code order by day1) as LeftPadVal,
nvl(extract(month from Lag(day1)
over(partition by Code,trunc(day1,'yyyy') order by day1))
,1) as LeftPadFrom,
case max(day1) over(partition by Code,trunc(day1,'yyyy'))
when day1 then 1 else 0 end as WillRightPad
from (select Code,trunc(day,'mm') as day1,
sum(sum(decode(flg,1,-stock,stock)))
over(partition by Code order by trunc(day,'mm')) as stock
from 在庫
group by Code,trunc(day,'mm'))
order by Code,day1) Loop
for i in rec.LeftPadFrom..extract(month from rec.day1) Loop
if i = 1 then out_rec.m1 := rec.LeftPadVal; end if;
if i = 2 then out_rec.m2 := rec.LeftPadVal; end if;
if i = 3 then out_rec.m3 := rec.LeftPadVal; end if;
if i = 4 then out_rec.m4 := rec.LeftPadVal; end if;
if i = 5 then out_rec.m5 := rec.LeftPadVal; end if;
if i = 6 then out_rec.m6 := rec.LeftPadVal; end if;
if i = 7 then out_rec.m7 := rec.LeftPadVal; end if;
if i = 8 then out_rec.m8 := rec.LeftPadVal; end if;
if i = 9 then out_rec.m9 := rec.LeftPadVal; end if;
if i = 10 then out_rec.m10 := rec.LeftPadVal; end if;
if i = 11 then out_rec.m11 := rec.LeftPadVal; end if;
if i = 12 then out_rec.m12 := rec.LeftPadVal; end if;
end Loop;
if extract(month from rec.day1) = 1 then out_rec.m1 := rec.stock; end if;
if extract(month from rec.day1) = 2 then out_rec.m2 := rec.stock; end if;
if extract(month from rec.day1) = 3 then out_rec.m3 := rec.stock; end if;
if extract(month from rec.day1) = 4 then out_rec.m4 := rec.stock; end if;
if extract(month from rec.day1) = 5 then out_rec.m5 := rec.stock; end if;
if extract(month from rec.day1) = 6 then out_rec.m6 := rec.stock; end if;
if extract(month from rec.day1) = 7 then out_rec.m7 := rec.stock; end if;
if extract(month from rec.day1) = 8 then out_rec.m8 := rec.stock; end if;
if extract(month from rec.day1) = 9 then out_rec.m9 := rec.stock; end if;
if extract(month from rec.day1) = 10 then out_rec.m10 := rec.stock; end if;
if extract(month from rec.day1) = 11 then out_rec.m11 := rec.stock; end if;
if extract(month from rec.day1) = 12 then out_rec.m12 := rec.stock; end if;
if rec.WillRightPad = 1 then
for i in extract(month from rec.day1)..12 Loop
if i = 1 then out_rec.m1 := rec.stock; end if;
if i = 2 then out_rec.m2 := rec.stock; end if;
if i = 3 then out_rec.m3 := rec.stock; end if;
if i = 4 then out_rec.m4 := rec.stock; end if;
if i = 5 then out_rec.m5 := rec.stock; end if;
if i = 6 then out_rec.m6 := rec.stock; end if;
if i = 7 then out_rec.m7 := rec.stock; end if;
if i = 8 then out_rec.m8 := rec.stock; end if;
if i = 9 then out_rec.m9 := rec.stock; end if;
if i = 10 then out_rec.m10 := rec.stock; end if;
if i = 11 then out_rec.m11 := rec.stock; end if;
if i = 12 then out_rec.m12 := rec.stock; end if;
end Loop;
end if;
if rec.WillRightPad = 1 then
out_rec.code := rec.Code;
out_rec.year := extract(year from rec.day1);
pipe row(out_rec);
out_rec := OutZaikoType(NULL,NULL,0,0,0,0,0,0,0,0,0,0,0,0);
end if;
end Loop;
end;
/
sho err
select code,year,m1 as "1月",m2 as "2月",m3 as "3月",m4 as "4月",m5 as "5月",
m6 as "6月",m7 as "7月",m8 as "8月",m9 as "9月",m10 as "10月",m11 as "11月",m12 as "12月"
from table(OutZaiko);