分析関数の衝撃

PostgreSQLの分析関数の衝撃（8）
――RowsとRangeの代用

OracleのRowsやRangeの代用

2010/04/13 14:00

ポスト

　本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。本稿では、PostgreSQL 8.4でOracleの分析関数のRows指定やRange指定と同じ結果を取得するSQLを解説します。

ポスト

はじめに

　本連載では、分析関数の衝撃シリーズを、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のイメージ