MySQLで分析関数を模倣1（前編）

はじめに

　本稿では、Oracleの分析関数の中で、Rows指定およびRange指定なしのsum関数、Rank関数、dense_Rank関数と同じ結果を取得する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ごとのValの合計を求める

　最初は、IDごとにValの合計を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。

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

select ID,Seq,Val,
sum(Val) over(partition by ID) as sumVal
  from IDTable
order by ID,Seq;

　partition by IDという指定をして、IDごとのValの合計を求めていますので、答えは相関サブクエリを使った下記となります。

select ID,Seq,Val,
(select sum(b.Val)
   from IDTable b
  where b.ID = a.ID) as sumVal
  from IDTable a
order by ID,Seq;

　相関サブクエリでwhere b.ID = a.IDを指定して、外側のselect文の結果と同じIDを条件として、sum関数でValの合計を求めてます。結果としてIDごとのValの合計が求まります。別解として、内部結合を使う方法もあります。

select a.ID,a.Seq,a.Val,b.sumVal
  from IDTable a,
       (select ID,
        sum(Val) as sumVal
          from IDTable
        group by ID) b
 where b.ID = a.ID
order by a.ID,a.Seq;

　インラインビューでIDごとのValの合計を求めておいて、IDの一致を結合条件として内部結合させています。

　SQLのイメージは下記となります。

SQLのイメージ

　なお、内部結合を使う方法であれば、例えば、IDごとのValの最大値と行数も欲しい場合は、下記のように列を追加するだけでよいです。

select a.ID,a.Seq,a.Val,b.sumVal,b.maxVal,b.cnt
  from IDTable a,
       (select ID,
        sum(Val) as sumVal,
        max(Val) as maxVal,
        count(*) as cnt
          from IDTable
        group by ID) b
 where b.ID = a.ID
order by a.ID,a.Seq;