9.20. 集約関数

集約関数は入力値の集合から単一の結果を計算します。 表9.51「汎用集約関数」および表9.52「統計処理用の集約関数」に通常の組み込み集約関数を示します。 表9.53「順序集合集約関数」および表9.54「仮想集合集約関数」には組み込みの順序集合集約関数を示します。 表9.55「グループ化演算」には、集約関数と密接に関係するグループ化演算を示します。 集約関数の特殊な構文に関する考察は4.2.7. 集約式で説明されています。 また、初歩的な情報については2.7. 集約関数を参照して下さい。

表9.51 汎用集約関数

関数引数のデータ型戻り値型説明 
array_agg(expression) 配列以外の任意の型 引数の型の配列 不可NULLを含めて入力値が結合された配列
array_agg(expression) 任意の配列型 引数と同じデータ型 不可入力配列が結合された、1次元高い配列(入力はすべて同じ次元であり、空やNULLではないこと)
avg(expression) smallintintbigintrealdouble precisionnumeric、またはinterval 整数型の引数であれば全てnumeric、浮動小数点の引数であればdouble precision、それ以外は引数のデータ型と同じ 全ての入力値の平均値(算術平均)
bit_and(expression) smallintintbigint、またはbit 引数のデータ型と同じ 全ての非NULLの入力値のビット積、非NULLの入力値がなければNULL
bit_or(expression) smallintintbigint、または bit 引数のデータ型と同じ 全ての非NULLの入力値のビット和、非NULLの入力値がなければNULL
bool_and(expression) bool bool 全ての入力が真ならば真、そうでなければ偽
bool_or(expression) bool bool 少なくとも1つの入力値が真ならば真。そうでなければ偽
count(*)  bigint入力行の数
count(expression)任意の型bigint expressionが非NULL値を持つ入力行の個数
every(expression) bool bool bool_andと等価
json_agg(expression) any json 不可JSON配列として値を集約
jsonb_agg(expression) any jsonb 不可値をJSON配列として集約する
json_object_agg(name, value) (any, any) json 不可名前/値の対をJSONオブジェクトとして集約
jsonb_object_agg(name, value) (any, any) jsonb 不可名前/値のペアをJSONオブジェクトとして集約
max(expression) 任意の数値型、文字列型、日付時刻型、ネットワーク型、列挙型、あるいはそれらの配列引数の型と同じ 全ての入力値にわたりexpressionの最大値
min(expression) 任意の数値型、文字列型、日付時刻型、ネットワーク型、列挙型、あるいはそれらの配列引数の型と同じ 全ての入力値にわたりexpressionの最小値
string_agg(expression, delimiter) (text, text)または(bytea, bytea) 引数と同じ型 不可入力された値が指定したデリミタで区切られた一つの文字列に連結されます。
sum(expression) smallintintbigintrealdouble precisionnumericintervalまたはmoney smallintまたはint型の引数であればbigintbigint型の引数であればnumeric、それ以外は引数のデータ型と同じ 全ての入力値に渡りexpressionの和
xmlagg(expression) xml xml 不可XML値の連結( 9.14.1.7. xmlaggも参照)

上記の関数は、count関数を除き、1行も選択されなかった場合NULL値を返すことに注意してください。 特に、行の選択がないsum関数は、予想されるであろうゼロではなくNULLを返し、そしてarray_aggは、入力行が存在しない場合に、空配列ではなくNULLを返します。 必要であれば、NULLをゼロまたは空配列と置換する目的でcoalesce関数を使うことができます。

部分モードをサポートする集約関数は並列集約など、様々な最適化に参加することができます。

注記

bool_andbool_or論理集約関数は標準SQLの集約関数everyanyまたはsomeに対応します。 anysomeについてですが、標準の構文には曖昧さがあるようです。

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

ここで、副問い合わせが論理値での1行を返す場合、ANYは副問い合わせを導入するもの、もしくは集約関数であるものいずれかとみなすことができます。 従って、これらの集約関数に標準の名前を付けることはできません。

注記

他のSQLデータベース管理システムでの作業に親しんだユーザは、count集約関数がテーブル全体に適用される場合の性能に失望するかも知れません。

SELECT count(*) FROM sometable;

のような問い合わせはテーブルサイズに比例した労力が必要です。PostgreSQLはテーブル全体か、そのテーブルの全ての行を含んだインデックス全体のスキャンを必要とします。

集約関数array_aggjson_aggjsonb_aggjson_object_aggjsonb_object_aggstring_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 統計処理用の集約関数

