はじめに
本稿では、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のイメージは下記です。
ちなみに、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;
