CREATE AGGREGATE — 新しい集約関数を定義する
CREATE [ OR REPLACE ] AGGREGATEname
( [argmode
] [argname
]arg_data_type
[ , ... ] ) ( SFUNC =sfunc
, STYPE =state_data_type
[ , SSPACE =state_data_size
] [ , FINALFUNC =ffunc
] [ , FINALFUNC_EXTRA ] [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , COMBINEFUNC =combinefunc
] [ , SERIALFUNC =serialfunc
] [ , DESERIALFUNC =deserialfunc
] [ , INITCOND =initial_condition
] [ , MSFUNC =msfunc
] [ , MINVFUNC =minvfunc
] [ , MSTYPE =mstate_data_type
] [ , MSSPACE =mstate_data_size
] [ , MFINALFUNC =mffunc
] [ , MFINALFUNC_EXTRA ] [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , MINITCOND =minitial_condition
] [ , SORTOP =sort_operator
] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] ) CREATE [ OR REPLACE ] AGGREGATEname
( [ [argmode
] [argname
]arg_data_type
[ , ... ] ] ORDER BY [argmode
] [argname
]arg_data_type
[ , ... ] ) ( SFUNC =sfunc
, STYPE =state_data_type
[ , SSPACE =state_data_size
] [ , FINALFUNC =ffunc
] [ , FINALFUNC_EXTRA ] [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , INITCOND =initial_condition
] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] [ , HYPOTHETICAL ] ) または以下の旧構文 CREATE [ OR REPLACE ] AGGREGATEname
( BASETYPE =base_type
, SFUNC =sfunc
, STYPE =state_data_type
[ , SSPACE =state_data_size
] [ , FINALFUNC =ffunc
] [ , FINALFUNC_EXTRA ] [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , COMBINEFUNC =combinefunc
] [ , SERIALFUNC =serialfunc
] [ , DESERIALFUNC =deserialfunc
] [ , INITCOND =initial_condition
] [ , MSFUNC =msfunc
] [ , MINVFUNC =minvfunc
] [ , MSTYPE =mstate_data_type
] [ , MSSPACE =mstate_data_size
] [ , MFINALFUNC =mffunc
] [ , MFINALFUNC_EXTRA ] [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , MINITCOND =minitial_condition
] [ , SORTOP =sort_operator
] )
CREATE AGGREGATE
は、新しい集約関数を定義します。
CREATE OR REPLACE AGGREGATE
は、新しい集約関数を定義するか、既存の定義を置き換えます。
配布物には基本的、かつ、よく使用される集約関数がいくつか含まれています。これらの集約関数については、9.21に文書化されています。
新しい型を定義する場合、またはまだ提供されていない集約関数が必要な場合、必要な機能を実現するためにCREATE AGGREGATE
を使うことができます。
既存の定義を置き換える場合には、引数の型、結果の型、直接引数の数を変えることはできません。 また、新しい定義は古いものと同じ種類(通常の集約、順序集合集約、仮想集合集約)でなければなりません。
スキーマ名が付けられている場合(例えば、CREATE AGGREGATE myschema.myagg ...
)、集約関数は指定されたスキーマに作成されます。
スキーマ名がなければ、集約関数は現在のスキーマに作成されます。
集約関数は名前と入力データ型(複数可)の組み合わせによって識別されます。 演算の対象となる入力データ型が異なっていれば、同じスキーマ内に同じ名前の集約関数があっても構いません。 1つのスキーマ内では、集約関数の名前と入力データ型の組み合わせは、通常の関数の名前と入力データ型の組み合わせと異なる必要があります。 この動作は通常の関数名についてオーバーロードする時と同じです(CREATE FUNCTIONを参照してください)。
単純な集約関数は1つか2つの通常の関数から作られます。
状態遷移関数sfunc
と最終計算関数ffunc
(省略可能)です。
これらは以下のように使われます。
sfunc
( 内部状態, 次のデータ値 ) ---> 次の内部状態ffunc
( 内部状態 ) ---> 集約の結果
PostgreSQLは、集約の現在の内部状態を保持するstype
データ型の一時変数を作成します。
それぞれの入力行に対して、集約引数の値が計算され、現在の状態値と新しい引数値で状態遷移関数が呼び出され、新しい内部状態変数が計算されます。
全ての行が処理されると、最終関数が1回呼び出され、集約の出力値が計算されます。
最終関数がない場合は、終了時の状態値がそのまま返されます。
集約関数は、初期条件、つまり内部状態値の初期値を提供することができます。
これはtext
型の値としてデータベースに格納されますが、状態値データ型の定数として有効な外部表現でなければいけません。
初期状態が与えられていない場合、状態値はNULLから始まります。
状態遷移関数が「strict」と宣言されている場合、NULLを入力値にして呼び出すことはできません。
そのような遷移関数では、集約は次のように実行されます。
NULL入力値を持つ行は無視されます。
(関数は呼び出されず、前回の状態値が保持されます。)
初期状態値がNULLである場合、初めて入力行がすべて非NULL入力値であった時にその最初の引数の値で状態値を置き換え、以後、すべて非NULL入力値のそれぞれの行について、遷移関数が呼び出されます。
このような動作は、max
のような集約を実装するには便利です。
ただし、state_data_type
が最初のarg_data_type
と同じ時にのみ有効であることに注意してください。
これらの型が異なる時は、非NULL初期値を供給するか、strictでない遷移関数を使わなければいけません。
状態遷移関数がstrictでない場合は、それぞれの入力行に対してその関数が無条件に呼び出されるので、NULL入力とNULL状態値を自分で処理しなければいけません。 これは、関数の作成者が、集約関数におけるNULL値の扱いを完全に制御できることを意味します。
最終関数が「strict」と宣言されていると、終了状態値がNULLの時は、最終関数が呼び出されません。
その場合、NULLという結果が自動的に出力されます
(もちろんこれは、strictな関数の一般的な動作に過ぎません)。
いずれにせよ、最終関数はNULLを返すことができます。
例えば、avg
の最終関数は、入力が0行だとわかるとNULLを返します。
最終関数を、状態値だけでなく、集約の入力値に対応する追加パラメータも取るように宣言すると便利なことがあります。
こうすることの主な理由に、最終関数が多様型で、状態値のデータ型だけでは結果の型を決定するのに不十分である、ということがあります。
これらの追加パラメータは必ずNULLとして渡されます(従ってFINALFUNC_EXTRA
オプションが使われている場合、最終関数はstrictであってはいけません)が、それでも有効なパラメータです。
最終関数は、現在の呼び出しでの実際の引数の型を特定するために、例えばget_fn_expr_argtype
を使うことができます。
集約は、38.12.1に記述されているように移動集約モードをサポートすることができます。
このためには、MSFUNC
、MINVFUNC
、MSTYPE
のパラメータを指定する必要があり、またオプションでMSSPACE
、MFINALFUNC
、MFINALFUNC_EXTRA
、MFINALFUNC_MODIFY
、MINITCOND
のパラメータを指定できます。
MINVFUNC
を除き、これらのパラメータは、M
のない単純集約の対応するパラメータのように動作しますが、集約について逆変換関数を含む別の実装を定義します。
パラメータのリストにORDER BY
を含む構文は、順序集合集約と呼ばれる特別な種類の集約を作ります。
またHYPOTHETICAL
が指定されている場合は、仮想集合集約が作られます。
これらの集約は、ソートされた値のグループに対して、順序に依存した方法で作用するため、入力についてのソート順の指定は、呼び出しにおける本質的な部分になります。
また、これらの集約は直接引数をとることができます。
直接引数は、行毎に一度ではなく、集約に対して一度だけ評価されます。
仮想集合集約は、順序集合集約のサブクラスで、直接引数のいくつかが、集約される引数の列と、数とデータ型についてマッチする必要があります。
これにより、直接引数の値を、「仮想的な」行として、集約の入力行の集合に加えることができます。
38.12.4で説明されている通り、集約では部分集約をサポートすることができます。
このためにはCOMBINEFUNC
パラメータを指定する必要があります。
state_data_type
がinternal
の場合、通常はSERIALFUNC
およびDESERIALFUNC
パラメータも提供して、並列集約を可能にするのが適切でしょう。
並列集約を可能にするには、集約にPARALLEL SAFE
の印をつける必要もあることに注意してください。
MIN
やMAX
のような振舞いをする集約では、すべての入力行を走査せずにインデックスを検索することで最適化できることがあります。
このように最適化される集約の場合、ソート演算子を指定することで明示してください。
その演算子で生成されるソート順で集約の最初の要素が生成されなければならないということが基本的な必要条件です。
言い換えると、
SELECT agg(col) FROM tab;
が以下と同じでなければならないということです。
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
更に、集約がNULL入力を無視すること、および、NULL以外の入力がまったくなかった時にのみNULLという結果を返すことも前提となります。
通常、データ型の<
演算子はMIN
のソート演算子として、また、>
演算子はMAX
のソート演算子として適切です。
指定した演算子がB-treeインデックス演算子クラスの「より小さい」ストラテジか「より大きい」ストラテジのメンバでない限り、最適化が実際には効果がないことに注意してください。
集約関数を作成するためには、引数の型、状態の型、戻り値の型に対するUSAGE
権限およびサポート関数に対するEXECUTE
権限を持たなければなりません。
name
作成する集約関数の名前です(スキーマ修飾名も可)。
argmode
引数のモードで、IN
またはVARIADIC
です。
(集約関数はOUT
の引数をサポートしません。)
省略した場合のデフォルトはIN
です。
VARIADIC
を指定できるのは、最後の引数だけです。
argname
引数の名前です。 現在は、文書化を目的とする場合にのみ有効です。 省略した場合、引数には名前がありません。
arg_data_type
集約関数が演算する入力データ型です。
引数が存在しない集約関数を作成するには、引数指定のリストに*
と記載してください
(例えば count(*)
などの集約です)。
base_type
CREATE AGGREGATE
の旧構文では、入力データ型は集約の名前の次に記載されたものではなくbasetype
パラメータにより指定されます。
この構文では入力パラメータを1つしかとれないことに注意してください。
この構文で引数を持たない集約を定義するためには、basetype
を"ANY"
(*
ではありません)と指定してください。
順序集合集約関数は旧構文では定義できません。
sfunc
それぞれの入力行に対して呼び出される状態遷移関数の名前です。
通常のN
引数を持つ集約関数では、sfunc
はN
+1個の引数を取らなければなりません。
最初の引数はstate_data_type
型で、残りはその集約の入力データ型として宣言したものと一致していなければなりません。
この関数はstate_data_type
型の値を返さなければなりません。
この関数は、現在の状態値と現在の入力データ値を受け取り、次の状態値を返します。
順序集合(仮想集合を含む)集約では、状態遷移関数は現在値と集約引数のみを受け取り、直接引数は受け取りません。 それ以外の点は全く同じです。
state_data_type
集約の状態値のデータ型です。
state_data_size
集約の状態値のおおよその平均サイズ(単位はバイト)です。
このパラメータを省略した場合、あるいはゼロを指定した場合、state_data_type
に基づいたデフォルトの推定が使われます。
プランナは、グループ化された集約のクエリに必要なメモリを推定するのに、この値を使います。
ffunc
最終関数の名前です。最終関数は、全ての入力行に対する処理が終わった後、集約の結果を計算するために呼び出されます。
通常の集約では、この関数はstate_data_type
型の引数を1つ取らなければなりません。
集約の出力データ型はこの関数の戻り値として定義されます。
ffunc
が指定されない場合には、集約の結果として終了時の状態値が使われます。出力型はstate_data_type
になります。
順序集合(仮想集合を含む)集約では、最終関数は終了時の状態値だけでなく、すべての直接引数の値も受け取ります。
FINALFUNC_EXTRA
が指定された場合、最終関数は、終了時の状態値と直接引数に加えて、集約の通常の(集約された)引数に対応する追加のNULL値を受け取ります。
これは主に、多様型の集約が定義されているときに、集約の結果の型を正しく解決するのに役立ちます。
FINALFUNC_MODIFY
= { READ_ONLY
| SHAREABLE
| READ_WRITE
}
このオプションは、最終関数が引数を変更しない純粋な関数であるかどうかを指定します。
READ_ONLY
は変更しないことを示します。他の2つの値は遷移状態値を変更するかもしれないことを示します。
さらなる詳細は以下の注釈をご覧ください。
順序集合集約ではデフォルトがREAD_WRITE
であることを除き、デフォルトはREAD_ONLY
です。
combinefunc
集約関数が部分集約をサポートできるようにするために、combinefunc
を指定することができます。
これが指定されると、combinefunc
は、入力値の何らかの部分集合に対する集約の結果を含む2つのstate_data_type
値を結合し、両方の入力に対する集約結果を表す新しいstate_data_type
を生成しなければなりません。
この関数は、個々の入力行に対して作用してそれを集約中の状態に追加するのではなく、実行中の状態に別の集約状態を追加するsfunc
として考えることができます。
combinefunc
は、state_data_type
の引数を2つ取り、state_data_type
の値を返すものとして宣言されなければなりません。
この関数は「strict」とすることもできます。
その場合、入力状態の一方がNULLのときは関数が呼び出されず、他方の状態が正しい結果であると見なされます。
state_data_type
がinternal
の集約関数では、combinefunc
をSTRICTにすることはできません。
この場合、combinefunc
はNULL状態を正しく処理し、返される状態が集約のメモリコンテキスト内に適切に保存されることを確実にする必要があります。
serialfunc
state_data_type
がinternal
の集約関数は、serialfunc
関数がある場合に限り、並列集約に参加することができます。
serialfunc
は、集約の状態を他のプロセスに転送するためにbytea
の値にシリアライズしなければなりません。
この関数はinternal
型の引数を1つ取り、bytea
型を返さなければなりません。
これに対応するdeserialfunc
も必要です。
deserialfunc
以前にシリアライズされた集約状態をデシリアライズしてstate_data_type
に戻します。
この関数はbytea
型およびinternal
型の2つの引数を取り、internal
型の結果を生成しなければなりません。
(注意:2番目のinternal
の引数は使用されませんが、型の安全性の理由から必要となっています。)
initial_condition
状態値の初期設定です。
データ型state_data_type
として受け取り可能な文字列定数でなければいけません。
このパラメータが指定されない場合、状態値はNULLから始まります。
msfunc
移動集約モードにおいて、それぞれの入力行に対して呼び出される前方状態遷移関数の名前です。
これは最初の引数と結果がmstate_data_type
型で、state_data_type
とは異なるかもしれないことを除けば、通常の遷移関数と全く同じです。
minvfunc
移動集約モードで使われる逆状態遷移関数の名前です。
この関数はmsfunc
と同じ引数および結果型を持ちますが、現在の集約状態に対して、値を追加するのではなく、取り除くために使われます。
逆遷移関数は前方状態遷移関数と同じstrictさの属性を持っていなければなりません。
mstate_data_type
移動集約モードを使うときの、集約状態値のデータ型です。
mstate_data_size
移動集約モードを使うときの、集約状態値のおおよその平均サイズ(バイト単位)です。
state_data_size
と同じように作用します。
mffunc
移動集約モードを使うときに、すべての入力行が走査された後で、集約結果を計算するために呼び出される最終関数の名前です。
これは、最初の引数の型がmstate_data_type
であり、MFINALFUNC_EXTRA
により追加のダミー引数を指定できることを除けば、ffunc
と同じように作用します。
mffunc
またはmstate_data_type
によって決定される集約結果の型は、集約の通常の実装によって決定される型と適合しなければなりません。
MFINALFUNC_MODIFY
= { READ_ONLY
| SHAREABLE
| READ_WRITE
}
このオプションはFINALFUNC_MODIFY
と似ていますが、移動集約最終関数の挙動を記述します。
minitial_condition
移動集約モードを使うときの、状態値の初期設定です。
これは、initial_condition
と同じように作用します。
sort_operator
MIN
またはMAX
のような集約に対して関連付けされるソート演算子です。
これは単なる演算子の名前です(スキーマ修飾可能)。
この演算子は集約(これは単一引数の通常の集約でなければなりません)と同じ入力データ型を持つと前提されています。
PARALLEL =
{ SAFE
| RESTRICTED
| UNSAFE
}
PARALLEL SAFE
、PARALLEL RESTRICTED
、PARALLEL UNSAFE
の意味はCREATE FUNCTION
におけるものと同じです。
集約は、その印がPARALLEL UNSAFE
(これがデフォルトです!)あるいはPARALLEL RESTRICTED
となっている場合、並列処理での使用を考慮されません。
プランナは集約のサポート関数の並列処理安全性の印を考慮せず、集約自体の印のみを参照することに注意してください。
HYPOTHETICAL
順序集合集約についてのみ、このフラグは、仮想集合集約の要求に従って集約の引数が処理されることを指定します。
つまり、最後のいくつかの引数が、集約される(WITHIN GROUP
の)引数と適合しなければなりません。
HYPOTHETICAL
フラグは実行時の動作には何の影響もなく、集約の引数のデータ型と照合についての解析時の解決にのみ影響します。
CREATE AGGREGATE
のパラメータは、任意の順番で記述することができます。上記の順番で記述する必要はありません。
サポート関数名を指定するパラメータでは、必要なら、SFUNC = public.sum
のようにスキーマ名を書くことができます。
しかし、引数の型をそこに書くことはできません。
サポート関数の引数の型は、他のパラメータにより決定されるからです。
通常PostgreSQL関数は入力値を変更しない純粋な関数であることが期待されます。 しかし集約のコンテキストで使用される場合には、集約遷移関数は、これを偽ってその状態遷移引数を直接変更することが許されます。 これにより、遷移状態の新しいコピーを都度作るのに比べると、かなりの性能上の利点が期待できます。
同様に、集約最終関数は入力値を変更しない純粋な関数であることが期待されますが、状態遷移引数を変更するのを避けることが実用的でないことがあります。
そうした挙動はFINALFUNC_MODIFY
引数を使って宣言しなければなりません。
READ_WRITE
値は、最終関数が遷移状態を明示されていない方法で変更することを示します。
この値はwindow関数として集約を使うことを回避します。
また、同じ入力値と遷移関数を共有する集約の遷移状態をマージすることを回避します。
SHAREABLE
値は、遷移関数が最終関数の後に適用できないが、最終関数の複数回の呼び出しを終了遷移状態値に適用できることを示します。
この値はwindow関数として集約を使うことを回避しますが、遷移状態のマージを許容します。
(つまり、ここでの最適化の眼目は、同じ最終関数を繰り返し適用することではなく、異なる最終関数を同じ終了遷移状態値に適用することです。
これは最終関数のうちREAD_WRITE
と印付けられているものが一つもない限り許容されます。)
集約が移動集約モードをサポートしていると、移動フレームの開始のあるウィンドウ(つまり、UNBOUNDED PRECEDING
以外のフレーム開始モード)として集約が使われる場合に計算の効率が向上します。
概念的には、前方遷移関数はウィンドウフレームに下から入るときに集約の状態に入力値を追加し、逆遷移関数はフレームを上から抜けるときにそれを取り除きます。
従って、値が取り除かれるときは、必ず追加された時と同じ順番で取り除かれます。
従って、逆遷移関数が実行される時は、いつでも最も早く追加されたけれども、まだ取り除かれていない引き数値を受け取ります。
逆遷移関数は、最も古い行を取り除いた後、現在の状態に少なくとも1行が残ることを前提とできます。
(そうならない場合は、ウィンドウ関数の仕組みは、逆遷移関数を使うのでなく、単純に新しい集約を開始します。)
移動集約モードの前方遷移関数は、新しい集約値としてNULLを返すことは許されません。 逆遷移関数がNULLを返した場合、それは、逆関数がその入力値について状態計算を元に戻すことができなかったと見なされるため、集約の計算は現在のフレームの開始位置からやり直しとなります。 こうすることで、実行中の状態値を元に戻すのが現実的でないということが稀に起こるような場合でも、移動集約モードを使うことができます。
移動集約が実装されていない場合でも、集約は移動フレームを使うことができますが、フレームの開始が移動した場合、PostgreSQLは必ず集約全体を再計算します。 集約が移動集約モードをサポートするかどうかに関わらず、PostgreSQLは移動フレームの終了を再計算なしに処理することができます。 これは、集約の状態に新しい値を追加し続けることで処理できます。 これがwindow関数として集約を利用するためには最終関数が読み出し専用でなければならない理由です。 最終関数は集約の状態値を破壊しないものとされるので、フレームの境界の集合に対して集約結果の値が得られた後でも、集約を続行することが可能です。
順序集合集約の構文では、VARIADIC
を最後の直接パラメータと、最後の集約(WITHIN GROUP
)パラメータの両方について指定することができます。
しかし、現在の実装ではVARIADIC
の使用を2つの方法に制限しています。
1つ目は、順序集合集約では、VARIADIC "any"
のみが利用でき、他のvariadicの配列型は利用できないことです。
2つ目は、最後の直接パラメータがVARIADIC "any"
の場合、集約パラメータは1つだけしか使えず、かつそれもVARIADIC "any"
でなければならない、ということです。
(システムカタログで使われる表現において、これらの2つのパラメータは、1つのVARIADIC "any"
要素に統合されています。
なぜなら、pg_proc
は2つ以上のVARIADIC
パラメータがある関数を表現できないからです。)
仮想集合集約の場合、VARIADIC "any"
パラメータに対応する直接引数は仮想的なパラメータで、それより前のパラメータは、集約引数に対応する制約のない、追加の直接引数となります。
現在は、順序集合集約は、ウィンドウ関数として使うことはできないので、移動集約モードをサポートする必要はありません。
部分集約(並列集約を含む)は現在のところ、順序集約ではサポートされません。
また、部分集約はDISTINCT
あるいはORDER BY
句を含む集約の呼び出しでは決して使われることはありません。
なぜなら、部分集約ではそれらを意味論的にサポートできないからです。
38.12を参照してください。
CREATE AGGREGATE
はPostgreSQLの言語拡張です。
標準SQLには、ユーザ定義の集約関数を使用する機能はありません。