PostgreSQL ユーザ定義の集約の追加説明
PostgreSQL の日本語ドキュメントで、ユーザ定義の集約のところが面白かったので付け足して解説してみます。
PostgreSQL 9.4 マニュアルのちょっと残念なところ
35.10.ユーザ定義の集約 には、移動集約モードがユーザ定義の集約関数でも使用可能で、その作成の仕方が書かれています。
しかし残念ながら、マニュアルで挙げられている例が複素数という敷居の高い例をあげていて、さらに複素数型という標準にない型を作成し、その複素数型の作成はその後のページ以降35.11. ユーザ定義の型に書かれているという、非常にもったいない構成になっています。
読む順番を入れ替えれば試すことは出来ますが、ユーザ定義集約を試したいならそこまで難しい道を辿らなくても、もっと簡単に試すことが出来ます。
そのため、ユーザ定義関数を移動集約モードに対応することで、華麗にスルーされそうなこの章にスポットを当ててみたいと思います。
移動集約モード
移動集約モードとは簡単に言えば、Window関数のパフォーマンスを上げるためだけに既に計算した結果をうまく使うモードです。
例えば、1,2,3,4,5...10 と並んでいる数列の現在の位置から2つ先までを足した合計を出したいとしましょう。
現在の位置が1なら1+2+3=6、2なら2+3+4=9、3なら3+4+5=12...となります。
素直に実装すると1行進めて3つの数字を足すことを繰り返しおこなう実装になりますが、このとき前の値を一つ引いた値から次の値を一つ足すことで同じ結果が得られることに気がつきます。つまり 1,2,3 = 6 から1行次の値を計算するときは、1+2+3=6 から -1 して 4を足すと 1+2+3-1+4=9になります。1行移動したときに全部の計算をし直さなくても前の値から不要な一行分引いて、必要な一行分足せば結果を得ることが出来ます。
2つ先ぐらいなら足し算2つなので、その都度計算しても問題ないですが、例えば100先まで1行づつ計算することを考えると有効であると言えます。これを実現するのが移動集約モードです。
実際にPostgreSQL 9.4に組み込まれているWindow関数では実際にそういう計算をして計算量を減らしています。また、それだけではなくユーザ定義で作成出来る関数でも簡単に使用出来るようになっています。
ということで、ここではテキスト型の合計(sum)関数を作成することで、内部の動きを可視化して見ようと思います。
PostgreSQLのユーザ定義関数はいろんな言語で作成出来るようになっていますが、一番お手軽なSQLで作成してます。
文字列の合計を返す関数の作成
まずはテキスト型の集約関数の合計から呼び出されるテキストの足し算を作成します。ここでは、わかりやすいように文字列と文字列の間に'+'を入れて一つの文字列を作成するtext_add 関数を作成します。
CREATE FUNCTION text_add(text,text) RETURNS text
AS 'SELECT concat($1,''+'',$2)'
LANGUAGE SQL;
この関数を'a'と'b'の引数で渡すと'a+b'と返します。
SELECT text_add('a','b');
text_add
----------
a+b
(1 行)
この関数を使用する集約関数を作成するだけで文字列の合計(sum_s)関数が作成出来ます。詳しい作成の仕方は上述のマニュアルを参照して下さい。と言っても sfuncに先ほど作成した関数を指定して、stype(入力型)にtext型、initcondに初期値を入れているだけです。
CREATE AGGREGATE sum_s (text)
( sfunc = text_add,
stype = text,
initcond = '');
テストテーブルの作成
集約関数が出来たので集約するためのテーブルを用意します。数値1,2,3...10の列と文字列1,2,3...10の列があるテーブルで試してみましょう。
CREATE TABLE test (i int primary key, i_s text);
INSERT INTO test
(SELECT generate_series(1,10) as i,
to_char(generate_series(1,10),'FM99') as i_s );
これでi(数値)、i_s(文字列)の1〜10が入ったテーブルが作成されてます。
SELECT * FROM test;
i | i_s
----+-----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
(10 行)
このテーブルを集約してみます。
SELECT sum(i), sum_s(i_s) FROM test;
sum | sum_s
-----+-----------------------
55 | +1+2+3+4+5+6+7+8+9+10
(1 行)
'+'で繋げられた文字列の合計が出来ました。
Window関数で試す
作成した集約関数はWindow関数でも使用することが出来ます。1行づつ順に足していくWindow関数で試してみましょう。
SELECT i, sum(i) OVER (ORDER BY i), sum_s(i_s) OVER (ORDER BY i) from test;
i | sum | sum_s
----+-----+-----------------------
1 | 1 | +1
2 | 3 | +1+2
3 | 6 | +1+2+3
4 | 10 | +1+2+3+4
5 | 15 | +1+2+3+4+5
6 | 21 | +1+2+3+4+5+6
7 | 28 | +1+2+3+4+5+6+7
8 | 36 | +1+2+3+4+5+6+7+8
9 | 45 | +1+2+3+4+5+6+7+8+9
10 | 55 | +1+2+3+4+5+6+7+8+9+10
(10 行)
2行先を足すWindow関数
今度は2行先までの合計をWindow関数を使って文字列で表現してみます。 1のときは、1+2+3、2のときは、2+3+4...の計算式とすることが出来ます。
SELECT i
, sum(i) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
, sum_s(i_s) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
FROM test;
i | sum | sum_s
----+-----+---------
1 | 6 | +1+2+3
2 | 9 | +2+3+4
3 | 12 | +3+4+5
4 | 15 | +4+5+6
5 | 18 | +5+6+7
6 | 21 | +6+7+8
7 | 24 | +7+8+9
8 | 27 | +8+9+10
9 | 19 | +9+10
10 | 10 | +10
(10 行)
1行移動する毎に3つの数字を足し算していることがわかります。
移動集約モードに対応した集約関数
PostgreSQL 9.3まではここまででした。9.4では、移動集約モードをサポートしているため、集約関数を拡張できます。移動集約モードをサポートするには、足し算の'+'の関数と引き算の'-'の関数が必要となります。
引き算の関数を作成しますが、ここでは可視化するために文字列を'-'で繋げる関数を作成してみます。
足し算の関数の'+'を'-'に変えただけです。
CREATE FUNCTION text_sub(text,text)
RETURNS text
AS 'SELECT concat($1,''-'',$2)'
LANGUAGE SQL;
この足し算(text_add)と引き算(text_sub)を利用する集約関数を作成します。
CREATE AGGREGATE sum_m (text)
( sfunc = text_add,
stype = text,
initcond = '',
mstype = text,
msfunc = text_add,
minvfunc = text_sub);
m で始まる変数に追加します。 msfuncに足し算(sfuncと同じ関数)とminvfuncに引き算の関数を指定します。
このsum_mを使用した集約関数を実行してみます。
SELECT i
, sum(i) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
, sum_s(i_s) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
, sum_m(i_s) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
FROM test ORDER BY i;
i | sum | sum_s | sum_m
----+-----+---------+-----------------------------------------
1 | 6 | +1+2+3 | +1+2+3
2 | 9 | +2+3+4 | +1+2+3-1+4
3 | 12 | +3+4+5 | +1+2+3-1+4-2+5
4 | 15 | +4+5+6 | +1+2+3-1+4-2+5-3+6
5 | 18 | +5+6+7 | +1+2+3-1+4-2+5-3+6-4+7
6 | 21 | +6+7+8 | +1+2+3-1+4-2+5-3+6-4+7-5+8
7 | 24 | +7+8+9 | +1+2+3-1+4-2+5-3+6-4+7-5+8-6+9
8 | 27 | +8+9+10 | +1+2+3-1+4-2+5-3+6-4+7-5+8-6+9-7+10
9 | 19 | +9+10 | +1+2+3-1+4-2+5-3+6-4+7-5+8-6+9-7+10-8
10 | 10 | +10 | +1+2+3-1+4-2+5-3+6-4+7-5+8-6+9-7+10-8-9
(10 行)
sum_m の結果が長くなってますが、2行目からは、前の行の不要分を引いて、2つ先の行分を足す処理で求められています。
例えば 8行目では、前の行の既に求められている「+1+2+3-1+4-2+5-3+6-4+7-5+8-6+9」に対して -7と+10の処理をしているということになります。
100行先まで10行分求めようとしたら、移動集約モードが無い処理では99回の加算を10回 99×10 = 990回計算する必要がありますが、
移動集約モードがあれば99 + (2 * 9) = 117回と圧倒的に計算量を減らすことが出来ます。
移動集約モードに対応しているかはシステムテーブルのpg_aggregateを見ればわかります。
aggmtransfn, aggminvtransfn が定義されている集約関数は移動集約モードに対応しているため、9.4以上で
速度向上が期待できます。
SELECT aggfnoid, aggmtransfn, aggminvtransfn
FROM pg_aggregate
WHERE aggmtransfn != 0;
aggfnoid | aggmtransfn | aggminvtransfn
------------------------+-------------------+--------------------
pg_catalog.avg | int8_avg_accum | int8_avg_accum_inv
pg_catalog.avg | int4_avg_accum | int4_avg_accum_inv
pg_catalog.avg | int2_avg_accum | int2_avg_accum_inv
pg_catalog.avg | numeric_avg_accum | numeric_accum_inv
pg_catalog.avg | interval_accum | interval_accum_inv
pg_catalog.sum | int8_avg_accum | int8_avg_accum_inv
....
このように移動集約モードがユーザ定義関数でも簡単に作成出来るようになっているよというお話でした。