分析関数の衝撃

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

OracleのRowsやRangeの代用

山岸賢治[著]

2010/04/13 14:00

ポスト

ダウンロード SourceCode (1.6 KB)

2. Rows between current row and unbounded following

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

出力結果

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

　PostgreSQL 8.4では、rows unbounded precedingが使えることを踏まえて、答えは下記となります。

PostgreSQL8.4での代用案1

select sortKey,Val,
count(*) over(order by sortKey desc
              rows unbounded preceding) as cnt,
min(Val) over(order by sortKey desc
              rows unbounded preceding) as minVal,
max(Val) over(order by sortKey desc
              rows unbounded preceding) as maxVal,
sum(Val) over(order by sortKey desc
              rows unbounded preceding) as sumVal,
array_agg(Val) over(order by sortKey desc
                    rows unbounded preceding) as Vals
  from OracleCompOlap
order by sortKey;

出力結果

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

　order by sortKey Rows between current row and unbounded followingは、order by sortKeyの逆ソートであるorder by sortKey descを使って、order by sortKey desc Rows unbounded precedingに書き換えることができることを踏まえてます。なお、array_agg関数は、集約の内訳を表示するのに便利なので使用してます。

　この場合は、Rows指定でなくRange指定でも同じ結果を取得できるので、下記の別解も考えられます。

PostgreSQL8.4での代用案2

select sortKey,Val,
count(*) over(order by sortKey desc) as cnt,
min(Val) over(order by sortKey desc) as minVal,
max(Val) over(order by sortKey desc) as maxVal,
sum(Val) over(order by sortKey desc) as sumVal,
array_agg(Val) over(order by sortKey desc) as Vals
  from OracleCompOlap
order by sortKey;

　『4.2.8. ウィンドウ関数呼び出し』に記述されているように、order byを指定して、frame_clause（フレームクロウズ）を省略すると、デフォルトのRANGE UNBOUNDED PRECEDINGになります。これはRange between unbounded preceding and current rowと同じ意味なのです。

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

SQLのイメージ