説明

<command>CREATE AGGREGATE</command> defines a new aggregate function. <command>CREATE OR REPLACE AGGREGATE</command> will either define a new aggregate function or replace an existing definition. Some basic and commonly-used aggregate functions are included with the distribution; they are documented in <xref linkend="functions-aggregate"/>. If one defines new types or needs an aggregate function not already provided, then <command>CREATE AGGREGATE</command> can be used to provide the desired features. CREATE AGGREGATEは、新しい集約関数を定義します。 CREATE OR REPLACE AGGREGATEは、新しい集約関数を定義するか、既存の定義を置き換えます。 配布物には基本的、かつ、よく使用される集約関数がいくつか含まれています。これらの集約関数については、9.21に文書化されています。 新しい型を定義する場合、またはまだ提供されていない集約関数が必要な場合、必要な機能を実現するためにCREATE AGGREGATEを使うことができます。

When replacing an existing definition, the argument types, result type, and number of direct arguments may not be changed. Also, the new definition must be of the same kind (ordinary aggregate, ordered-set aggregate, or hypothetical-set aggregate) as the old one. 既存の定義を置き換える場合には、引数の型、結果の型、直接引数の数を変えることはできません。 また、新しい定義は古いものと同じ種類(通常の集約、順序集合集約、仮想集合集約)でなければなりません。

If a schema name is given (for example, <literal>CREATE AGGREGATE myschema.myagg ...</literal>) then the aggregate function is created in the specified schema. Otherwise it is created in the current schema. スキーマ名が付けられている場合（例えば、CREATE AGGREGATE myschema.myagg ...）、集約関数は指定されたスキーマに作成されます。 スキーマ名がなければ、集約関数は現在のスキーマに作成されます。

An aggregate function is identified by its name and input data type(s). Two aggregates in the same schema can have the same name if they operate on different input types. The name and input data type(s) of an aggregate must also be distinct from the name and input data type(s) of every ordinary function in the same schema. This behavior is identical to overloading of ordinary function names (see <xref linkend="sql-createfunction"/>). 集約関数は名前と入力データ型(複数可)の組み合わせによって識別されます。 演算の対象となる入力データ型が異なっていれば、同じスキーマ内に同じ名前の集約関数があっても構いません。 1つのスキーマ内では、集約関数の名前と入力データ型の組み合わせは、通常の関数の名前と入力データ型の組み合わせと異なる必要があります。 この動作は通常の関数名についてオーバーロードする時と同じです(CREATE FUNCTIONを参照してください)。

A simple aggregate function is made from one or two ordinary functions: a state transition function <replaceable class="parameter">sfunc</replaceable>, and an optional final calculation function <replaceable class="parameter">ffunc</replaceable>. These are used as follows: 単純な集約関数は1つか2つの通常の関数から作られます。 状態遷移関数sfuncと最終計算関数ffunc(省略可能)です。 これらは以下のように使われます。

<replaceable class="parameter">sfunc</replaceable>( internal-state, next-data-values ) &#45;&#45;-> next-internal-state
<replaceable class="parameter">ffunc</replaceable>( internal-state ) &#45;&#45;-> aggregate-value

sfunc( 内部状態, 次のデータ値 ) ---> 次の内部状態
ffunc( 内部状態 ) ---> 集約の結果

<productname>PostgreSQL</productname> creates a temporary variable of data type <replaceable class="parameter">stype</replaceable> to hold the current internal state of the aggregate. At each input row, the aggregate argument value(s) are calculated and the state transition function is invoked with the current state value and the new argument value(s) to calculate a new internal state value. After all the rows have been processed, the final function is invoked once to calculate the aggregate's return value. If there is no final function then the ending state value is returned as-is. PostgreSQLは、集約の現在の内部状態を保持するstypeデータ型の一時変数を作成します。 それぞれの入力行に対して、集約引数の値が計算され、現在の状態値と新しい引数値で状態遷移関数が呼び出され、新しい内部状態変数が計算されます。 全ての行が処理されると、最終関数が1回呼び出され、集約の出力値が計算されます。 最終関数がない場合は、終了時の状態値がそのまま返されます。

An aggregate function can provide an initial condition, that is, an initial value for the internal state value. This is specified and stored in the database as a value of type <type>text</type>, but it must be a valid external representation of a constant of the state value data type. If it is not supplied then the state value starts out null. 集約関数は、初期条件、つまり内部状態値の初期値を提供することができます。 これはtext型の値としてデータベースに格納されますが、状態値データ型の定数として有効な外部表現でなければいけません。 初期状態が与えられていない場合、状態値はNULLから始まります。

