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

10-6 累計を行列変換

SQLパズル

在庫テーブル
       day  code  flg  stock
----------  ----  ---  -----
2002/12/21  A     1        8
2002/12/25  A     2        5
2002/12/25  A     3        9
2003/01/11  A     1        5
2003/01/21  A     2        5
2003/01/21  A     3        5
2003/02/01  A     1       10
2003/02/11  A     2       10
2003/02/21  A     3       10
2002/12/31  B     1       15
2002/12/31  B     2       20
2002/12/31  B     3        9
2003/01/01  B     1        8
2003/01/11  B     2        9
2003/01/21  B     3        5
2003/02/11  B     1       10
2003/02/11  B     2       50
2003/02/21  B     3       10
2002/02/21  C     3       11
2002/02/23  C     3       22
2003/10/10  C     3       33


day :処理の日付
code:商品コード
flg :1の場合(販売)、2の場合(発注)、3の場合(製造)
数量:上記flgが、1の場合は、販売数量
      上記flgが、2の場合は、発注数量
      上記flgが、3の場合は、製造数量

なので月ごとの在庫数は以下の値になります
2002/12  A    6
2003/01  A    5
2003/02  A   20
2002/12  B   14
2003/01  B    6
2003/02  B   50

以上をふまえて、在庫数の推移を年別、コード別に出力する

出力結果
code  year  1月  2月  3月   4月  5月  6月  7月  8月  9月  10月  11月  12月
----  ----  ---  ---  ---  ---  ---  ---  ---  ---  ---  ----  ----  ----
A     2002    0    0    0    0    0    0    0    0    0     0     0     6
A     2003   11   21   21   21   21   21   21   21   21    21    21    21
B     2002    0    0    0    0    0    0    0    0    0     0     0    14
B     2003   20   70   70   70   70   70   70   70   70    70    70    70
C     2002    0   33   33   33   33   33   33   33   33    33    33    33
C     2003   33   33   33   33   33   33   33   33   33    66    66    66


データ作成スクリプト

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;


SQL

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);


解説

withを使う方法では、累計を取得して、外部結合してます。