はじめに
本稿では、Oracleの分析関数の中で、Lag
関数とLead
関数と同じ結果を取得するSQLを扱います。
対象読者
- MySQLやPostgreSQLで、分析関数と同じ結果を取得するSQLを書きたい方
- OracleやDB2やSQLServerの、分析関数の理解を深めたい方
本稿では、相関サブクエリを多用しますので、『相関サブクエリで行と行を比較する』を先に読んでおくと理解が進むと思います。
必要な環境
本稿で扱うSQLは、MySQL 5.1.28で動作確認しました。ソースコードはPostgreSQL 8.3とOracle 10.2.0.1.0でも動作確認しました。その他、次の環境でも応用が可能です。
- SQLServer
- DB2
1. IDごとのSeqの昇順での1行前と1行後の行のValを求める
最初は、IDごとのSeqの昇順で、1行前と1行後の行のValを求めるSQLについてです。まずは、テーブルのデータと出力結果を考えます。
ID | Seq | Val |
AA | 1 | 100 |
AA | 2 | 100 |
AA | 3 | 500 |
AA | 4 | 200 |
AA | 5 | 200 |
AA | 6 | 50 |
BB | 1 | 200 |
BB | 2 | 400 |
BB | 3 | 800 |
BB | 4 | 900 |
CC | 1 | 100 |
CC | 2 | 800 |
CC | 3 | 700 |
DD | 1 | 400 |
EE | 1 | 50 |
FF | 1 | 10 |
FF | 3 | 20 |
FF | 5 | 40 |
FF | 6 | 80 |
IDごとのSeqの昇順での1行前と1行後の行のValを求めます。1行前の行がなければ、その値はnull
とします。同様に、1行後の行がなければ、その値はnull
とします。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。
select ID,Seq,Val, Lag(Val) over(partition by ID order by Seq) as LagVal, Lead(Val) over(partition by ID order by Seq) as LeadVal from IDTable order by ID,Seq;
ID | Seq | Val | LagVal | LeadVal |
AA | 1 | 100 | null | 100 |
AA | 2 | 100 | 100 | 500 |
AA | 3 | 500 | 100 | 200 |
AA | 4 | 200 | 500 | 200 |
AA | 5 | 200 | 200 | 50 |
AA | 6 | 50 | 200 | null |
BB | 1 | 200 | null | 400 |
BB | 2 | 400 | 200 | 800 |
BB | 3 | 800 | 400 | 900 |
BB | 4 | 900 | 800 | null |
CC | 1 | 100 | null | 800 |
CC | 2 | 800 | 100 | 700 |
CC | 3 | 700 | 800 | null |
DD | 1 | 400 | null | null |
EE | 1 | 50 | null | null |
FF | 1 | 10 | null | 20 |
FF | 3 | 20 | 10 | 40 |
FF | 5 | 40 | 20 | 80 |
FF | 6 | 80 | 40 | null |
Lag(Val) over(partition by ID order by Seq)
について考えてみると、Seqの昇順でソートした時の1行前というのは、ソートキーであるSeqの値が対象行より小さい中で最大のSeqの行(最大下界)だと考えることができます。
Lead(Val) over(partition by ID order by Seq)
について考えてみると、Seqの昇順でソートした時の1行後というのは、ソートキーであるSeqの値が対象行より大きい中で最小のSeqの行(最小上界)だと考えることができます。
以上をふまえて、答えは下記となります。
select ID,Seq,Val, (select b.Val from IDTable b where b.ID=a.ID and b.Seq < a.Seq order by b.Seq desc Limit 1) as LagVal (select b.Val from IDTable b where b.ID=a.ID and b.Seq > a.Seq order by b.Seq Limit 1) as LeadVal from IDTable a order by ID,Seq;
LagValを求める相関サブクエリでは、where
句で自分よりSeqが小さいことを条件として、order by
句でSeqの降順にソートして、Limit
句で行数を1行のみと指定してます。
LeadValを求める相関サブクエリでは、where
句で自分よりSeqが大きいことを条件として、order by
句でSeqの昇順にソートして、Limit
句で行数を1行のみと指定してます。
SQLのイメージは下記です。
![SQLのイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/3105/fig01a.gif)
ちなみに、Limit
句を使わないSQLは、下記となります。
select ID,Seq,Val, (select b.Val from IDTable b where b.ID=a.ID and exists(select 1 from IDTable c where c.ID=a.ID and c.Seq between b.Seq and a.Seq having count(*) = 1+1)) as LagVal, (select b.Val from IDTable b where b.ID=a.ID and exists(select 1 from IDTable c where c.ID=a.ID and c.Seq between a.Seq and b.Seq having count(*) = 1+1)) as LeadVal from IDTable a order by ID,Seq;
from
句でのネストされたインラインビューなどはネストの深い方から読みますが、上記のようなselect
句でのネストされた相関サブクエリはネストの浅い方から読むと分かりやすいです。
select
句での相関サブクエリの中で、exists
述語を使用し、where
句で同じIDを指定し、between
述語でSeqの大小条件を指定して、having
句でその行の数を指定しています。
LagValでは、条件式
Valを取得する行のソートキー <= ソートキー <= 自分の行のソートキー
を満たす行数が2であることをhaving
句で指定しています。
LeadValでは、条件式
自分の行のソートキー <= ソートキー <= Valを取得する行のソートキー
を満たす行数が2であることをhaving
句で指定しています。
上記のSQLは、having
句での条件指定が単純な等価条件で、かつ、group by
句がないので、下記に書き換え可能です。
select ID,Seq,Val, (select b.Val from IDTable b where b.ID=a.ID and (select count(*) from IDTable c where c.ID=a.ID and c.Seq between b.Seq and a.Seq) = 1+1) as LagVal, (select b.Val from IDTable b where b.ID=a.ID and (select count(*) from IDTable c where c.ID=a.ID and c.Seq between a.Seq and b.Seq) = 1+1) as LeadVal from IDTable a order by ID,Seq;