トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
8-14 Seqが1小さいデータも出力
SQLパズル
AmountTable
Code seq amount
---- --- ------
10 1 100
10 2 200
10 2 400
10 5 800
20 4 1600
20 5 3200
20 6 6400
20 8 300
20 8 600
30 4 1200
30 5 2400
30 6 4800
30 7 9600
30 8 8500
CodeとSeqの組み合わせごとのamountの合計を求め、
Codeが同じでSeqが1小さいレコードのamountの合計(なければ0)も出力する。
出力結果
Code seq amountSUM preAmountSUM
---- --- --------- ------------
10 1 100 0
10 2 600 100
10 5 800 0
20 4 1600 0
20 5 3200 1600
20 6 6400 3200
20 8 900 0
30 4 1200 0
30 5 2400 1200
30 6 4800 2400
30 7 9600 4800
30 8 8500 9600
データ作成スクリプト
create table AmountTable as
select 10 as Code,1 as seq,100 as amount from dual
union select 10,2, 200 from dual
union select 10,2, 400 from dual
union select 10,5, 800 from dual
union select 20,4,1600 from dual
union select 20,5,3200 from dual
union select 20,6,6400 from dual
union select 20,8, 300 from dual
union select 20,8, 600 from dual
union select 30,4,1200 from dual
union select 30,5,2400 from dual
union select 30,6,4800 from dual
union select 30,7,9600 from dual
union select 30,8,8500 from dual;
SQL
--■■■相関サブクエリを使う方法■■■
select Code,seq,
sum(amount) as amountSum,
(select nvl(sum(b.amount),0)
from AmountTable b
where b.Code = a.Code
and b.seq = a.seq-1) as preAmountSum
from AmountTable a
group by Code,seq
order by Code,seq;
--■■■分析関数を使う方法■■■
select Code,seq,
sum(amount) as amountSum,
nvl(max(sum(amount)) over(partition by Code order by seq
Range between 1 preceding
and 1 preceding),0) as preAmountSum
from AmountTable
group by Code,seq
order by Code,seq;
解説
Lag関数は、 Rows betweenとprecedingで模倣できます。
Lead関数は、Rows betweenとfollowingで模倣できます。
8-13 Lag関数とLead関数を模倣
分析関数を使う方法では、このことを応用(RowsをRangeに変更)してます。
CodeZine:分析関数の衝撃(後編)