分析関数の衝撃

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

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

山岸賢治[著]

2008/11/11 14:00

ポスト

ダウンロード SourceCode (2.6 KB)

　連載「分析関数の衝撃」では、Oracleの分析関数を主に扱いました。「分析関数の衝撃」の外伝となるこの連載では、 MySQLで、Oracleの分析関数と同じ結果を取得するSQLの考え方と、処理のイメージを解説します。

ポスト

はじめに

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

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

分析関数を使ったSQL

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

出力結果

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

　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;

出力結果

ID	Seq	Val	sumVal	maxVal	cnt
AA	1	100	1150	500	6
AA	2	100	1150	500	6
AA	3	500	1150	500	6
AA	4	200	1150	500	6
AA	5	200	1150	500	6
AA	6	50	1150	500	6
BB	1	200	2300	900	4
BB	2	400	2300	900	4
BB	3	800	2300	900	4
BB	4	900	2300	900	4
CC	1	100	1600	800	3
CC	2	800	1600	800	3
CC	3	700	1600	800	3
DD	1	400	400	400	1
EE	1	50	50	50	1
FF	1	10	150	80	4
FF	3	20	150	80	4
FF	5	40	150	80	4
FF	6	80	150	80	4