はじめに
本稿では、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についてです。まずは、テーブルのデータと、出力結果を考えます。
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で、Seqが自分より1小さい行から2大きい行までのValの合計を求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。
分析関数を使った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;
出力結果
| ID | Seq | Val | sumVal |
| AA | 1 | 100 | 700 (100+100+500) |
| AA | 2 | 100 | 900 (100+100+500+200) |
| AA | 3 | 500 | 1000 (100+500+200+200) |
| AA | 4 | 200 | 950 (500+200+200+50) |
| AA | 5 | 200 | 450 (200+200+50) |
| AA | 6 | 50 | 250 (200+50) |
| BB | 1 | 200 | 1400 (200+400+800) |
| BB | 2 | 400 | 2300 (200+400+800+900) |
| BB | 3 | 800 | 2100 (400+800+900) |
| BB | 4 | 900 | 1700 (800+900) |
| CC | 1 | 100 | 1600 (100+800+700) |
| CC | 2 | 800 | 1600 (100+800+700) |
| CC | 3 | 700 | 1500 (800+700) |
| DD | 1 | 400 | 400 |
| EE | 1 | 50 | 50 |
| FF | 1 | 10 | 30 (10+20) |
| FF | 3 | 20 | 60 (20+40) |
| FF | 5 | 40 | 120 (40+80) |
| FF | 6 | 80 | 120 |
相関サブクエリでの、Range指定の分析関数の代用は簡単で、下記が答えとなります。
相関サブクエリを使うSQL
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のイメージ
