グループで最大のレコードを取得するSQL

同一グループの中で最大のレコードを取得するSQLはたまに必要になります。 このような時はGROUP BYを使ってグループをまとめながら最大値を求めてその結果を…までは良いのですが、そこから2種類の方法があるので書いておきます。 key,value, year, month, timeを持つテーブルからkey=xに関してyearごとにmax(time)を持つvalueを求める場合は以下のようになります。

1. INNER JOINを使う

explain query plan
select distinct(s.year),value from [table] as s inner join (
  select year,max(time) as max_time from [table] where key=[x] group by year)
  as m on s.year = m.year and s.time = m.max_time and key=[x];

1|0|0|SEARCH TABLE [table] USING COVERING INDEX [table_idx] (key=?)
0|0|0|SEARCH TABLE [table] AS s USING INDEX [table_idx] (key=?)
0|1|1|SCAN SUBQUERY 1 AS m

explainで実行計画も載せてみましたが、まずsの条件で検索をしてから、mの検索結果とjoinして表示しているようです。

2. サブクエリを条件式に使う

explain query plan
select distinct(year),value from [table] as s where key=[x] and time=(
  select max(time) from [table] as m where s.year = m.year and key=[x]);

0|0|0|SEARCH TABLE [table] AS s USING INDEX [table_idx] (key=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE [table] AS m USING COVERING INDEX [table_idx] (key=?)
0|0|0|USE TEMP B-TREE FOR DISTINCT

こちらではsの条件で検索してから、サブクエリとしてmを発行し、mの検索結果を一時的に構築したB-Treeを使いながらdistinctしてるみたいです。

explainの結果を見る限りでは1のほうが早そうです。 参考になるかはわかりませんが、実際に運用しているスクリプトの実行時間は1=25分、2=33分でした。 EXISTSを使う方法もあるみたいですね。