分析関数の衝撃

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

OracleのRowsやRangeの代用

山岸賢治[著]

2010/04/13 14:00

ポスト

ダウンロード SourceCode (1.6 KB)

4. Range 3 Preceding

　最後は、Range 3 Precedingなcount(*)とminとmaxとsumを代用してみます。サンプルを見てみましょう。

OracleCompOlap

sortKey	Val
1	1
3	4
5	5
7	9
8	8
9	2
10	0
12	5
13	7
14	3
15	5
18	6

模倣対象のOracleのSQL

select sortKey,Val,
count(*) over(order by sortKey Range 3 Preceding) as cnt,
min(Val) over(order by sortKey Range 3 Preceding) as minVal,
max(Val) over(order by sortKey Range 3 Preceding) as maxVal,
sum(Val) over(order by sortKey Range 3 Preceding) as sumVal
  from OracleCompOlap
order by sortKey;

出力結果

sortKey	Val	cnt	minVal	maxVal	sumVal
1	1	1	1	1	1
3	4	2	1	4	5
5	5	2	4	5	9
7	9	2	5	9	14
8	8	3	5	9	22
9	2	3	2	9	19
10	0	4	0	9	19
12	5	3	0	5	7
13	7	3	0	7	12
14	3	3	3	7	15
15	5	4	3	7	20
18	6	2	5	6	11

　分析関数のRows指定は、Lag関数やLead関数を使って数行前や数行後の行の値を取得して模倣することができました。分析関数のRange指定は、下記のように相関サブクエリや自己結合を使って模倣できます。なお、array_agg関数は、集約の内訳を表示するのに便利なので使用してます。

PostgreSQL8.4での代用案1（相関サブクエリ）

select sortKey,Val,
(select count(*) from OracleCompOlap b
  where b.sortKey between a.sortKey-3
                      and a.sortKey) as cnt,
(select min(b.Val) from OracleCompOlap b
  where b.sortKey between a.sortKey-3
                      and a.sortKey) as minVal,
(select max(b.Val) from OracleCompOlap b
  where b.sortKey between a.sortKey-3
                      and a.sortKey) as maxVal,
(select sum(b.Val) from OracleCompOlap b
  where b.sortKey between a.sortKey-3
                      and a.sortKey) as sumVal,
(select array_agg(b.Val) from OracleCompOlap b
  where b.sortKey between a.sortKey-3
                      and a.sortKey) as Vals
from OracleCompOlap a
order by sortKey;

出力結果

sortKey	Val	cnt	minVal	maxVal	sumVal	Vals
1	1	1	1	1	1	{1}
3	4	2	1	4	5	{1,4}
5	5	2	4	5	9	{4,5}
7	9	2	5	9	14	{5,9}
8	8	3	5	9	22	{5,9,8}
9	2	3	2	9	19	{9,8,2}
10	0	4	0	9	19	{9,8,2,0}
12	5	3	0	5	7	{2,0,5}
13	7	3	0	7	12	{0,5,7}
14	3	3	3	7	15	{5,7,3}
15	5	4	3	7	20	{5,7,3,5}
18	6	2	5	6	11	{5,6}

PostgreSQL8.4での代用案2（自己結合）

select a.sortKey,a.Val,
count(*) as cnt,min(b.Val) as minVal,
max(b.Val) as maxVal,sum(b.Val) as sumVal,
array_agg(b.Val) as Vals
  from OracleCompOlap a,OracleCompOlap b
 where b.sortKey between a.sortKey-3
                     and a.sortKey
group by a.sortKey,a.Val
order by a.sortKey;

　SQLのイメージは下記となります。order by sortKey Range 3 Precedingに対応する黄緑線と青線を引いてます。

SQLのイメージ

　データ構造によっては、下記のように、Lag関数やLead関数を何度も使ってwindow関数の集約対象になる可能性のある行の値を全て取得してから、case式でwindow関数の集約対象かを判断し、count関数ならばcase式を、min関数ならばLeast関数を、max関数ならばGreatest関数を、sum関数ならばcoalesce関数を使う。といった手段でも分析関数のRange指定を模倣できます。

Lag関数やLead関数を何度も使う方法

select sortKey,Val,
1+case when Lag1 is null then 0 else 1 end
 +case when Lag2 is null then 0 else 1 end
 +case when Lag3 is null then 0 else 1 end as cnt,
   Least(Val,Lag1,Lag2,Lag3) as minVal,
Greatest(Val,Lag1,Lag2,Lag3) as maxVal,
Val+coalesce(Lag1,0)
   +coalesce(Lag2,0)
   +coalesce(Lag3,0) as sumVal
from (select sortKey,Val,
      case when sortKey1 between sortKey-3 and sortKey
           then Lag1 end as Lag1,
      case when sortKey2 between sortKey-3 and sortKey
           then Lag2 end as Lag2,
      case when sortKey3 between sortKey-3 and sortKey
           then Lag3 end as Lag3
      from (select sortKey,Val,
            Lag(sortKey,1) over(order by sortKey) as sortKey1,
            Lag(sortKey,2) over(order by sortKey) as sortKey2,
            Lag(sortKey,3) over(order by sortKey) as sortKey3,
            Lag(Val,1) over(order by sortKey) as Lag1,
            Lag(Val,2) over(order by sortKey) as Lag2,
            Lag(Val,3) over(order by sortKey) as Lag3
            from OracleCompOlap) a) a;