MySQLで分析関数を模倣2（中編）

MySQLで、Oracleの分析関数と同じ結果を取得する2

3. 同じIDでSeqの昇順で最後にValが100か200だった行のValを求める

　次は、同じIDでSeqの昇順で最後にValが100か200だった行の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が100か200だった行のValを求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

select ID,Seq,Val,
Last_Value(case when Val in(100,200) then Val end ignore nulls)
over(partition by ID order by Seq) as LastVal
  from IDTable
order by ID,Seq;

出力結果

ID	Seq	Val	LastVal
AA	1	100	100
AA	2	100	100
AA	3	500	100
AA	4	200	200
AA	5	200	200
AA	6	50	200
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	null
EE	1	50	null
FF	1	10	null
FF	3	20	null
FF	5	40	null
FF	6	80	null

　基本的に、前問および前々問と同じ考え方を使いますが、ignore nullsで、Valが100か200ではない行は無視していることをふまえて、答えは、下記となります。

相関サブクエリを使う方法

select ID,Seq,Val,
(select b.Val
   from IDTable b
  where b.ID=a.ID
    and b.Seq = (select max(c.Seq)
                   from IDTable c
                  where c.ID=a.ID
                    and c.Val in(100,200)
                    and c.Seq <= a.Seq)) as LastVal
  from IDTable a
order by ID,Seq;

　解説すると、最初に下記によって、同じIDで、Seqが自分以下で、Valが100か200の行の中での最大のSeqを求めています。

同じIDで、Seqが自分以下で、Valが100か200の行の中での最大のSeqを求める

select max(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を取得しています。

同じIDでSeqの昇順で最後にValが100か200だった行のValを取得

select b.Val
  from IDTable b
 where b.ID=a.ID
   and b.Seq = (select max(c.Seq)
                  from IDTable c
                 where c.ID=a.ID
                   and c.Val in(100,200)
                   and c.Seq <= a.Seq)

　下記のLimit句を使った別解もあり、こっちのほうがシンプルでしょう。

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 desc Limit 1) as LastVal
  from IDTable a
order by ID,Seq;

　SQLのイメージは下記です。

SQLのイメージ