If the state transition function is declared <quote>strict</quote>, then it cannot be called with null inputs. With such a transition function, aggregate execution behaves as follows. Rows with any null input values are ignored (the function is not called and the previous state value is retained). If the initial state value is null, then at the first row with all-nonnull input values, the first argument value replaces the state value, and the transition function is invoked at each subsequent row with all-nonnull input values. This is handy for implementing aggregates like <function>max</function>. Note that this behavior is only available when <replaceable class="parameter">state_data_type</replaceable> is the same as the first <replaceable class="parameter">arg_data_type</replaceable>. When these types are different, you must supply a nonnull initial condition or use a nonstrict transition function. 状態遷移関数が「strict」と宣言されている場合、NULLを入力値にして呼び出すことはできません。 そのような遷移関数では、集約は次のように実行されます。 NULL入力値を持つ行は無視されます。 （関数は呼び出されず、前回の状態値が保持されます。） 初期状態値がNULLである場合、初めて入力行がすべて非NULL入力値であった時にその最初の引数の値で状態値を置き換え、以後、すべて非NULL入力値のそれぞれの行について、遷移関数が呼び出されます。 このような動作は、maxのような集約を実装するには便利です。 ただし、state_data_typeが最初のarg_data_typeと同じ時にのみ有効であることに注意してください。 これらの型が異なる時は、非NULL初期値を供給するか、strictでない遷移関数を使わなければいけません。

If the state transition function is not strict, then it will be called unconditionally at each input row, and must deal with null inputs and null state values for itself. This allows the aggregate author to have full control over the aggregate's handling of null values. 状態遷移関数がstrictでない場合は、それぞれの入力行に対してその関数が無条件に呼び出されるので、NULL入力とNULL状態値を自分で処理しなければいけません。 これは、関数の作成者が、集約関数におけるNULL値の扱いを完全に制御できることを意味します。

If the final function is declared <quote>strict</quote>, then it will not be called when the ending state value is null; instead a null result will be returned automatically. (Of course this is just the normal behavior of strict functions.) In any case the final function has the option of returning a null value. For example, the final function for <function>avg</function> returns null when it sees there were zero input rows. 最終関数が「strict」と宣言されていると、終了状態値がNULLの時は、最終関数が呼び出されません。 その場合、NULLという結果が自動的に出力されます （もちろんこれは、strictな関数の一般的な動作に過ぎません）。 いずれにせよ、最終関数はNULLを返すことができます。 例えば、avgの最終関数は、入力が0行だとわかるとNULLを返します。

Sometimes it is useful to declare the final function as taking not just the state value, but extra parameters corresponding to the aggregate's input values. The main reason for doing this is if the final function is polymorphic and the state value's data type would be inadequate to pin down the result type. These extra parameters are always passed as NULL (and so the final function must not be strict when the <literal>FINALFUNC_EXTRA</literal> option is used), but nonetheless they are valid parameters. The final function could for example make use of <function>get_fn_expr_argtype</function> to identify the actual argument type in the current call. 最終関数を、状態値だけでなく、集約の入力値に対応する追加パラメータも取るように宣言すると便利なことがあります。 こうすることの主な理由に、最終関数が多様型で、状態値のデータ型だけでは結果の型を決定するのに不十分である、ということがあります。 これらの追加パラメータは必ずNULLとして渡されます(従ってFINALFUNC_EXTRAオプションが使われている場合、最終関数はstrictであってはいけません)が、それでも有効なパラメータです。 最終関数は、現在の呼び出しでの実際の引数の型を特定するために、例えばget_fn_expr_argtypeを使うことができます。

An aggregate can optionally support <firstterm>moving-aggregate mode</firstterm>, as described in <xref linkend="xaggr-moving-aggregates"/>. This requires specifying the <literal>MSFUNC</literal>, <literal>MINVFUNC</literal>, and <literal>MSTYPE</literal> parameters, and optionally the <literal>MSSPACE</literal>, <literal>MFINALFUNC</literal>, <literal>MFINALFUNC_EXTRA</literal>, <literal>MFINALFUNC_MODIFY</literal>, and <literal>MINITCOND</literal> parameters. Except for <literal>MINVFUNC</literal>, these parameters work like the corresponding simple-aggregate parameters without <literal>M</literal>; they define a separate implementation of the aggregate that includes an inverse transition function. 集約は、36.12.1に記述されているように移動集約モードをサポートすることができます。 このためには、MSFUNC、MINVFUNC、MSTYPEのパラメータを指定する必要があり、またオプションでMSSPACE、MFINALFUNC、MFINALFUNC_EXTRA、MFINALFUNC_MODIFY、MINITCONDのパラメータを指定できます。 MINVFUNCを除き、これらのパラメータは、Mのない単純集約の対応するパラメータのように動作しますが、集約について逆変換関数を含む別の実装を定義します。

