★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

37.10. ユーザ定義の集約

PostgreSQLにおける集約関数は、状態値状態遷移関数で定義されています。 つまり集約は、入力行を順次処理して更新される状態値を使用することで動作します。 新しい集約関数を定義するためには、状態値のデータ型、初期状態値、そして状態遷移関数のデータ型を選択します。 状態遷移関数は、前の状態値と現在行の集約のための入力値(複数可)を取り、新たな状態値を返します。 実行中に保持する状態値と求めている集約の結果のデータが違う場合は、最終関数を指定することもできます。 最終関数は、最後の状態値を取り、そして集約の結果として望まれているものを返します。 原則として、遷移関数と最終関数は、通常の関数であり集約以外の状況でも使用することができます。 (実際には、集約の一部として呼び出されて動作する専用の遷移関数を作成することは、多くの場合パフォーマンス上の理由から役立ちます。)

したがって、集約のユーザに見える引数と結果のデータ型に加え、引数と結果の型のどちらとも違う可能性がある内部状態値のデータ型があります。

最終関数を使わない集約を定義した場合は、列の値を行ごとに計算する関数を実行することで集約ができます。 sumはそのような集約の一例です。 sumは0から始まり、常に現在の行の値をその時点までの総和に追加します。 例えば、もしsum集約を複素数(complex)のデータ型で動作するようにしたければ、そのデータ型の加算関数だけが必要になります。 集約の定義は以下のようになります。

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)'
);

これは以下のように使用します。

SELECT sum(a) FROM test_complex;

   sum
-----------
 (34,53.9)

(関数のオーバーロード機能に依存していることに注意してください。 sumという名前の集約関数は複数存在しますが、PostgreSQLは列のcomplex型に適用できるsum関数を見つけ出すことができます。)

上記のsumの定義は、もし非NULLの入力値がなければ0(初期状態)を返します。 本来はこの場合NULLを返したいのではないかと思いますし、標準SQLではsumがそのように動作することを期待しています。 そうするためには、単にinitcond句を省略すれば、初期状態がNULLになります。 通常このことは、sfuncがNULL状態の入力をチェックする必要があることを意味します。 しかしsumや、その他maxminのような単純な集約にとっては、状態変数に最初の非NULL入力値を挿入し、 2番目の非NULL入力値で状態遷移関数の適用を開始すれば十分です。 PostgreSQLは、もし初期状態がNULLで状態遷移関数がstrict(厳密)と宣言されている場合、自動的にそのように動作します(つまりNULL入力では呼び出されないようになります)。

もう1つのstrictな状態遷移関数のデフォルト動作としては、NULL入力値が現れると前の状態値が変わらずに維持されるということがあります。 したがって、NULL値は無視されます。 もしNULL入力に対し他の動作が必要な場合は、状態遷移関数をstrict宣言しないようにします。その代わりにNULL入力の検査をおこなようにコーディングし、必要なことをすればよいのです。

avg(平均値計算)はもっと複雑な集約の一例です。 それには2つの変動する状態が必要になります。入力の合計と入力数のカウントです。 最終的な結果はこれらの値を割算することによって得られます。 平均値計算は配列を状態遷移値として使う典型的な実装です。 例えば、avg(float8)の組み込みの実装は以下のようになっています。

CREATE AGGREGATE avg (float8)
(
    sfunc = float8_accum,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0,0}'
);

注記

float8_accumは、入力の総和と個数だけではなく二乗和も蓄積しますので、2要素ではなく、3要素の配列を必要とします。 それは、avg以外の他の集約でも使用できるようにするためです。

SQLの集約関数はオプションによりDISTINCTORDER BYを許可します。それは集約の遷移関数に渡される行や順序を制御します。これらのオプションは裏側で実装されるので、集約のサポート関数が気にする必要はありません。

さらなる詳細については、CREATE AGGREGATEコマンドを参照してください。

37.10.1. 移動集約モード

