分析関数の衝撃

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

OracleのRowsやRangeの代用

山岸賢治[著]

2010/04/13 14:00

ポスト

ダウンロード SourceCode (1.6 KB)

Rows between 2 following and unbounded following

　応用して今度は、Rows between 2 following and unbounded followingなcount(*)とminとmaxとsumを代用してみます。サンプルを見てみましょう。

模倣対象のOracleのSQL

select sortKey,Val,
count(*) over(order by sortKey
              Rows between 2 following
                       and unbounded following) as cnt,
min(Val) over(order by sortKey
              Rows between 2 following
                       and unbounded following) as minVal,
max(Val) over(order by sortKey
              Rows between 2 following
                       and unbounded following) as maxVal,
sum(Val) over(order by sortKey
              Rows between 2 following
                       and unbounded following) as sumVal
  from OracleCompOlap
order by sortKey;

出力結果

sortKey	Val	cnt	minVal	maxVal	sumVal
1	1	10	0	9	50
3	4	9	0	9	45
5	5	8	0	8	36
7	9	7	0	7	28
8	8	6	0	7	26
9	2	5	3	7	26
10	0	4	3	7	21
12	5	3	3	6	14
13	7	2	5	6	11
14	3	1	6	6	6
15	5	0	null	null	null
18	6	0	null	null	null

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

PostgreSQL8.4での代用案

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

出力結果

sortKey	Val	cnt	minVal	maxVal	sumVal	Vals
1	1	10	0	9	50	{6,5,3,7,5,0,2,8,9,5}
3	4	9	0	9	45	{6,5,3,7,5,0,2,8,9}
5	5	8	0	8	36	{6,5,3,7,5,0,2,8}
7	9	7	0	7	28	{6,5,3,7,5,0,2}
8	8	6	0	7	26	{6,5,3,7,5,0}
9	2	5	3	7	26	{6,5,3,7,5}
10	0	4	3	7	21	{6,5,3,7}
12	5	3	3	6	14	{6,5,3}
13	7	2	5	6	11	{6,5}
14	3	1	6	6	6	{6}
15	5	0	null	null	null	null
18	6	0	null	null	null	null