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

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

2. 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,
Last_Value(Val) over(partition by ID
order by Seq Rows between Unbounded Preceding
                      and Unbounded Following) as LastVal
  from IDTable
order by ID,Seq;

出力結果

ID	Seq	Val	LastVal
AA	1	100	50
AA	2	100	50
AA	3	500	50
AA	4	200	50
AA	5	200	50
AA	6	50	50
BB	1	200	900
BB	2	400	900
BB	3	800	900
BB	4	900	900
CC	1	100	700
CC	2	800	700
CC	3	700	700
DD	1	400	400
EE	1	50	50
FF	1	10	80
FF	3	20	80
FF	5	40	80
FF	6	80	80

　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 max(c.Seq)
                   from IDTable c
                  where c.ID=a.ID)) as LastVal
  from IDTable a
order by ID,Seq;

　解説すると、最初に下記によって、同じIDの中で最大のSeqを求めています。

同じIDの中で、最大のSeqを求める

select max(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 max(c.Seq)
                  from IDTable c
                 where c.ID=a.ID)

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

Limit句を使った別解

select ID,Seq,Val,
(select b.Val
   from IDTable b
  where b.ID=a.ID
 order by b.Seq desc Limit 1) as LastVal
  from IDTable a
order by ID,Seq;

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

SQLのイメージ