トップページに戻る    次の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:分析関数の衝撃(後編)