分析関数の衝撃

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

OracleのRowsやRangeの代用

山岸賢治[著]

2010/04/13 14:00

ポスト

ダウンロード SourceCode (1.6 KB)

3. Rows between unbounded Preceding and 2 Preceding

　次は、Rows between unbounded Preceding and 2 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
              Rows between unbounded Preceding
                       and 2 Preceding) as cnt,
min(Val) over(order by sortKey
              Rows between unbounded Preceding
                       and 2 Preceding) as minVal,
max(Val) over(order by sortKey
              Rows between unbounded Preceding
                       and 2 Preceding) as maxVal,
sum(Val) over(order by sortKey
              Rows between unbounded Preceding
                       and 2 Preceding) as sumVal
  from OracleCompOlap
order by sortKey;

出力結果

sortKey	Val	cnt	minVal	maxVal	sumVal
1	1	0	null	null	null
3	4	0	null	null	null
5	5	1	1	1	1
7	9	2	1	4	5
8	8	3	1	5	10
9	2	4	1	9	19
10	0	5	1	9	27
12	5	6	1	9	29
13	7	7	0	9	29
14	3	8	0	9	34
15	5	9	0	9	41
18	6	10	0	9	44

　本稿の「1. Rows 2 Preceding」と「2. Rows between current row and unbounded following」を踏まえて、答えは下記となります。なお、array_agg関数は、集約の内訳を表示するのに便利なので使用してます。

PostgreSQL8.4での代用案

select sortKey,Val,
Lag(WKcnt,2,0::bigint) over(order by sortKey) as cnt,
Lag(WKminVal,2) over(order by sortKey) as minVal,
Lag(WKmaxVal,2) over(order by sortKey) as maxVal,
Lag(WKsumVal,2) over(order by sortKey) as sumVal,
Lag(WKVals,2)   over(order by sortKey) as Vals
from (select sortKey,Val,
      count(*) over(order by sortKey) as WKcnt,
      min(Val) over(order by sortKey) as WKminVal,
      max(Val) over(order by sortKey) as WKmaxVal,
      sum(Val) over(order by sortKey) as WKsumVal,
      array_agg(Val) over(order by sortKey) as WKVals
        from OracleCompOlap) a
order by sortKey;

出力結果

sortKey	Val	cnt	minVal	maxVal	sumVal	Vals
1	1	0	null	null	null	null
3	4	0	null	null	null	null
5	5	1	1	1	1	{1}
7	9	2	1	4	5	{1,4}
8	8	3	1	5	10	{1,4,5}
9	2	4	1	9	19	{1,4,5,9}
10	0	5	1	9	27	{1,4,5,9,8}
12	5	6	1	9	29	{1,4,5,9,8,2}
13	7	7	0	9	29	{1,4,5,9,8,2,0}
14	3	8	0	9	34	{1,4,5,9,8,2,0,5}
15	5	9	0	9	41	{1,4,5,9,8,2,0,5,7}
18	6	10	0	9	44	{1,4,5,9,8,2,0,5,7,3}

　インラインビューの中で、count(*) over(order by sortKey)といったwindow関数の結果を求めておいてから、Lag関数で2行前の値を取得してます。count関数の代用だけは、Lag(WKcnt,2,0::bigint)といった感じで、2行前の行が存在しなかったら0に変換してます。

　SQLのイメージは下記となります。order by sortKey Rows between unbounded Preceding and 2 Precedingに対応する紫線と黄緑線と青線を引いてます。

SQLのイメージ