MySQLで分析関数を模倣4（完結編）

はじめに

　本稿では、Oracleの分析関数の中で、Range指定およびRows指定のSum関数と同じ結果を取得するSQLを扱います。

対象読者

• MySQLやPostgreSQLで、分析関数と同じ結果を取得するSQLを書きたい方
• OracleやDB2やSQL Serverの、分析関数の理解を深めたい方

必要な環境

　本稿で扱うSQLは、MySQL 5.1.28で動作確認しました。ソースコードはPostgreSQL 8.3とOracle 10.2.0.1.0でも動作確認しました。その他、次の環境でも応用が可能です。

• SQL Server
• DB2

1. Range指定のSum関数

　最初は、Range指定のSum関数と同じ結果を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。

　同じIDで、Seqが自分より1小さい行から2大きい行までのValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

select ID,Seq,Val,
sum(Val) over(partition by ID order by Seq
              range between 1 preceding
                        and 2 following) as sumVal
  from IDTable
order by ID,Seq;

　相関サブクエリでの、Range指定の分析関数の代用は簡単で、下記が答えとなります。

select ID,Seq,Val,
(select sum(b.Val)
   from IDTable b
  where b.ID = a.ID
    and b.Seq between a.Seq-1 and a.Seq+2) as sumVal
  from IDTable a
order by ID,Seq;

　相関サブクエリのbetween述語で、「IDが同じであること」および「Seqが自分より1小さい値以上、かつ、自分より2大きい値以下であること」を条件とし、Sum関数でValの合計を求めています。SQLのイメージは下記です。

SQLのイメージ