集約関数は、移動集約モードをオプションでサポートします。それは、ウィンドウ内のフレーム開始点を移動することで、集約関数の実行を大幅に高速にすることができます。 (集約関数としてのウィンドウ関数の使用に関する情報は3.54.2.8 を参照してください。) 基本的な考え方は、通常の順方向の遷移関数に加えて、集約は逆方向遷移関数を提供します。これによりウィンドウフレームが終了した時点で、集約の実行中の状態値から行を除外することが可能になります。 例えば、sum集約では、順方向遷移関数として加算を使用しており、逆方向遷移関数として減算を使用します。 逆方向遷移関数を持たないとウィンドウ関数は、フレームの開始点に移動するたびに一から集約を再計算しなければなりません。 その実行時間は、入力行の数のフレーム長の平均回数倍に比例します。 逆遷移関数を使用すると実行時間は、入力行の数にのみ比例します。

逆遷移関数には、現在の状態値と現在の状態が含まれる最も古い行の集約入力値(複数可)を渡されます。 与えられた入力行が集約されていなかった場合は、それに続く行のみ状態値を再構築する必要があります。 これは時々、順方向遷移関数は通常の集約モードよりも必要な状態を持つことが必要になります。 そのため、移動集約モードは、通常のモードから完全に分離した実装を使用します。 必要に応じて、独自の状態データ型、独自の順方向遷移関数、及びそれ独自の最終関数を持ちます。 これらは必要がない場合、通常モードのデータ型および関数と同じでも構いません。

例として、移動集約モードをサポートするために、以下のようにsum集約を拡張できます。

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)',
    msfunc = complex_add,
    minvfunc = complex_sub,
    mstype = complex,
    minitcond = '(0,0)'
);

mで始まる名前のパラメータは、移動集約の実装を定義します。 逆遷移関数minvfunc以外はmのない通常の集約パラメータに対応しています。

移動集約モードのための順方向遷移関数は、新しい状態値としてnullを返すことが許されていません。 逆遷移関数がnullを返した場合、関数はこの特定の入力に対して状態計算を逆にできないことを示すものと考えます。そのような集約計算は、現在のフレーム開始位置からやり直すことになります。 この規則は、実行中の状態値から逆転することが現実的でないような、まれなケースで使用することが出来ます。 逆遷移関数はこれらのケースで諦めますが、大部分のケースで動作することが出来ます。 例として、浮動小数点数を扱う集約は、NaN(非数)の入力が実行されている状態値から除去されなければならない時に諦めることを選択するかもしれません。

移動集約サポート関数を記述する際には、逆遷移関数が正しい状態値を正確に再構築できていることを確認することが重要です。 それ以外の場合は、移動集約モードが使用されているかどうかに応じてユーザに見える結果に違いがあるかもしれません。 逆遷移関数を追加する最初の簡単な例は、要件を満たせていないfloat4float8入力のsumです。 稚拙なsum(float8)の宣言です。

CREATE AGGREGATE unsafe_sum (float8)
(
    stype = float8,
    sfunc = float8pl,
    mstype = float8,
    msfunc = float8pl,
    minvfunc = float8mi
);

この集約は、逆遷移関数を持たない場合よりも激しく異なる結果になります。例を考えます。

SELECT
  unsafe_sum(x) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM (VALUES (1, 1.0e20::float8),
             (2, 1.0::float8)) AS v (n,x);

このクエリは 2行目の結果が期待した1ではなく0を返します。 原因は、浮動小数点値で制限された精度です:1e20に1を加えても結果は再び1e20になります。その結果から1e20を引くと1ではなく0になります。 これは、PostgreSQL限定ではなくて、一般的な浮動小数点演算の制限であることに注意してください。

37.10.2. 多様引数と可変長引数集約

