| PostgreSQL 9.4.5文書 | |||
|---|---|---|---|
| 前のページ | 上に戻る | 第 9章関数と演算子 | 次のページ |
集約関数は複数の入力値から単一の結果を計算します。 表9-49および表9-50に通常の組み込み集約関数を示します。 表9-51および表9-52には組み込みの順序集合集約関数を示します。 集約関数の特殊な構文に関する考察は項4.2.7で説明されています。 また、初歩的な情報については項2.7を参照して下さい。
表 9-49. 汎用集約関数
| 関数 | 引数のデータ型 | 戻り値型 | 説明 |
|---|---|---|---|
array_agg(expression)
| any | 引数型の配列 | 配列に連結されたNULLを含む入力値 |
avg(expression)
| smallint, int, bigint, real, double precision, numeric, or interval | 整数型の引数であれば全てnumeric、浮動小数点の引数であればdouble precision、それ以外は引数のデータ型と同じ | 全ての入力値の平均値（算術平均） |
bit_and(expression)
| smallint, int, bigint, or bit | 引数のデータ型と同じ | 全ての非NULLの入力値のビット積、非NULLの入力値がなければNULL |
bit_or(expression)
| smallint、int、bigint、または bit | 引数のデータ型と同じ | 全ての非NULLの入力値のビット和、非NULLの入力値がなければNULL |
bool_and(expression)
| bool | bool | 全ての入力が真ならば真、そうでなければ偽 |
bool_or(expression)
| bool | bool | 少なくとも1つの入力値が真ならば真。そうでなければ偽 |
count(*)
| bigint | 入力行の数 | |
count(expression) | any | bigint | expressionが非NULL値を持つ入力行の個数 |
every(expression)
| bool | bool | bool_andと等価 |
json_agg(expression)
| any | json | JSON配列として値を集約 |
json_object_agg(name, value)
| (any, any) | json | 名前/値の対をJSONオブジェクトとして集約 |
max(expression)
| 全ての配列、数値、文字列、または日付時刻型 | 引数の型と同じ | 全ての入力値にわたりexpressionの最大値 |
min(expression)
| 全ての配列、数値、文字列、または日付時刻型 | 引数の型と同じ | 全ての入力値にわたりexpressionの最小値 |
string_agg(expression,
delimiter)
| (text, text)または(bytea, bytea) | 引数と同じ型 | 入力された値が指定したデリミタで区切られた一つの文字列に連結されます。 |
sum(expression)
| smallint、int、bigint、real、double precision、numeric、intervalまたはmoney | smallintまたはint型の引数であればbigint、bigint型の引数であればnumeric、それ以外は引数のデータ型と同じ | 全ての入力値に渡りexpressionの和 |
xmlagg(expression)
| xml | xml | XML値の連結（ 項9.14.1.7も参照） |
上記の関数は、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 ...;ここで、副問い合わせが論理値での１行を返す場合、
ANYは副問い合わせを導入するもの、もしくは集約関数であるものいずれかとみなすことができます。 従って、これらの集約関数に標準の名前を付けることはできません。
注意: 他のSQLデータベース管理システムでの作業に親しんだユーザは、
count集約関数がテーブル全体に適用される場合の性能に失望するかも知れません。SELECT count(*) FROM sometable;のような問い合わせはテーブルサイズに比例した労力が必要です。PostgreSQLはテーブル全体か、そのテーブルの全ての行を含んだインデックス全体のスキャンを必要とします。
集約関数array_agg、json_agg、json_object_agg、string_agg、およびxmlagg、そして類似のユーザ定義の集約関数は、入力値の順序に依存した意味のある別の結果値を生成します。
この並び順はデフォルトでは指定されませんが、項4.2.7に記述されているように、集計呼び出し中にORDER BY句を書くことで制御可能となります。別の方法として、並び替えられた副問い合わせから入力値を供給することでも上手くいきます。
例をあげます。
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
しかしこの構文はSQL標準では許されておらず、他のデータベースシステムに移植性はありません。
統計解析処理によく使用される集約関数を表9-50に示します。 （これらは、より一般的に使用される集約関数との混乱を防ぐために別出ししました。） 説明の部分におけるNは、すべての入力式が非NULLの入力行の個数を表します。 すべての場合にて、例えばNが0の時など計算が無意味である場合にはNULLが返されます。
表 9-50. 統計処理用の集約関数
| 関数 | 引数の型 | 戻り値の型 | 説明 |
|---|---|---|---|
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)
| smallint、int、 bigint、real、double precision、またはnumeric | 浮動小数点型の引数ではdouble precision。それ以外ではnumeric | stddev_sampの歴史的な別名 |
stddev_pop(expression)
| smallint、int、 bigint、real、double precision、またはnumeric | 浮動小数点型の引数ではdouble precision。それ以外ではnumeric | 入力値に対する母標準偏差 |
stddev_samp(expression)
| smallint、int、 bigint、real、double precision、またはnumeric | 浮動小数点型の引数ではdouble precision。それ以外ではnumeric | 入力値に対する標本標準偏差 |
variance(expression)
| smallint、int、 bigint、real、double precision、またはnumeric | 浮動小数点型の引数ではdouble precision。それ以外ではnumeric | var_sampの歴史的な別名 |
var_pop(expression)
| smallint、int、 bigint、real、double precision、またはnumeric | 浮動小数点型の引数ではdouble precision。それ以外ではnumeric | 入力値に対する母分散（母標準偏差の自乗） |
var_samp(expression)
| smallint、int、 bigint、real、double precision、またはnumeric | 浮動小数点型の引数ではdouble precision。それ以外ではnumeric | 入力値に対する標本分散（標本標準偏差の二乗） |
表9-51に順序集合集約構文を使う集約関数を示します。 これらの関数は"逆分散"関数として参照されることがあります。
表 9-51. 順序集合集約関数
| 関数 | 直接引数型 | 集約された引数型 | 戻り値型 | 説明 |
|---|---|---|---|---|
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-51に列挙された集約はすべて整列された入力内のNULL値を無視します。 fractionパラメータを取るものでは、割合値は0と1の間でなければなりません。そうでなければエラーが投げられます。 しかしながら、割合値のNULLは単に結果のNULLを生じます。
表9-52に列挙されている集約は、それぞれ項9.21で定義されている同じ名前のウィンドウ関数と関連します。 それぞれの場合、集約結果は、そのような行がsorted_argsから計算された行の整列されたグループに追加されるのであれば、argsから構成される"仮定の"行のために関連するウィンドウ関数が返す値です。
表 9-52. 仮定集合集約関数
| 関数 | 直接引数型 | 集約された引数型 | 戻り値型 | 説明 |
|---|---|---|---|---|
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で与えられる集約された引数の数と型と一致しなければなりません。 ほとんどの組み込み集約とは異なり、この集約は厳格ではありません、すなわち、NULLを含む入力行を落としません。 NULL値はORDER BY節で指定されるルールに従って並べられます。