はじめに
本稿では、Oracleの分析関数のFirst_Value関数およびLast_Value関数と、同じ結果を取得する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が最小の行のValを求める
最初は、IDごとのSeqが最小の行のValを求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。
IDTable
| 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が最小の行のValを求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。
分析関数を使ったSQL
select ID,Seq,Val, First_Value(Val) over(partition by ID order by Seq) as FirstVal from IDTable order by ID,Seq;
出力結果
| ID | Seq | Val | FirstVal |
| AA | 1 | 100 | 100 |
| AA | 2 | 100 | 100 |
| AA | 3 | 500 | 100 |
| AA | 4 | 200 | 100 |
| AA | 5 | 200 | 100 |
| AA | 6 | 50 | 100 |
| BB | 1 | 200 | 200 |
| BB | 2 | 400 | 200 |
| BB | 3 | 800 | 200 |
| BB | 4 | 900 | 200 |
| CC | 1 | 100 | 100 |
| CC | 2 | 800 | 100 |
| CC | 3 | 700 | 100 |
| DD | 1 | 400 | 400 |
| EE | 1 | 50 | 50 |
| FF | 1 | 10 | 10 |
| FF | 3 | 20 | 10 |
| FF | 5 | 40 | 10 |
| FF | 6 | 80 | 10 |
partition by IDという指定をして、同じIDの中でSeqが最小の行のValを求めていますので、答えは相関サブクエリを使った、下記となります。
相関サブクエリを使う方法
select ID,Seq,Val,
(select b.Val
from IDTable b
where b.ID=a.ID
and b.Seq = (select min(c.Seq)
from IDTable c
where c.ID=a.ID)) as FirstVal
from IDTable a
order by ID,Seq;
サブクエリのネストのあるSQLは、ネストの深いほうから先に読んでいくと理解しやすいと思います。最初に下記によって、同じIDの中で、最小のSeqを求めています。
同じIDの中で、最小のSeqを求める
select min(c.Seq) from IDTable c where c.ID=a.ID
続いて下記によって、同じIDでSeqが最小の行のValを取得しています。
同じIDでSeqが最小の行のValを取得
select b.Val
from IDTable b
where b.ID=a.ID
and b.Seq = (select min(c.Seq)
from IDTable c
where c.ID=a.ID)
下記のLimit句を使った別解もあり、下記のほうがシンプルでしょうし、 ソートキーがSeqのみでない（複数ソートキー）場合に、order by句にソートキーを追加するだけで容易に対応できます。
ちなみに、複数ソートキーの場合にLimit句を使わないSQLはnot exists述語を使って、最小値の行は、自分よりキーの大きい行が存在しない行、と考える必要があります。
Limit句を使った別解
select ID,Seq,Val, (select b.Val from IDTable b where b.ID=a.ID order by b.Seq Limit 1) as FirstVal from IDTable a order by ID,Seq;
SQLのイメージは下記です。
SQLのイメージ
