8-19 12進数変換と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        0
2007/11/11  320     1120        0      150     1000        0
2007/11/12  640     1120        0      150     1000        0
2007/12/12  100     1000      150     1120        0     2400
2007/12/15  900     1000      150     1120        0     2400
2008/02/01  300     2400     1000        0      900      220
2008/02/03  400     2400     1000        0      900      220
2008/02/19  800     2400     1000        0      900      220
2008/02/28  900     2400     1000        0      900      220
2008/03/10  100      900        0     2400      220      800
2008/03/15  300      900        0     2400      220      800
2008/03/20  500      900        0     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

データ作成スクリプト

前のSQLパズルと同じ

SQL

--■■■12進数変換を使う方法■■■
select Day1,Val,SumVal,
nvl(max(SumVal) over(order by yyyymm
                     range between 2 preceding
                               and 2 preceding),0) as "2ヵ月前",
nvl(max(SumVal) over(order by yyyymm
                     range between 1 preceding
                               and 1 preceding),0) as "1ヵ月前",
nvl(max(SumVal) over(order by yyyymm
                     range between 1 following
                               and 1 following),0) as "1ヵ月後",
nvl(max(SumVal) over(order by yyyymm
                     range between 2 following
                               and 2 following),0) as "2ヵ月後"
from (select Day1,Val,
      sum(Val) over(partition by trunc(Day1,'mm')) as SumVal,
      extract(year  from Day1)*12
     +extract(month from Day1) as yyyymm
        from ValTable)
order by Day1;

--■■■interval型を使う方法■■■
select day1,Val,
sum(Val) over(partition by trunc(Day1,'mm')) as SumVal,
nvl(sum(Val) over(order by trunc(Day1,'mm')
                  range between interval '2' month preceding
                            and interval '2' month preceding),0) as "2ヵ月前",
nvl(sum(Val) over(order by trunc(Day1,'mm')
                  range between interval '1' month preceding
                            and interval '1' month preceding),0) as "1ヵ月前",
nvl(sum(Val) over(order by trunc(Day1,'mm')
                  range between interval '1' month following
                            and interval '1' month following),0) as "1ヵ月後",
nvl(sum(Val) over(order by trunc(Day1,'mm')
                  range between interval '2' month following
                            and interval '2' month following),0) as "2ヵ月後"
  from ValTable
order by Day1;

解説

12進数変換とrangeの組み合わせて、
値がnだけ前の値および値がnだけ後の値
にアクセスしてます。

12進数変換の例
2007/10 → 2007*12+10
2007/11 → 2007*12+11
2007/12 → 2007*12+12
2008/01 → 2008*12+ 1=2007*12+13
2008/02 → 2008*12+ 2=2007*12+14

interval型を使う方法がオススメですが、
12進数に変換しても悪くはないでしょう。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
年を数値で取得したい場合は、
to_number(to_char(date型,'yyyy')) よりも extract(year  from date型)
月を数値で取得したい場合は、
to_number(to_char(date型,'mm'))   よりも extract(month from date型)
日を数値で取得したい場合は、
to_number(to_char(date型,'dd'))   よりも extract(day from date型)

としたほうが、シンプルでしょうねぇ
extract関数は、数値型を返すからです。

---