8-16 2ヶ月前のデータも出力

SQLパズル

AmountTable2
Code  yyyymm  amount
----  ------  ------
AAAA  200701     100
AAAA  200702     200
AAAA  200704     400
AAAA  200711     500
AAAA  200712     600
AAAA  200801     700
AAAA  200802     800
AAAA  200810     900
BBBB  200705     111
BBBB  200707     222
BBBB  200712     333
BBBB  200801     444
BBBB  200802     555

同じCodeで、
yyyymmddが2ヶ月前のamount(なければ0)を出力する。

AmountTable2のプライマリキーは、Codeとyyyymmとする。

出力結果
Code  yyyymm  amount  amount2monthsBefore
----  ------  ------  -------------------
AAAA  200701     100                    0
AAAA  200702     200                    0
AAAA  200704     400                  200
AAAA  200711     500                    0
AAAA  200712     600                    0
AAAA  200801     700                  500
AAAA  200802     800                  600
AAAA  200810     900                    0
BBBB  200705     111                    0
BBBB  200707     222                  111
BBBB  200712     333                    0
BBBB  200801     444                    0
BBBB  200802     555                  333

データ作成スクリプト

create table amountTable2(
Code   char(4),
yyyymm char(6),
amount number(3),
primary key(Code,yyyymm));

insert into amountTable2 values('AAAA','200701',100);
insert into amountTable2 values('AAAA','200702',200);
insert into amountTable2 values('AAAA','200704',400);
insert into amountTable2 values('AAAA','200711',500);
insert into amountTable2 values('AAAA','200712',600);
insert into amountTable2 values('AAAA','200801',700);
insert into amountTable2 values('AAAA','200802',800);
insert into amountTable2 values('AAAA','200810',900);
insert into amountTable2 values('BBBB','200705',111);
insert into amountTable2 values('BBBB','200707',222);
insert into amountTable2 values('BBBB','200712',333);
insert into amountTable2 values('BBBB','200801',444);
insert into amountTable2 values('BBBB','200802',555);
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select Code,yyyymm,amount,
nvl((select b.amount
       from AmountTable2 b
      where b.Code = a.Code
        and b.yyyymm = case substr(a.yyyymm,-2)
                       when '01' then to_char(to_number(a.yyyymm) - 100 -1+11)
                       when '02' then to_char(to_number(a.yyyymm) - 100 -2+12)
                       else to_char(to_number(a.yyyymm)-2)
                       end),0) as "amount2monthsBefore"
from AmountTable2 a;

--■■■分析関数を使う方法■■■
select Code,yyyymm,amount,
nvl(max(amount) over(partition by Code
                     order by to_number(substr(yyyymm,1,4))*12+to_number(substr(yyyymm,-2))
                     Range between 2 Preceding and 2 Preceding),0) as "amount2monthsBefore"
from amountTable2;

--■■■interval型を使う方法■■■
select Code,yyyymm,amount,
nvl(max(amount)
    over(partition by Code
    order by to_date(yyyymm,'yyyymm')
    range between interval '2' month preceding
              and interval '2' month preceding),0) as "amount2monthsBefore"
  from AmountTable2;

解説

分析関数を使う方法では、
yyyymmを12進数に変換して、order by句で使ってます。

12進数変換の例
200710 → 2007*12+10
200711 → 2007*12+11
200712 → 2007*12+12
200801 → 2008*12+ 1=2007*12+13
200802 → 2008*12+ 2=2007*12+14

ちなみに、計算式の結果は、下記となります。
2007*12+10 = 24094
2007*12+11 = 24095
2007*12+12 = 24096
2008*12+ 1 = 24097
2008*12+ 2 = 24098

---