集約関数は入力値の集合から単一の結果を計算します。 表9.51「汎用集約関数」および表9.52「統計処理用の集約関数」に通常の組み込み集約関数を示します。 表9.53「順序集合集約関数」および表9.54「仮想集合集約関数」には組み込みの順序集合集約関数を示します。 表9.55「グループ化演算」には、集約関数と密接に関係するグループ化演算を示します。 集約関数の特殊な構文に関する考察は4.2.7. 集約式で説明されています。 また、初歩的な情報については2.7. 集約関数を参照して下さい。
表9.51 汎用集約関数
関数 | 引数のデータ型 | 戻り値型 | 部分モード | 説明 |
---|---|---|---|---|
array_agg(
| 配列以外の任意の型 | 引数の型の配列 | 不可 | NULLを含めて入力値が結合された配列 |
array_agg(
| 任意の配列型 | 引数と同じデータ型 | 不可 | 入力配列が結合された、1次元高い配列(入力はすべて同じ次元であり、空やNULLではないこと) |
avg(
| smallint 、int 、bigint 、real 、double precision 、numeric 、またはinterval
| 整数型の引数であれば全てnumeric 、浮動小数点の引数であればdouble precision 、それ以外は引数のデータ型と同じ
| 可 | 全ての入力値の平均値(算術平均) |
bit_and(
| smallint 、int 、bigint 、またはbit
| 引数のデータ型と同じ | 可 | 全ての非NULLの入力値のビット積、非NULLの入力値がなければNULL |
bit_or(
| smallint 、int 、bigint 、または
bit
| 引数のデータ型と同じ | 可 | 全ての非NULLの入力値のビット和、非NULLの入力値がなければNULL |
bool_and(
| bool
| bool
| 可 | 全ての入力が真ならば真、そうでなければ偽 |
bool_or(
| bool
| bool
| 可 | 少なくとも1つの入力値が真ならば真。そうでなければ偽 |
count(*)
| bigint | 可 | 入力行の数 | |
count( | 任意の型 | bigint | 可 | expression が非NULL値を持つ入力行の個数
|
every(
| bool
| bool
| 可 | bool_and と等価 |
json_agg(
| any
| json
| 不可 | JSON配列として値を集約 |
jsonb_agg(
| any
| jsonb
| 不可 | 値をJSON配列として集約する |
json_object_agg(
| (any, any)
| json
| 不可 | 名前/値の対をJSONオブジェクトとして集約 |
jsonb_object_agg(
| (any, any)
| jsonb
| 不可 | 名前/値のペアをJSONオブジェクトとして集約 |
max(
| 任意の数値型、文字列型、日付時刻型、ネットワーク型、列挙型、あるいはそれらの配列 | 引数の型と同じ | 可 | 全ての入力値にわたりexpression の最大値
|
min(
| 任意の数値型、文字列型、日付時刻型、ネットワーク型、列挙型、あるいはそれらの配列 | 引数の型と同じ | 可 | 全ての入力値にわたりexpression の最小値
|
string_agg(
| (text , text )または(bytea , bytea )
| 引数と同じ型 | 不可 | 入力された値が指定したデリミタで区切られた一つの文字列に連結されます。 |
sum(
| smallint 、int 、bigint 、real 、double precision 、numeric 、interval またはmoney
| smallint またはint 型の引数であればbigint 、bigint 型の引数であればnumeric 、それ以外は引数のデータ型と同じ
| 可 | 全ての入力値に渡りexpression の和 |
xmlagg(
| xml
| xml
| 不可 | XML値の連結( 9.14.1.7. xmlagg も参照) |
上記の関数は、count
関数を除き、1行も選択されなかった場合NULL値を返すことに注意してください。
特に、行の選択がないsum
関数は、予想されるであろうゼロではなくNULLを返し、そしてarray_agg
は、入力行が存在しない場合に、空配列ではなくNULLを返します。
必要であれば、NULLをゼロまたは空配列と置換する目的でcoalesce
関数を使うことができます。
部分モードをサポートする集約関数は並列集約など、様々な最適化に参加することができます。
bool_and
、bool_or
論理集約関数は標準SQLの集約関数every
、any
またはsome
に対応します。
any
とsome
についてですが、標準の構文には曖昧さがあるようです。
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
ここで、副問い合わせが論理値での1行を返す場合、ANY
は副問い合わせを導入するもの、もしくは集約関数であるものいずれかとみなすことができます。
従って、これらの集約関数に標準の名前を付けることはできません。
他のSQLデータベース管理システムでの作業に親しんだユーザは、count
集約関数がテーブル全体に適用される場合の性能に失望するかも知れません。
SELECT count(*) FROM sometable;
のような問い合わせはテーブルサイズに比例した労力が必要です。PostgreSQLはテーブル全体か、そのテーブルの全ての行を含んだインデックス全体のスキャンを必要とします。
集約関数array_agg
、json_agg
、jsonb_agg
、json_object_agg
、jsonb_object_agg
、string_agg
、およびxmlagg
、そして類似のユーザ定義の集約関数は、入力値の順序に依存した意味のある別の結果値を生成します。
この並び順はデフォルトでは指定されませんが、4.2.7. 集約式に記述されているように、集計呼び出し中にORDER BY
句を書くことで制御可能となります。別の方法として、並び替えられた副問い合わせから入力値を供給することでも上手くいきます。
例をあげます。
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
外側の問い合わせのレベルで結合などの追加処理がある場合、この方法は失敗するかもしれないことに注意して下さい。 なぜなら、集約の計算の前に副問い合わせの出力を並べ替える必要があるかも知れないからです。
統計解析処理によく使用される集約関数を表9.52「統計処理用の集約関数」に示します。
(これらは、より一般的に使用される集約関数との混乱を防ぐために別出ししました。)
説明の部分におけるN
は、すべての入力式が非NULLの入力行の個数を表します。
すべての場合にて、例えばN
が0の時など計算が無意味である場合にはNULLが返されます。
表9.52 統計処理用の集約関数
表9.53「順序集合集約関数」に順序集合集約構文を使う集約関数を示します。 これらの関数は「逆分散」関数として参照されることがあります。
表9.53 順序集合集約関数
表9.53「順序集合集約関数」に列挙された集約はすべて整列された入力内のNULL値を無視します。
fraction
パラメータを取るものでは、fraction(割合)の値は0と1の間でなければなりません。そうでなければエラーが投げられます。
しかしながら、franctionの値がNULLなら単にNULLという結果になります。
表9.54「仮想集合集約関数」に列挙されている集約は、それぞれ9.21. ウィンドウ関数で定義されている同じ名前のウィンドウ関数と関連します。
どの場合も、集約結果は、args
から構築される「仮想的な」行に対して、関連するウィンドウ関数が返す値で、そのような行がsorted_args
から計算されるソートされた行のグループに追加される場合を想定します。
表9.54 仮想集合集約関数
各仮想集合集約に対してargs
で与えられる直接引数のリストは、sorted_args
で与えられる集約された引数の数と型と一致しなければなりません。
ほとんどの組み込み集約とは異なり、この集約はSTRICTではありません、すなわち、NULLを含む入力行を落としません。
NULL値はORDER BY
節で指定されるルールに従って並べられます。
グループ化演算はグループ化セット(7.2.4. GROUPING SETS
、CUBE
、ROLLUP
参照)と一緒に使われ、結果の行を区別するものです。
GROUPING
演算の引数は実際には評価されませんが、関連する問い合わせのGROUP BY
句にある式と正確に一致する必要があります。
最も右側の引数が最下位ビットになるようにビットが割り当てられます。
各ビットは、対応する式が結果の行を生成するグループ化セットのグループ化条件に含まれていれば0、そうでなければ1です。
例えば以下のようになります。
=>
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)