4. 同じIDでSeqの昇順で最初にValが100か200になる行のValを求める
最後は、同じIDでSeqの昇順で最初にValが100か200になる行の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の昇順で最初にValが100か200になる行のValを求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。
select ID,Seq,Val, First_Value(case when Val in(100,200) then Val end ignore nulls) over(partition by ID order by Seq Rows between Current Row and Unbounded Following) as FirstVal from IDTable order by ID,Seq;
ID | Seq | Val | FirstVal |
AA | 1 | 100 | 100 |
AA | 2 | 100 | 100 |
AA | 3 | 500 | 200 |
AA | 4 | 200 | 200 |
AA | 5 | 200 | 200 |
AA | 6 | 50 | null |
BB | 1 | 200 | 200 |
BB | 2 | 400 | null |
BB | 3 | 800 | null |
BB | 4 | 900 | null |
CC | 1 | 100 | 100 |
CC | 2 | 800 | null |
CC | 3 | 700 | null |
DD | 1 | 400 | null |
EE | 1 | 50 | null |
FF | 1 | 10 | null |
FF | 3 | 20 | null |
FF | 5 | 40 | null |
FF | 6 | 80 | null |
前問と同じ考え方を使って、答えは、下記となります。
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 and c.Val in(100,200) and c.Seq >= a.Seq)) as FirstVal from IDTable a order by ID,Seq;
解説すると、最初に下記によって、同じIDで、Seqが自分以上で、Valが100か200の行の中での最小のSeqを求めています。
select min(c.Seq) from IDTable c where c.ID=a.ID and c.Val in(100,200) and c.Seq >= a.Seq
続いて下記により、同じIDでSeqの昇順で最初にValが100か200になる行の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 and c.Val in(100,200) and c.Seq >= a.Seq)
下記のLimit
句を使った別解もあり、こっちのほうがシンプルでしょう。
select ID,Seq,Val, (select b.Val from IDTable b where b.ID=a.ID and b.Val in(100,200) and b.Seq >= a.Seq order by b.Seq Limit 1) as FirstVal from IDTable a order by ID,Seq;
SQLのイメージは下記となります。
最後に
今回は、First_Value
関数やLast_Value
関数と同じ結果を取得するSQLを扱いました。次回の後編では、Lag
関数およびLead
関数と同じ結果を取得するSQLを扱う予定です。
参考資料
- ONLamp.com 『Emulating Analytic (AKA Ranking) Functions with MySQL』 Stephane Faroultt著、2007年3月
本連載の原案となった記事です。 - ONLamp.com 『Emulating Analytic (AKA Ranking) Functions with MySQL: Part 2』 Stephane Faroult著、2007年4月
本連載の原案となった記事です。