はじめに
本稿では、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 |
