<firstterm>Aggregate functions</firstterm> compute a single result from a set of input values. The built-in general-purpose aggregate functions are listed in <xref linkend="functions-aggregate-table"/> while statistical aggregates are in <xref linkend="functions-aggregate-statistics-table"/>. The built-in within-group ordered-set aggregate functions are listed in <xref linkend="functions-orderedset-table"/> while the built-in within-group hypothetical-set ones are in <xref linkend="functions-hypothetical-table"/>. Grouping operations, which are closely related to aggregate functions, are listed in <xref linkend="functions-grouping-table"/>. The special syntax considerations for aggregate functions are explained in <xref linkend="syntax-aggregates"/>. Consult <xref linkend="tutorial-agg"/> for additional introductory information. 集約関数は入力値の集合から単一の結果を計算します。 表 9.59に組み込みの汎用的な集約関数を、表 9.60に統計集約関数を示します。 表 9.61には組み込みのグループ内順序集合集約関数を、一方表 9.62には組み込みのグループ内仮想集合用の順序集約関数を示します。 表 9.63には、集約関数と密接に関係するグループ化演算を示します。 集約関数の特殊な構文に関する考察は4.2.7で説明されています。 また、初歩的な情報については2.7を参照して下さい。
Aggregate functions that support <firstterm>Partial Mode</firstterm> are eligible to participate in various optimizations, such as parallel aggregation. 部分モードをサポートする集約関数は並列集約など、様々な最適化に有用です。
表9.59 汎用集約関数
Function 関数 Description 説明 | 部分モード |
|---|---|
Returns an arbitrary value from the non-null input values. NULL以外の入力値から任意の値を返します。 | 可 |
Collects all the input values, including nulls, into an array. NULLも含めてすべての入力値を収集して配列に格納します。 | 可 |
Concatenates all the input arrays into an array of one higher dimension. (The inputs must all have the same dimensionality, and cannot be empty or null.) すべての入力配列を結合して次元が1高い配列に格納します。 （入力配列はすべて同じ次元数を持ち、空もしくはNULLであってはいけません。） | 可 |
|
Computes the average (arithmetic mean) of all the non-null input values. すべての非NULL入力値の平均（算術平均）を計算します。 | 可 |
Computes the bitwise AND of all non-null input values. 全ての非NULLの入力値のビット積を計算します。 | 可 |
Computes the bitwise OR of all non-null input values. 全ての非NULLの入力値のビット和を計算します。 | 可 |
Computes the bitwise exclusive OR of all non-null input values. Can be useful as a checksum for an unordered set of values. すべての非NULL入力値のビット毎の排他的論理和を計算します。 順序付けられない値の集合のチェックサムとして有用かもしれません。 | 可 |
Returns true if all non-null input values are true, otherwise false. 全ての入力が真ならば真、そうでなければ偽を返します。 | 可 |
|
Returns true if any non-null input value is true, otherwise false. 入力のどれかが真ならば真、そうでなければ偽を返します。 | 可 |
|
Computes the number of input rows. 入力行数を返します。 | 可 |
Computes the number of input rows in which the input value is not null. 非NULLの入力行数を返します。 | 可 |
|
This is the SQL standard's equivalent to <function>bool_and</function>.
これはSQL標準の | 可 |
Collects all the input values, including nulls, into a JSON array.
Values are converted to JSON as per <function>to_json</function>
or <function>to_jsonb</function>.
NULLも含めてすべての入力値を収集し、JSON配列に格納します。
入力は | 非 |
Behaves like <function>json_object</function><!-- xref -->, but as an
aggregate function, so it only takes one
<replaceable>key_expression</replaceable> and one
<replaceable>value_expression</replaceable> parameter.
| 非 |
Collects all the key/value pairs into a JSON object. Key arguments
are coerced to text; value arguments are converted as per
<function>to_json</function> or <function>to_jsonb</function>.
Values can be null, but keys cannot.
すべてのキー／値ペアをJSONオブジェクトに格納します。
キー引数はテキストに変換されます。値引数は | 非 |
Collects all the key/value pairs into a JSON object. Key arguments
are coerced to text; value arguments are converted as per
<function>to_json</function> or <function>to_jsonb</function>.
The <parameter>key</parameter> can not be null. If the
<parameter>value</parameter> is null then the entry is skipped,
すべてのキー／値ペアをJSONオブジェクトに格納します。
キー引数はテキストに変換されます。値は | 非 |
Collects all the key/value pairs into a JSON object. Key arguments
are coerced to text; value arguments are converted as per
<function>to_json</function> or <function>to_jsonb</function>.
Values can be null, but keys cannot.
If there is a duplicate key an error is thrown.
すべてのキー／値ペアをJSONオブジェクトに格納します。
キー引数はテキストに変換されます。値は | 非 |
Behaves in the same way as <function>json_array</function>
but as an aggregate function so it only takes one
<replaceable>value_expression</replaceable> parameter.
If <literal>ABSENT ON NULL</literal> is specified, any NULL
values are omitted.
If <literal>ORDER BY</literal> is specified, the elements will
appear in the array in that order rather than in the input order.
| 非 |
Collects all the key/value pairs into a JSON object. Key arguments
are coerced to text; value arguments are converted as per
<function>to_json</function> or <function>to_jsonb</function>.
The <parameter>key</parameter> can not be null. If the
<parameter>value</parameter> is null then the entry is skipped.
If there is a duplicate key an error is thrown.
すべてのキー／値ペアをJSONオブジェクトに格納します。
キー引数はテキストに変換されます。値は | 非 |
Computes the maximum of the non-null input
values. Available for any numeric, string, date/time, or enum type,
as well as <type>inet</type>, <type>interval</type>,
<type>money</type>, <type>oid</type>, <type>pg_lsn</type>,
<type>tid</type>, <type>xid8</type>,
and arrays of any of these types.
非NULL入力値の最大を計算します。
数値、文字列、日時、列挙型および | 可 |
Computes the minimum of the non-null input
values. Available for any numeric, string, date/time, or enum type,
as well as <type>inet</type>, <type>interval</type>,
<type>money</type>, <type>oid</type>, <type>pg_lsn</type>,
<type>tid</type>, <type>xid8</type>,
and arrays of any of these types.
非NULL入力値の最小を計算します。
数値、文字列、日時、列挙型および | 可 |
Computes the union of the non-null input values. 非NULL入力の和を計算します。 | 非 |
Computes the intersection of the non-null input values. 非NULL入力の共通部分を計算します。 | 非 |
Collects all the input values, skipping nulls, into a JSON array.
Values are converted to JSON as per <function>to_json</function>
or <function>to_jsonb</function>.
NULLをスキップして、すべての入力値をJSON配列に収集します。
値は | 非 |
Concatenates the non-null input values into a string. Each value
after the first is preceded by the
corresponding <parameter>delimiter</parameter> (if it's not null).
非NULL入力を結合して文字列に格納します。
最初の値以降、各値の前に | 可 |
|
Computes the sum of the non-null input values. 非NULL入力値の合計を計算します。 | 可 |
|
Concatenates the non-null XML input values (see <xref linkend="functions-xml-xmlagg"/>). 非NULLのXML入力値を結合します。（9.15.1.7参照。） | 非 |
It should be noted that except for <function>count</function>,
these functions return a null value when no rows are selected. In
particular, <function>sum</function> of no rows returns null, not
zero as one might expect, and <function>array_agg</function>
returns null rather than an empty array when there are no input
rows. The <function>coalesce</function> function can be used to
substitute zero or an empty array for null when necessary.
上記の関数は、count関数を除き、1行も選択されなかった場合NULL値を返すことに注意してください。
特に、行の選択がないsum関数は、予想されるであろうゼロではなくNULLを返し、そしてarray_aggは、入力行が存在しない場合に、空配列ではなくNULLを返します。
必要であれば、NULLをゼロまたは空配列と置換する目的でcoalesce関数を使うことができます。
The aggregate functions <function>array_agg</function>,
<function>json_agg</function>, <function>jsonb_agg</function>,
<function>json_agg_strict</function>, <function>jsonb_agg_strict</function>,
<function>json_object_agg</function>, <function>jsonb_object_agg</function>,
<function>json_object_agg_strict</function>, <function>jsonb_object_agg_strict</function>,
<function>json_object_agg_unique</function>, <function>jsonb_object_agg_unique</function>,
<function>json_object_agg_unique_strict</function>,
<function>jsonb_object_agg_unique_strict</function>,
<function>string_agg</function>,
and <function>xmlagg</function>, as well as similar user-defined
aggregate functions, produce meaningfully different result values
depending on the order of the input values. This ordering is
unspecified by default, but can be controlled by writing an
<literal>ORDER BY</literal> clause within the aggregate call, as shown in
<xref linkend="syntax-aggregates"/>.
Alternatively, supplying the input values from a sorted subquery
will usually work. For example:
集約関数array_agg、json_agg、jsonb_agg、json_agg_strict、jsonb_agg_strict、json_object_agg、jsonb_object_agg、json_object_agg_strict、jsonb_object_agg_strict、json_object_agg_unique、jsonb_object_agg_unique、json_object_agg_unique_strict、jsonb_object_agg_unique_strict、string_agg、およびxmlagg、そして類似のユーザ定義の集約関数は、入力値の順序に依存した意味のある別の結果値を生成します。
この並び順はデフォルトでは指定されませんが、4.2.7に記述されているように、集計呼び出し中にORDER BY句を書くことで制御可能となります。別の方法として、並べ替えられた副問い合わせから入力値を供給することでも上手くいきます。
例をあげます。
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
Beware that this approach can fail if the outer query level contains additional processing, such as a join, because that might cause the subquery's output to be reordered before the aggregate is computed. 外側の問い合わせのレベルで結合などの追加処理がある場合、この方法は失敗するかもしれないことに注意して下さい。 なぜなら、集約の計算の前に副問い合わせの出力を並べ替える必要があるかも知れないからです。
The boolean aggregates <function>bool_and</function> and
<function>bool_or</function> correspond to the standard SQL aggregates
<function>every</function> and <function>any</function> or
<function>some</function>.
<productname>PostgreSQL</productname>
supports <function>every</function>, but not <function>any</function>
or <function>some</function>, because there is an ambiguity built into
the standard syntax:
bool_and、bool_or論理集約関数は標準SQLの集約関数every、anyまたはsomeに対応します。
PostgreSQLはeveryをサポートしますが、any、あるいはsomeはサポートしません。
anyとsomeの標準の構文には曖昧さがあるからです。
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
Here <function>ANY</function> can be considered either as introducing
a subquery, or as being an aggregate function, if the subquery
returns one row with a Boolean value.
Thus the standard name cannot be given to these aggregates.
ここで、副問い合わせが論理値での１行を返す場合、ANYは副問い合わせを導入するもの、もしくは集約関数であるものいずれかとみなすことができます。
従って、これらの集約関数に標準の名前を付けることはできません。
Users accustomed to working with other SQL database management
systems might be disappointed by the performance of the
<function>count</function> aggregate when it is applied to the
entire table. A query like:
他のSQLデータベース管理システムでの作業に親しんだユーザは、count集約関数がテーブル全体に適用される場合の性能に失望するかも知れません。
SELECT count(*) FROM sometable;
will require effort proportional to the size of the table: <productname>PostgreSQL</productname> will need to scan either the entire table or the entirety of an index that includes all rows in the table. のような問い合わせはテーブルサイズに比例した労力が必要です。PostgreSQLはテーブル全体か、そのテーブルの全ての行を含んだインデックス全体のスキャンを必要とします。
<xref linkend="functions-aggregate-statistics-table"/> shows
aggregate functions typically used in statistical analysis.
(These are separated out merely to avoid cluttering the listing
of more-commonly-used aggregates.) Functions shown as
accepting <replaceable>numeric_type</replaceable> are available for all
the types <type>smallint</type>, <type>integer</type>,
<type>bigint</type>, <type>numeric</type>, <type>real</type>,
and <type>double precision</type>.
Where the description mentions
<parameter>N</parameter>, it means the
number of input rows for which all the input expressions are non-null.
In all cases, null is returned if the computation is meaningless,
for example when <parameter>N</parameter> is zero.
統計解析処理によく使用される集約関数を表 9.60に示します。
（これらは、より一般的に使用される集約関数との混乱を防ぐために別出ししました。）
numeric_typeを受け付けると表示されている関数は、smallint、integer、bigint、numeric、real、double precisionのすべての型で利用可能です。
説明の部分におけるNは、すべての入力式が非NULLの入力行の個数を表します。
すべての場合にて、例えばNが0の時など計算が無意味である場合にはNULLが返されます。
表9.60 統計処理用の集約関数
<xref linkend="functions-orderedset-table"/> shows some
aggregate functions that use the <firstterm>ordered-set aggregate</firstterm>
syntax. These functions are sometimes referred to as <quote>inverse
distribution</quote> functions. Their aggregated input is introduced by
<literal>ORDER BY</literal>, and they may also take a <firstterm>direct
argument</firstterm> that is not aggregated, but is computed only once.
All these functions ignore null values in their aggregated input.
For those that take a <parameter>fraction</parameter> parameter, the
fraction value must be between 0 and 1; an error is thrown if not.
However, a null <parameter>fraction</parameter> value simply produces a
null result.
表 9.61に順序集合集約構文を使う集約関数を示します。
これらの関数は「逆分散」関数として参照されることがあります。
これらの集約入力はORDER BYで導入され、集約ではないdirect argumentを取ることもでき、一度だけ計算されます。
fractionパラメータを取る関数では、その値は0と1の間でなければなりません。そうでなければエラーが生じます。
ただしNULLのfraction値は単にNULLの結果をもたらします。
表9.61 順序集合集約関数
Each of the <quote>hypothetical-set</quote> aggregates listed in
<xref linkend="functions-hypothetical-table"/> is associated with a
window function of the same name defined in
<xref linkend="functions-window"/>. In each case, the aggregate's result
is the value that the associated window function would have
returned for the <quote>hypothetical</quote> row constructed from
<replaceable>args</replaceable>, if such a row had been added to the sorted
group of rows represented by the <replaceable>sorted_args</replaceable>.
For each of these functions, the list of direct arguments
given in <replaceable>args</replaceable> must match the number and types of
the aggregated arguments given in <replaceable>sorted_args</replaceable>.
Unlike most built-in aggregates, these aggregates are not strict, that is
they do not drop input rows containing nulls. Null values sort according
to the rule specified in the <literal>ORDER BY</literal> clause.
表 9.62に列挙されている「仮想集合」集約は、それぞれ9.22で定義されている同じ名前のウィンドウ関数と関連します。
どの場合も、集約結果は、argsから構築される「仮想的な」行に対して、関連するウィンドウ関数が返す値で、そのような行がsorted_argsから計算されるソートされた行のグループに追加される場合を想定します。
これらの関数に対してargsで与えられる直接引数のリストは、sorted_argsで与えられる集約された引数の数と型に一致しなければなりません。
ほとんどの組み込み集約とは異なり、この集約はSTRICTではありません、すなわち、NULLを含む入力行を落としません。
NULL値はORDER BY節で指定されるルールに従って並べられます。
表9.62 仮想集合集約関数
表9.63 グループ化演算
The grouping operations shown in
<xref linkend="functions-grouping-table"/> are used in conjunction with
grouping sets (see <xref linkend="queries-grouping-sets"/>) to distinguish
result rows. The arguments to the <literal>GROUPING</literal> function
are not actually evaluated, but they must exactly match expressions given
in the <literal>GROUP BY</literal> clause of the associated query level.
For example:
表 9.63で示すグループ化演算はグループ化セット（7.2.4参照）と一緒に使われ、結果の行を区別するものです。
GROUPING関数の引数は実際には評価されませんが、関連する問い合わせのGROUP BY句にある式と正確に一致する必要があります。
例えば以下のようになります。
=>SELECT * FROM items_sold;make | model | sales -------+-------+------- Foo | GT | 10 Foo | Tour | 20 Bar | City | 15 Bar | Sport | 5 (4 rows)=>SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);make | model | grouping | sum -------+-------+----------+----- Foo | GT | 0 | 10 Foo | Tour | 0 | 20 Bar | City | 0 | 15 Bar | Sport | 0 | 5 Foo | | 1 | 30 Bar | | 1 | 20 | | 3 | 50 (7 rows)
Here, the <literal>grouping</literal> value <literal>0</literal> in the
first four rows shows that those have been grouped normally, over both the
grouping columns. The value <literal>1</literal> indicates
that <literal>model</literal> was not grouped by in the next-to-last two
rows, and the value <literal>3</literal> indicates that
neither <literal>make</literal> nor <literal>model</literal> was grouped
by in the last row (which therefore is an aggregate over all the input
rows).
ここで、最初の4行のグループ化値0はこれらがグループ化列に対して正常にグループ化されたことを示します。
値1はmodelが最後とその一つ前の行ではグループ化されなかったことを、値3はmakeもmodelも最後の行でグループ化されなかったことを意味します（ですから最後の行はすべての入力行に対する集約になっています）。