The syntax with <literal>ORDER BY</literal> in the parameter list creates a special type of aggregate called an <firstterm>ordered-set aggregate</firstterm>; or if <literal>HYPOTHETICAL</literal> is specified, then a <firstterm>hypothetical-set aggregate</firstterm> is created. These aggregates operate over groups of sorted values in order-dependent ways, so that specification of an input sort order is an essential part of a call. Also, they can have <firstterm>direct</firstterm> arguments, which are arguments that are evaluated only once per aggregation rather than once per input row. Hypothetical-set aggregates are a subclass of ordered-set aggregates in which some of the direct arguments are required to match, in number and data types, the aggregated argument columns. This allows the values of those direct arguments to be added to the collection of aggregate-input rows as an additional <quote>hypothetical</quote> row. パラメータのリストにORDER BYを含む構文は、順序集合集約と呼ばれる特別な種類の集約を作ります。 またHYPOTHETICALが指定されている場合は、仮想集合集約が作られます。 これらの集約は、ソートされた値のグループに対して、順序に依存した方法で作用するため、入力についてのソート順の指定は、呼び出しにおける本質的な部分になります。 また、これらの集約は直接引数をとることができます。 直接引数は、行毎に一度ではなく、集約に対して一度だけ評価されます。 仮想集合集約は、順序集合集約のサブクラスで、直接引数のいくつかが、集約される引数の列と、数とデータ型についてマッチする必要があります。 これにより、直接引数の値を、「仮想的な」行として、集約の入力行の集合に加えることができます。

An aggregate can optionally support <firstterm>partial aggregation</firstterm>, as described in <xref linkend="xaggr-partial-aggregates"/>. This requires specifying the <literal>COMBINEFUNC</literal> parameter. If the <replaceable class="parameter">state_data_type</replaceable> is <type>internal</type>, it's usually also appropriate to provide the <literal>SERIALFUNC</literal> and <literal>DESERIALFUNC</literal> parameters so that parallel aggregation is possible. Note that the aggregate must also be marked <literal>PARALLEL SAFE</literal> to enable parallel aggregation. 36.12.4で説明されている通り、集約では部分集約をサポートすることができます。 このためにはCOMBINEFUNCパラメータを指定する必要があります。 state_data_typeがinternalの場合、通常はSERIALFUNCおよびDESERIALFUNCパラメータも提供して、並列集約を可能にするのが適切でしょう。 並列集約を可能にするには、集約にPARALLEL SAFEの印をつける必要もあることに注意してください。

Aggregates that behave like <function>MIN</function> or <function>MAX</function> can sometimes be optimized by looking into an index instead of scanning every input row. If this aggregate can be so optimized, indicate it by specifying a <firstterm>sort operator</firstterm>. The basic requirement is that the aggregate must yield the first element in the sort ordering induced by the operator; in other words: MINやMAXのような振舞いをする集約では、すべての入力行をスキャンせずにインデックスを検索することで最適化できることがあります。 このように最適化される集約の場合、ソート演算子を指定することで明示してください。 その演算子で生成されるソート順で集約の最初の要素が生成されなければならないということが基本的な必要条件です。 言い換えると、

SELECT agg(col) FROM tab;

must be equivalent to: が以下と同じでなければならないということです。

SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;

Further assumptions are that the aggregate ignores null inputs, and that it delivers a null result if and only if there were no non-null inputs. Ordinarily, a data type's <literal>&lt;</literal> operator is the proper sort operator for <function>MIN</function>, and <literal>&gt;</literal> is the proper sort operator for <function>MAX</function>. Note that the optimization will never actually take effect unless the specified operator is the <quote>less than</quote> or <quote>greater than</quote> strategy member of a B-tree index operator class. 更に、集約がNULL入力を無視すること、および、NULL以外の入力がまったくなかった時にのみNULLという結果を返すことも前提となります。 通常、データ型の<演算子はMINのソート演算子として、また、>演算子はMAXのソート演算子として適切です。 指定した演算子がB-treeインデックス演算子クラスの「より小さい」ストラテジか「より大きい」ストラテジのメンバでない限り、最適化が実際には効果がないことに注意してください。

To be able to create an aggregate function, you must have <literal>USAGE</literal> privilege on the argument types, the state type(s), and the return type, as well as <literal>EXECUTE</literal> privilege on the supporting functions. 集約関数を作成するためには、引数の型、状態の型、戻り値の型に対するUSAGE権限およびサポート関数に対するEXECUTE権限を持たなければなりません。

