はじめに
本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。本稿では、PostgreSQL 8.4でOracleの分析関数のRows指定やRange指定と同じ結果を取得するSQLを扱います。
対象読者
- PostgreSQLで
window関数を使ってみたい方 - 分析関数の理解を深めたい方
必要な環境
本稿で扱うSQLは、PostgreSQL 8.4 beta2で動作確認しました。その他、次の環境でも応用が可能です。
- Oracle
- DB2
- SQL Server
1. Rows 2 Preceding
『PostgreSQLの分析関数の衝撃2』の「5. 移動累計を求める」では、PostgreSQL 8.4では文法エラーになるRows 2 precedingなsumを代用する方法を扱いました。今度は、Rows 2 Precedingなcount(*)とminとmaxを代用してみます。サンプルを見てみましょう。
OracleRows2
| ID | sortKey | Val |
| 1 | 1 | 10 |
| 1 | 5 | 90 |
| 1 | 8 | 50 |
| 1 | 9 | 70 |
| 2 | 1 | 80 |
| 2 | 3 | 20 |
| 2 | 6 | 40 |
| 2 | 7 | 50 |
| 3 | 1 | 0 |
| 3 | 2 | 30 |
| 3 | 5 | 50 |
| 4 | 1 | 60 |
模倣対象のOracleのSQL
select ID,sortKey,Val,
count(*) over(partition by ID
order by sortKey Rows 2 Preceding) as cnt,
min(Val) over(partition by ID
order by sortKey Rows 2 Preceding) as minVal,
max(Val) over(partition by ID
order by sortKey Rows 2 Preceding) as maxVal
from OracleRows2
order by ID,sortKey;
出力結果
| ID | sortKey | Val | cnt | minVal | maxVal |
| 1 | 1 | 10 | 1 | 10 | 10 |
| 1 | 5 | 90 | 2 | 10 | 90 |
| 1 | 8 | 50 | 3 | 10 | 90 |
| 1 | 9 | 70 | 3 | 50 | 90 |
| 2 | 1 | 80 | 1 | 80 | 80 |
| 2 | 3 | 20 | 2 | 20 | 80 |
| 2 | 6 | 40 | 3 | 20 | 80 |
| 2 | 7 | 50 | 3 | 20 | 50 |
| 3 | 1 | 0 | 1 | 0 | 0 |
| 3 | 2 | 30 | 2 | 0 | 30 |
| 3 | 5 | 50 | 3 | 0 | 50 |
| 4 | 1 | 60 | 1 | 60 | 60 |
Rows 2 Precedingなので、ソートキーをsortKeyとしての1行前と2行前の行の値を求めればよさそうだと考えて、答えは下記となります。
PostgreSQL8.4での代用案
select ID,sortKey,Val,
1+case when Lag1 is null then 0 else 1 end
+case when Lag2 is null then 0 else 1 end as cnt,
Least(Val,Lag1,Lag2) as minVal,
Greatest(Val,Lag1,Lag2) as maxVal
from (select ID,sortKey,Val,
Lag(Val,1) over(partition by ID
order by sortKey) as Lag1,
Lag(Val,2) over(partition by ID
order by sortKey) as Lag2
from OracleRows2) a
order by ID,sortKey;
Lag関数で1行前と2行前を求めて（なければnull）、case式やLeast関数やGreatest関数で使用してます。補足ですが、OracleやDB2のLeast関数やGreatest関数は、引数に1つでもnullがあると関数の値もnullになりますが、PostgreSQLのLeast関数やGreatest関数はnullを無視してくれます。
SQLのイメージは下記となります。partition by IDに対応する赤線と、order by sortKey Rows 2 Precedingに対応する黄緑線と青線を引いてます。
SQLのイメージ
