はじめに
本稿では、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 | 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と同じ結果を取得します。
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のイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/3107/fig01a.gif)
なお、内部結合を使う方法であれば、例えば、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 |