集約関数は多様状態遷移関数や多様最終関数を使用することができます。これにより、同じ関数を使用して複数の集約を実装することができます。 37.2.5に多様関数の説明があります。 もう少し細かく言うと、集約関数自体が、単一の集約定義で複数の入力データ型を扱うことができるように、多様入力型(複数可)と多様状態型を指定することができるということです。 以下に多様型の集約の例を示します。

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

ここでは、任意の呼び出しが出来る集約として実際の状態型を(実際の入力型がその要素となる)配列型にしています。 この集約の動作は、その配列型に全ての入力を連結することです。 (組み込みの集約関数array_aggは、この定義での動作よりもより良い性能で、類似の機能を提供しています。)

以下に2つの異なる実データ型を引数として使用した出力例を示します。

SELECT attrelid::regclass, array_accum(attname)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |              array_accum              
---------------+---------------------------------------
 pg_tablespace | {spcname,spcowner,spcacl,spcoptions}
(1 row)

SELECT attrelid::regclass, array_accum(atttypid::regtype)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |        array_accum        
---------------+---------------------------
 pg_tablespace | {name,oid,aclitem[],text[]}
(1 row)

通常、上記の例のように多様型の結果を返す集約関数は多様状態型を持ちます。 それは、最終関数を適正に宣言するために以下が必要になります。結果の型は多様型であり、引数の型は多様型でない必要があります。そうでないとCREATE FUNCTIONは、呼び出しから結果の型を推定することができないので拒否されます。 しかし、状態型として多様型を使用するのは時に不便です。 最も一般的なケースでは集約サポート関数は、C言語で状態型をinternal(内部データ)と宣言して書かれる必要があります。なぜなら、SQLには同等のものがないためです。 このケースに対処するために、集約の入力引数と一致する追加のダミー引数を取るように最終関数を宣言することが可能です。 最終関数が呼び出されたときに特定の値を使用できないため、このようなダミー引数は常にnull値として渡されます。 それらは、多様最終関数の結果の型を集約の入力型(複数可)に合わせる場合のみ使用します。 例えば以下の定義は、組み込み集約のarray_aggと等価です。

CREATE FUNCTION array_agg_transfn(internal, anynonarray)
  RETURNS internal ...;
CREATE FUNCTION array_agg_finalfn(internal, anynonarray)
  RETURNS anyarray ...;

CREATE AGGREGATE array_agg (anynonarray)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    finalfunc_extra
);

ここで、finalfunc_extraオプションは最終関数が状態値に加えて、集約の入力引数(複数可)に対応する追加のダミー引数(複数可)を受け取れることを指定します。 array_agg_finalfnの追加引数anynonarrayにより有効であると宣言をすることができます。

集約関数は、通常の関数の場合とほとんど同じ方法で、最後の引数をVARIADIC配列として宣言することで、可変長の引数を受け入れるようにすることができます。 37.4.5を参照してください。 集約の遷移関数(複数可)は、それら最後の引数と同じ配列型を持っている必要があります。 遷移関数(複数可)は、典型的には、VARIADIC付きになりますが、これは必須ではありません。

注記

可変長集約は、ORDER BYオプション(4.2.7を参照してください)との組み合わせでは、パーサが実引数かどうかを見分けることができないので、簡単に誤用されるようになります。 ORDER BYの右側にあるすべてのものは、集約への引数ではなく、ソートキーであることに留意してください。 例えば、

SELECT myaggregate(a ORDER BY a, b, c) FROM ...

パーサには集約関数の引数1つと3つのソートキーと見えます。 しかし、これは以下のようにユーザーが意図している可能性があります。

SELECT myaggregate(a, b, c ORDER BY a) FROM ...

もしmyaggregateが可変長引数の場合、これらの呼び出しが両方とも妥当かもしれません。

同じ理由で、通常の引数の数とは違う同じ名前の集約関数を作成する前に二度考えるのが賢明です。

37.10.3. 順序集合の集約