パラメータ

The parameters of <command>CREATE AGGREGATE</command> can be written in any order, not just the order illustrated above. CREATE AGGREGATEのパラメータは、任意の順番で記述することができます。上記の順番で記述する必要はありません。

注釈

In parameters that specify support function names, you can write a schema name if needed, for example <literal>SFUNC = public.sum</literal>. Do not write argument types there, however &mdash; the argument types of the support functions are determined from other parameters. サポート関数名を指定するパラメータでは、必要なら、SFUNC = public.sumのようにスキーマ名を書くことができます。 しかし、引数の型をそこに書くことはできません。 サポート関数の引数の型は、他のパラメータにより決定されるからです。

Ordinarily, PostgreSQL functions are expected to be true functions that do not modify their input values. However, an aggregate transition function, <emphasis>when used in the context of an aggregate</emphasis>, is allowed to cheat and modify its transition-state argument in place. This can provide substantial performance benefits compared to making a fresh copy of the transition state each time. 通常PostgreSQL関数は入力値を変更しない純粋な関数であることが期待されます。 しかし集約のコンテキストで使用される場合には、集約遷移関数は、これを偽ってその状態遷移引数を直接変更することが許されます。 これにより、遷移状態の新しいコピーを都度作るのに比べると、かなりの性能上の利点が期待できます。

Likewise, while an aggregate final function is normally expected not to modify its input values, sometimes it is impractical to avoid modifying the transition-state argument. Such behavior must be declared using the <literal>FINALFUNC_MODIFY</literal> parameter. The <literal>READ_WRITE</literal> value indicates that the final function modifies the transition state in unspecified ways. This value prevents use of the aggregate as a window function, and it also prevents merging of transition states for aggregate calls that share the same input values and transition functions. The <literal>SHAREABLE</literal> value indicates that the transition function cannot be applied after the final function, but multiple final-function calls can be performed on the ending transition state value. This value prevents use of the aggregate as a window function, but it allows merging of transition states. (That is, the optimization of interest here is not applying the same final function repeatedly, but applying different final functions to the same ending transition state value. This is allowed as long as none of the final functions are marked <literal>READ_WRITE</literal>.) 同様に、集約最終関数は入力値を変更しない純粋な関数であることが期待されますが、状態遷移引数を変更するのを避けることが実用的でないことがあります。 そうした挙動はFINALFUNC_MODIFY引数を使って宣言しなければなりません。 READ_WRITE値は、最終関数が遷移状態を明示されていない方法で変更することを示します。 この値はwindow関数として集約を使うことを回避します。 また、同じ入力値と遷移関数を共有する集約の遷移状態をマージすることを回避します。 SHAREABLE値は、遷移関数が最終関数の後に適用できないが、最終関数の複数回の呼び出しを終了遷移状態値に適用できることを示します。 この値はwindow関数として集約を使うことを回避しますが、遷移状態のマージを許容します。 （つまり、ここでの最適化の眼目は、同じ最終関数を繰り返し適用することではなく、異なる最終関数を同じ終了遷移状態値に適用することです。 これは最終関数のうちREAD_WRITEと印付けられているものが一つもない限り許容されます。）

If an aggregate supports moving-aggregate mode, it will improve calculation efficiency when the aggregate is used as a window function for a window with moving frame start (that is, a frame start mode other than <literal>UNBOUNDED PRECEDING</literal>). Conceptually, the forward transition function adds input values to the aggregate's state when they enter the window frame from the bottom, and the inverse transition function removes them again when they leave the frame at the top. So, when values are removed, they are always removed in the same order they were added. Whenever the inverse transition function is invoked, it will thus receive the earliest added but not yet removed argument value(s). The inverse transition function can assume that at least one row will remain in the current state after it removes the oldest row. (When this would not be the case, the window function mechanism simply starts a fresh aggregation, rather than using the inverse transition function.) 集約が移動集約モードをサポートしていると、移動フレームの開始のあるウィンドウ（つまり、UNBOUNDED PRECEDING以外のフレーム開始モード）として集約が使われる場合に計算の効率が向上します。 概念的には、前方遷移関数はウィンドウフレームに下から入るときに集約の状態に入力値を追加し、逆遷移関数はフレームを上から抜けるときにそれを取り除きます。 従って、値が取り除かれるときは、必ず追加された時と同じ順番で取り除かれます。 従って、逆遷移関数が実行される時は、いつでも最も早く追加されたけれども、まだ取り除かれていない引き数値を受け取ります。 逆遷移関数は、最も古い行を取り除いた後、現在の状態に少なくとも1行が残ることを前提とできます。 (そうならない場合は、ウィンドウ関数の仕組みは、逆遷移関数を使うのでなく、単純に新しい集約を開始します。)

