8-18 dense_Rankとrangeの組み合わせ

SQLパズル

ValTable
day1        Val
----------  ---
2007/10/10   10
2007/10/11   20
2007/10/12   40
2007/10/30   80
2007/11/10  160
2007/11/11  320
2007/11/12  640
2007/12/12  100
2007/12/15  900
2008/02/01  300
2008/02/03  400
2008/02/19  800
2008/02/28  900
2008/03/10  100
2008/03/15  300
2008/03/20  500
2008/04/20   20
2008/04/21  200
2008/05/20  800

yyyymmごとのValの合計と、
yyyymmが2ヵ月前のValの合計(なければ0)
yyyymmが1ヵ月前のValの合計(なければ0)
yyyymmが1ヵ月後のValの合計(なければ0)
yyyymmが2ヵ月後のValの合計(なければ0)
を求める。
ただし、yyyymmの歯抜けは考慮しない。

出力結果
day1        Val   SumVal  2ヵ月前  1ヵ月前   1ヵ月後  2ヵ月後
----------  ---   ------  -------  -------  -------  -------
2007/10/10   10      150        0        0     1120     1000
2007/10/11   20      150        0        0     1120     1000
2007/10/12   40      150        0        0     1120     1000
2007/10/30   80      150        0        0     1120     1000
2007/11/10  160     1120        0      150     1000     2400
2007/11/11  320     1120        0      150     1000     2400
2007/11/12  640     1120        0      150     1000     2400
2007/12/12  100     1000      150     1120     2400      900
2007/12/15  900     1000      150     1120     2400      900
2008/02/01  300     2400     1120     1000      900      220
2008/02/03  400     2400     1120     1000      900      220
2008/02/19  800     2400     1120     1000      900      220
2008/02/28  900     2400     1120     1000      900      220
2008/03/10  100      900     1000     2400      220      800
2008/03/15  300      900     1000     2400      220      800
2008/03/20  500      900     1000     2400      220      800
2008/04/20   20      220     2400      900      800        0
2008/04/21  200      220     2400      900      800        0
2008/05/20  800      800      900      220        0        0

データ作成スクリプト

create table ValTable as
select to_date('2007/10/10','yyyy/mm/dd') as day1,10 as Val from dual
union select to_date('2007/10/11','yyyy/mm/dd'), 20 from dual
union select to_date('2007/10/12','yyyy/mm/dd'), 40 from dual
union select to_date('2007/10/30','yyyy/mm/dd'), 80 from dual
union select to_date('2007/11/10','yyyy/mm/dd'),160 from dual
union select to_date('2007/11/11','yyyy/mm/dd'),320 from dual
union select to_date('2007/11/12','yyyy/mm/dd'),640 from dual
union select to_date('2007/12/12','yyyy/mm/dd'),100 from dual
union select to_date('2007/12/15','yyyy/mm/dd'),900 from dual
union select to_date('2008/02/01','yyyy/mm/dd'),300 from dual
union select to_date('2008/02/03','yyyy/mm/dd'),400 from dual
union select to_date('2008/02/19','yyyy/mm/dd'),800 from dual
union select to_date('2008/02/28','yyyy/mm/dd'),900 from dual
union select to_date('2008/03/10','yyyy/mm/dd'),100 from dual
union select to_date('2008/03/15','yyyy/mm/dd'),300 from dual
union select to_date('2008/03/20','yyyy/mm/dd'),500 from dual
union select to_date('2008/04/20','yyyy/mm/dd'), 20 from dual
union select to_date('2008/04/21','yyyy/mm/dd'),200 from dual
union select to_date('2008/05/20','yyyy/mm/dd'),800 from dual;

SQL

select Day1,Val,SumVal,
nvl(max(SumVal) over(order by Rank
                     range between 2 preceding
                               and 2 preceding),0) as "2ヵ月前",
nvl(max(SumVal) over(order by Rank
                     range between 1 preceding
                               and 1 preceding),0) as "1ヵ月前",
nvl(max(SumVal) over(order by Rank
                     range between 1 following
                               and 1 following),0) as "1ヵ月後",
nvl(max(SumVal) over(order by Rank
                     range between 2 following
                               and 2 following),0) as "2ヵ月後"
from (select Day1,Val,
      sum(Val) over(partition by trunc(Day1,'mm')) as SumVal,
      dense_Rank() over(order by trunc(Day1,'mm')) as Rank
        from ValTable)
order by Day1;

解説

dense_Rank関数とrangeを組み合わせると、
同じ値を無視しての、n行前の値およびn行後の値
にアクセスできます。
なぜなら同じ値ならdense_Rank関数の値は等しくなるからです。

---