これまでに記述された集約は通常の集約です。 PostgreSQLは、順序集合集約もサポートします。それは、通常の集約とは2つの大きな違いがあります。 第一に、入力行ごとに評価される通常の集約引数に加えて、順序集合集約は、集約の呼び出しの時に一度だけ評価される直接引数を持つことが出来ます。 第二に、集約引数の構文は通常、明示的にソート順を指定します。 順序集合集約は通常、呼び出すソート順が必要な局面、例えば順位や百分位数(パーセンタイル)のような特定の行の順序に依存して計算する実装のために使用されます。 例えば、以下は組み込み関数percentile_discの定義と同じです。

CREATE FUNCTION ordered_set_transition(internal, anyelement)
  RETURNS internal ...;
CREATE FUNCTION percentile_disc_final(internal, float8, anyelement)
  RETURNS anyelement ...;

CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement)
(
    sfunc = ordered_set_transition,
    stype = internal,
    finalfunc = percentile_disc_final,
    finalfunc_extra
);

この集約は、float8型の直接引数(百分位数)と、任意のソート可能なデータ型を集約の入力として取ります。 それは、以下のように家計所得の中央値を得ることができます。

SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_disc
-----------------
           50489

ここで0.5は直接の引数です。百分位数が行毎に変化する値であったら意味がありません。

通常の集約の場合とは違って、順序集合集約のための入力行のソートは、裏側でおこなわれていません。それは集約のサポート関数の責任です。 典型的な実装方法は、集約の状態値にtuplesortオブジェクトへの参照を保持し、そのオブジェクトに入ってくる行を供給した後、ソートを完了し、最終関数内でデータを読み出すことです。 この設計は、最終関数がソートされるデータに追加の架空行を注入するなどの特別な操作を実行するのを可能にします。 通常の集約は多くの場合、PL/pgSQLまたは別のPL言語で書かれたサポート関数で実装することができますが、順序集合集約は状態値が任意のSQLデータ型のように定義可能ではないため一般的にC言語で書かれます。 (上の例では、状態値が内部型 — として宣言されていることに気づくでしょう。これは典型的なものです。)

順序集合集約のための状態遷移関数は、現在の状態値を加えた行ごとに集約入力値を受信し、更新された状態値を返します。 これは通常の集約と同じ定義ですが、(もしあっても)直接の引数が提供されていないことに注意してください。 最終関数は、最後の状態値、もしあれば直接の引数の値、および(finalfunc_extraが指定された場合)集約入力(複数)に対応するnull値を受信します。 通常の集約と同様に、finalfunc_extraは集約が多様(型)である場合にのみ便利です。そのとき集約の入力型(複数可)が、最終関数の結果の型と合わせるために追加のダミー引数が必要になります。

現在、順序集合集約は、ウィンドウ関数として使用することができないので移動集約モードをサポートする必要はありません。

37.10.4. 部分集約

省略可能ですが、集約関数は部分集約をサポート出来ます。 部分集約の考え方は、入力データの異なるサブセットに状態遷移関数を独立して実行し、その後、それらのサブセットから得られた状態値を結合します。こうすることで、単一の操作ですべての入力をスキャンした結果であったのと同じ状態値を生成します。 このモードは、別のワーカプロセスをテーブルの異なる部分をスキャンさせることによって並列集約のために使用することが出来ます。 それそれのワーカが、部分状態値を生成し、最後にこれらの状態値を結合して最終状態値を生成します。 (将来このモードは、ローカルとリモートのテーブルの集計を結合させるなどの目的のために使用されるかもしれません。それはまだ実装されていません。)

部分集約をサポートするためには、集約定義が結合関数を提供しなければなりません。 それは、2つの集約の状態型(入力行の2つのサブセットに対する集約した結果を表わす)の値を取り、状態型の新しい値を生成します。状態は、それらの行の集合の組み合わせを集約した後であろうものを表します。 2つのセットからの入力行の相対的な順序であったであろうものが指定されません。 これは入力行の順序に敏感な集約のための結合関数を定義することは通常不可能だということを意味します。

