トップページに戻る
次の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)(英語)