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

はじめに

　本稿では、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についてです。まずは、テーブルのデータと、出力結果を考えます。

　IDごとのSeqが最小の行のValを求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

select ID,Seq,Val,
First_Value(Val) over(partition by ID order by Seq) as FirstVal
  from IDTable
order by ID,Seq;

　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を求めています。

select min(c.Seq)
  from IDTable c
 where c.ID=a.ID

　続いて下記によって、同じ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述語を使って、最小値の行は、自分よりキーの大きい行が存在しない行、と考える必要があります。

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のイメージ