8-1 OracleのLead関数とLag関数を模倣

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

Codeごとの、SortKeyの昇順で、
3つ前の行の、Valの値(なければ0)と、
2つ前の行の、Valの値(なければ0)と、
1つ前の行の、Valの値(なければ0)と、
1つ後の行の、Valの値(なければ0)と、
2つ後の行の、Valの値(なければ0)と、
3つ後の行の、Valの値(なければ0)を
出力する。

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

データ作成スクリプト

create table LagLeadTable(
Code    char(4),
SortKey integer,
Val     integer);

insert into LagLeadTable values
('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);
commit;

SQL

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

解説

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

Lag関数とLead関数は、DB2 V9.5から使用可能になりました。

---