8-13 Lag関数とLead関数を模倣

SQLパズル

LagLeadTable
Code  SortKey  Val
----  -------  ---
AAAA      100    5
AAAA      200   20
AAAA      300   15
AAAA      400   30
BBBB      500   60
BBBB      600   70
BBBB      610   90
BBBB      620   50
BBBB      630   70
CCCC      100   80

select Code,SortKey,
Lag(Val,3,0)  over(partition by Code order by SortKey) as Lag3,
Lag(Val,2,0)  over(partition by Code order by SortKey) as Lag2,
Lag(Val,1,0)  over(partition by Code order by SortKey) as Lag1,
Val,
Lead(Val,1,0) over(partition by Code order by SortKey) as Lead1,
Lead(Val,2,0) over(partition by Code order by SortKey) as Lead2,
Lead(Val,3,0) over(partition by Code order by SortKey) as Lead3
  from LagLeadTable;
を模倣する。

出力結果
Code  SortKey  Lag3  Lag2  Lag1  Val  Lead1  Lead2  Lead3
----  -------  ----  ----  ----  ---  -----  -----  -----
AAAA      100     0     0     0    5     20     15     30
AAAA      200     0     0     5   20     15     30      0
AAAA      300     0     5    20   15     30      0      0
AAAA      400     5    20    15   30      0      0      0
BBBB      500     0     0     0   60     70     90     50
BBBB      600     0     0    60   70     90     50     70
BBBB      610     0    60    70   90     50     70      0
BBBB      620    60    70    90   50     70      0      0
BBBB      630    70    90    50   70      0      0      0
CCCC      100     0     0     0   80      0      0      0

データ作成スクリプト

create table LagLeadTable as
select 'AAAA' as Code,100 as SortKey,5 as Val from dual
union select 'AAAA',200,20 from dual
union select 'AAAA',300,15 from dual
union select 'AAAA',400,30 from dual
union select 'BBBB',500,60 from dual
union select 'BBBB',600,70 from dual
union select 'BBBB',610,90 from dual
union select 'BBBB',620,50 from dual
union select 'BBBB',630,70 from dual
union select 'CCCC',100,80 from dual;

SQL

select Code,sortkey,
nvl(max(Val) over(partition by Code order by SortKey
                  Rows between 3 preceding and 3 preceding),0) as Lag3,
nvl(max(Val) over(partition by Code order by SortKey
                  Rows between 2 preceding and 2 preceding),0) as Lag2,
nvl(max(Val) over(partition by Code order by SortKey
                  Rows between 1 preceding and 1 preceding),0) as Lag1,
Val,
nvl(max(Val) over(partition by Code order by SortKey
                  Rows between 1 following and 1 following),0) as Lead1,
nvl(max(Val) over(partition by Code order by SortKey
                  Rows between 2 following and 2 following),0) as Lead2,
nvl(max(Val) over(partition by Code order by SortKey
                  Rows between 3 following and 3 following),0) as Lead3
  from LagLeadTable;

解説

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

Oracleでは、模倣する必要は、ないと思いますが、
DB2で、役に立つかもしれません。

---