The forward transition function for moving-aggregate mode is not allowed to return NULL as the new state value. If the inverse transition function returns NULL, this is taken as an indication that the inverse function cannot reverse the state calculation for this particular input, and so the aggregate calculation will be redone from scratch for the current frame starting position. This convention allows moving-aggregate mode to be used in situations where there are some infrequent cases that are impractical to reverse out of the running state value. 移動集約モードの前方遷移関数は、新しい集約値としてNULLを返すことは許されません。 逆遷移関数がNULLを返した場合、それは、逆関数がその入力値について状態計算を元に戻すことができなかったと見なされるため、集約の計算は現在のフレームの開始位置からやり直しとなります。 こうすることで、実行中の状態値を元に戻すのが現実的でないということが稀に起こるような場合でも、移動集約モードを使うことができます。

If no moving-aggregate implementation is supplied, the aggregate can still be used with moving frames, but <productname>PostgreSQL</productname> will recompute the whole aggregation whenever the start of the frame moves. Note that whether or not the aggregate supports moving-aggregate mode, <productname>PostgreSQL</productname> can handle a moving frame end without recalculation; this is done by continuing to add new values to the aggregate's state. This is why use of an aggregate as a window function requires that the final function be read-only: it must not damage the aggregate's state value, so that the aggregation can be continued even after an aggregate result value has been obtained for one set of frame boundaries. 移動集約が実装されていない場合でも、集約は移動フレームを使うことができますが、フレームの開始が移動した場合、PostgreSQLは必ず集約全体を再計算します。 集約が移動集約モードをサポートするかどうかに関わらず、PostgreSQLは移動フレームの終了を再計算なしに処理することができます。 これは、集約の状態に新しい値を追加し続けることで処理できます。 これがwindow関数として集約を利用するためには最終関数が読み出し専用でなければならない理由です。 最終関数は集約の状態値を破壊しないものとされるので、フレームの境界の集合に対して集約結果の値が得られた後でも、集約を続行することが可能です。

The syntax for ordered-set aggregates allows <literal>VARIADIC</literal> to be specified for both the last direct parameter and the last aggregated (<literal>WITHIN GROUP</literal>) parameter. However, the current implementation restricts use of <literal>VARIADIC</literal> in two ways. First, ordered-set aggregates can only use <literal>VARIADIC "any"</literal>, not other variadic array types. Second, if the last direct parameter is <literal>VARIADIC "any"</literal>, then there can be only one aggregated parameter and it must also be <literal>VARIADIC "any"</literal>. (In the representation used in the system catalogs, these two parameters are merged into a single <literal>VARIADIC "any"</literal> item, since <structname>pg_proc</structname> cannot represent functions with more than one <literal>VARIADIC</literal> parameter.) If the aggregate is a hypothetical-set aggregate, the direct arguments that match the <literal>VARIADIC "any"</literal> parameter are the hypothetical ones; any preceding parameters represent additional direct arguments that are not constrained to match the aggregated arguments. 順序集合集約の構文では、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"パラメータに対応する直接引数は仮想的なパラメータで、それより前のパラメータは、集約引数に対応する制約のない、追加の直接引数となります。

Currently, ordered-set aggregates do not need to support moving-aggregate mode, since they cannot be used as window functions. 現在は、順序集合集約は、ウィンドウ関数として使うことはできないので、移動集約モードをサポートする必要はありません。

Partial (including parallel) aggregation is currently not supported for ordered-set aggregates. Also, it will never be used for aggregate calls that include <literal>DISTINCT</literal> or <literal>ORDER BY</literal> clauses, since those semantics cannot be supported during partial aggregation. 部分集約（並列集約を含む）は現在のところ、順序集約ではサポートされません。 また、部分集約はDISTINCTあるいはORDER BY句を含む集約の呼び出しでは決して使われることはありません。 なぜなら、部分集約ではそれらを意味論的にサポートできないからです。

例

See <xref linkend="xaggr"/>. 36.12を参照してください。

互換性

<command>CREATE AGGREGATE</command> is a <productname>PostgreSQL</productname> language extension. The SQL standard does not provide for user-defined aggregate functions. CREATE AGGREGATEはPostgreSQLの言語拡張です。 標準SQLには、ユーザ定義の集約関数を使用する機能はありません。

関連項目