関数引数の型戻り値の型部分モード説明
corr(Y, X) double precision double precision 相関係数
covar_pop(Y, X) double precision double precision 母共分散
covar_samp(Y, X) double precision double precision 標本共分散
regr_avgx(Y, X) double precision double precision 独立変数の平均値 (sum(X)/N)
regr_avgy(Y, X) double precision double precision 従属変数の平均値 (sum(Y)/N)
regr_count(Y, X) double precision bigint 両式が非NULLとなる入力行の個数
regr_intercept(Y, X) double precision double precision (X, Y)の組み合わせで決まる、最小二乗法による線形方程式のY切片
regr_r2(Y, X) double precision double precision 相関係数の二乗
regr_slope(Y, X) double precision double precision X, Y)の組み合わせで決まる、最小二乗法による線型方程式の傾き
regr_sxx(Y, X) double precision double precision sum(X^2) - sum(X)^2/N (従属変数の二乗和
regr_sxy(Y, X) double precision double precision sum(X*Y) - sum(X) * sum(Y)/N (従属変数と独立変数の積の和
regr_syy(Y, X) double precision double precision sum(Y^2) - sum(Y)^2/N (独立変数の二乗和
stddev(expression) smallintintbigintrealdouble precision、またはnumeric 浮動小数点型の引数ではdouble precision。それ以外ではnumeric stddev_sampの歴史的な別名
stddev_pop(expression) smallintintbigintrealdouble precision、またはnumeric 浮動小数点型の引数ではdouble precision。それ以外ではnumeric 入力値に対する母標準偏差
stddev_samp(expression) smallintintbigintrealdouble precision、またはnumeric 浮動小数点型の引数ではdouble precision。それ以外ではnumeric 入力値に対する標本標準偏差
variance(expression) smallintintbigintrealdouble precision、またはnumeric 浮動小数点型の引数ではdouble precision。それ以外ではnumeric var_sampの歴史的な別名
var_pop(expression) smallintintbigintrealdouble precision、またはnumeric 浮動小数点型の引数ではdouble precision。それ以外ではnumeric 入力値に対する母分散(母標準偏差の二乗)
var_samp(expression) smallintintbigintrealdouble precision、またはnumeric 浮動小数点型の引数ではdouble precision。それ以外ではnumeric 入力値に対する標本分散(標本標準偏差の二乗)

表9.53「順序集合集約関数」順序集合集約構文を使う集約関数を示します。 これらの関数は逆分散関数として参照されることがあります。

表9.53 順序集合集約関数

関数直接引数型集約された引数型戻り値型部分モード説明
mode() WITHIN GROUP (ORDER BY sort_expression) ソート可能な型 ソート式と同じ 不可 入力の最頻値を返す(複数の同じ度数の結果があれば、任意に選んだ最初のもの)
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) double precision double precisionまたはinterval ソート式と同じ 不可 連続百分位数: 入力項目を並べて必要であれば隣り合うものを補間して指定された割合に対応する値を返す
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression) double precision[] double precisionまたはinterval ソート式の型の配列 不可 複数の連続百分位数: 各非NULL要素をその百分位数に対応する値で置き換えて、fractionsパラメータの形に一致する結果の配列を返す
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) double precision ソート可能な型 ソート式と同じ 不可 離散百分位数: 並べた時のその位置が指定された割合と等しいもしくは越える最初の入力値を返す
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression) double precision[] ソート可能な型 ソート式の型の配列 不可 複数の離散百分位数: 各非NULL要素をその百分位数に対応する値で置き換えて、fractionsパラメータの形に一致する結果の配列を返す

表9.53「順序集合集約関数」に列挙された集約はすべて整列された入力内のNULL値を無視します。 fractionパラメータを取るものでは、fraction(割合)の値は0と1の間でなければなりません。そうでなければエラーが投げられます。 しかしながら、franctionの値がNULLなら単にNULLという結果になります。

表9.54「仮想集合集約関数」に列挙されている集約は、それぞれ9.21. ウィンドウ関数で定義されている同じ名前のウィンドウ関数と関連します。 どの場合も、集約結果は、argsから構築される仮想的な行に対して、関連するウィンドウ関数が返す値で、そのような行がsorted_argsから計算されるソートされた行のグループに追加される場合を想定します。

表9.54 仮想集合集約関数

関数直接引数型集約された引数型戻り値型部分モード説明
rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC "any" VARIADIC "any" bigint 不可 重複する行のギャップを含む仮想の行の順位
dense_rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC "any" VARIADIC "any" bigint 不可 ギャップを含まない仮想の行の順位
percent_rank(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC "any" VARIADIC "any" double precision 不可 仮想の行の相対順位、0から1まで
cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC "any" VARIADIC "any" double precision 不可 仮想の行の相対順位、1/Nから1まで

各仮想集合集約に対してargsで与えられる直接引数のリストは、sorted_argsで与えられる集約された引数の数と型と一致しなければなりません。 ほとんどの組み込み集約とは異なり、この集約はSTRICTではありません、すなわち、NULLを含む入力行を落としません。 NULL値はORDER BY節で指定されるルールに従って並べられます。

表9.55 グループ化演算

関数戻り値型説明
GROUPING(args...) integer どの引数が現在のグループ化セットに含まれないかを示す整数のビットマスク

グループ化演算はグループ化セット(7.2.4. GROUPING SETSCUBEROLLUP参照)と一緒に使われ、結果の行を区別するものです。 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)