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

8-15 2日前のデータも出力

SQLパズル

AmountTable
Code  yyyymmdd    amount
----  ----------  ------
AAAA  2007/01/01     100
AAAA  2007/01/05     200
AAAA  2007/01/06     400
AAAA  2007/01/07     500
AAAA  2007/01/09     600
AAAA  2007/01/11     700
AAAA  2007/01/12     800
AAAA  2007/01/14     900
BBBB  2007/12/01     111
BBBB  2007/12/05     222
BBBB  2007/12/06     333
BBBB  2007/12/07     444

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

出力結果
Code  yyyymmdd    amount  amount2daysBefore
----  ----------  ------  -----------------
AAAA  2007/01/01     100                  0
AAAA  2007/01/05     200                  0
AAAA  2007/01/06     400                  0
AAAA  2007/01/07     500                200
AAAA  2007/01/09     600                500
AAAA  2007/01/11     700                600
AAAA  2007/01/12     800                  0
AAAA  2007/01/14     900                800
BBBB  2007/12/01     111                  0
BBBB  2007/12/05     222                  0
BBBB  2007/12/06     333                  0
BBBB  2007/12/07     444                222


データ作成スクリプト

create table amountTable as
select 'AAAA' as Code,to_date('2007/01/01','yyyy/mm/dd') as yyyymmdd,100 as amount from dual
union select 'AAAA',to_date('2007/01/05','yyyy/mm/dd'),200 from dual
union select 'AAAA',to_date('2007/01/06','yyyy/mm/dd'),400 from dual
union select 'AAAA',to_date('2007/01/07','yyyy/mm/dd'),500 from dual
union select 'AAAA',to_date('2007/01/09','yyyy/mm/dd'),600 from dual
union select 'AAAA',to_date('2007/01/11','yyyy/mm/dd'),700 from dual
union select 'AAAA',to_date('2007/01/12','yyyy/mm/dd'),800 from dual
union select 'AAAA',to_date('2007/01/14','yyyy/mm/dd'),900 from dual
union select 'BBBB',to_date('2007/12/01','yyyy/mm/dd'),111 from dual
union select 'BBBB',to_date('2007/12/05','yyyy/mm/dd'),222 from dual
union select 'BBBB',to_date('2007/12/06','yyyy/mm/dd'),333 from dual
union select 'BBBB',to_date('2007/12/07','yyyy/mm/dd'),444 from dual;


SQL

col yyyymmdd for a10

--■■■相関サブクエリを使う方法■■■
select Code,to_char(yyyymmdd,'yyyy/mm/dd') as yyyymmdd,
amount,
nvl((select b.amount
       from AmountTable b
      where b.yyyymmdd = a.yyyymmdd - 2),0) as "amount2daysBefore"
from AmountTable a;

--■■■分析関数を使う方法■■■
select Code,to_char(yyyymmdd,'yyyy/mm/dd') as yyyymmdd,
amount,
nvl(max(amount) over(partition by Code order by yyyymmdd
                     Range between 2 Preceding and 2 Preceding),0) as "amount2daysBefore"
from amountTable;


解説

Lag関数は、 Rows betweenとprecedingで模倣できます。
Lead関数は、Rows betweenとfollowingで模倣できます。

分析関数を使う方法では、このことを応用(RowsをRangeに変更)してます。

Rangeの範囲指定は、date型をorder by句に指定し、
年の差
月の差
日の差
時間の差
分の差
秒の差
でも使えます。

SQL> select key,Val,
  2  max(Val) over(order by key
  3                range between interval '1' month Preceding
  4                          and interval '1' month Preceding) as PreVal
  5  from (select to_date('2007/10/10','yyyy/mm/dd') as key,100 as Val from dual
  6  union select to_date('2007/11/10','yyyy/mm/dd'),200 from dual
  7  union select to_date('2007/11/11','yyyy/mm/dd'),300 from dual
  8  union select to_date('2007/12/10','yyyy/mm/dd'),400 from dual);

key         Val  PreVal
----------  ---  ------
2007/10/10  100    null
2007/11/10  200     100
2007/11/11  300    null
2007/12/10  400     200

9-34 過去1時間の平均
CodeZine:分析関数の衝撃(後編)

マニュアル(期間リテラル)
マニュアル(Interval Literals)(英語)