簡単な例を示します。MAXMIN集約は、その遷移関数として使用される「2つの大なり」比較、又は「2つの小なり」比較関数と同じ結合関数を指定することで部分集約をサポートすることが出来ます。 SUM集約は結合関数として加算関数が必要になります。 (ここでも、入力データ型よりも状態値が広い場合を除き遷移関数と同じです。)

結合関数は、2番目の引数として、基本となる入力型ではなく状態型の値を取りますが、遷移関数のように扱われています。 具体的には、null値とstrict関数に対処するためのルールは似ています。 また、initcondが非nullである集約定義を指定する場合、各部分集約の実行のための初期状態として使用されるだけだけでなく、各部分の結果をその状態に結合するために呼び出される結合関数の初期状態としても使用されることに留意してください。

集約の状態型がinternalで宣言されている場合、その結果が集約状態値の正しいメモリコンテキストに割り当てられていることは結合関数の責任です。 これは特に、以下のことを意味します。最初の入力がNULLだと、単純に2番目の入力を返すのは無効です。なぜなら、その値が間違ったコンテキストになり、そして十分な寿命を持っていないことになります。

集約の状態型をinternalで宣言することは、シリアライズ関数デシリアライズ関数を提供するために通常適切です。これらの関数は、状態値を1つのプロセスから別のプロセスにコピーすることを可能にします。 これらの関数がなければ、並列集約を行うことができず、ローカル/リモート集約などの将来のアプリケーションも、おそらく動作しません。

シリアライズ関数は、internalの単一の引数を取り、フラットなblobのバイト状態値パッケージを表わすbytea型を返します。 逆にデシリアライズ関数はその変換を逆にします。 bytea型とinternal型の2つの引数を取り、internal型を返します。 (第2引数は使用せず常に0ですが、型の安全性の理由のために必要とされます。) デシリアライズ関数の結果は単純に、現在のメモリコンテキストに割り当てる必要があります。結合関数の結果とは異なり、長寿命でありません。

集約を並列に実行するために、集約自体にPARALLEL SAFEマークが、されなければならないというのは注目する価値があります。 それのサポート関数のパラレルセーフマークは参照されません。

37.10.5. 集約サポート関数

C言語で作成された関数は、AggCheckCallContextを呼び出して、集約サポート関数として呼び出されているかを検出することができます。 例えば。

if (AggCheckCallContext(fcinfo, NULL))

この検査を行う理由の1つとして、遷移関数に対してこれが真の場合、先頭の入力は一時的な状態値でなければなりませんので、新規に割り当ててコピーを持つことなくそのまま変更しても安全であることが分かることがあります。 例としてint8inc()を参照してください。 (これは関数内で参照渡しの入力を安全に変更できる唯一の場合です。 特に通常の集約のための最終関数はいかなる場合でもそれらの入力を変更してはなりません。 なぜならいくつかのケースでは、同じ最終状態の値から再実行されることがあるからです。)

AggCheckCallContextの第2引数は、集約の状態値が保管されているメモリコンテキストを取得するために使用できます。 これは状態値として展開されたオブジェクト(37.11.1を参照)を使用する遷移関数に便利です。 最初の呼び出しで、遷移関数はメモリコンテキストが集約状態のコンテキストの子である展開されたオブジェクトを返し、その後の呼び出しで同じ展開されたオブジェクトを保持し続ける必要があります。 array_append()の例を参照してください。 (array_append()は組み込み集約の遷移関数ではありませんが、カスタム集約の遷移関数で使用すると効率的に動作するように書かれています。)

別のサポートルーチンとしてC言語で書かれたAggGetAggref集約関数が利用可能です。それは、集約の呼び出しを定義するAggrefパースノードを返します。 これは主に順序集合集約で有用です。これはソートの順序をどう実現するかAggrefノードの内部構造まで検査することができます。 その例は、PostgreSQLソースコード中のorderedsetaggs.cから見つけることができます。