集約関数は入力値の集合から単一の結果を計算します。
表 9.59に組み込みの汎用的な集約関数を、表 9.60に統計集約関数を示します。
表 9.61には組み込みのグループ内順序集合集約関数を、一方表 9.62には組み込みのグループ内仮想集合用の順序集約関数を示します。
表 9.63には、集約関数と密接に関係するグループ化演算を示します。
集約関数の特殊な構文に関する考察は4.2.7で説明されています。
また、初歩的な情報については2.7を参照して下さい。
部分モードをサポートする集約関数は並列集約など、様々な最適化に有用です。
表9.59 汎用集約関数
関数
説明
| 部分モード |
|---|
any_value ( anyelement )
→ same as input type
NULL以外の入力値から任意の値を返します。
| 可 |
array_agg ( anynonarray )
→ anyarray
NULLも含めてすべての入力値を収集して配列に格納します。
| 可 |
array_agg ( anyarray )
→ anyarray
すべての入力配列を結合して次元が1高い配列に格納します。
（入力配列はすべて同じ次元数を持ち、空もしくはNULLであってはいけません。）
| 可 |
avg ( smallint )
→ numeric
avg ( integer )
→ numeric
avg ( bigint )
→ numeric
avg ( numeric )
→ numeric
avg ( real )
→ double precision
avg ( double precision )
→ double precision
avg ( interval )
→ interval
すべての非NULL入力値の平均（算術平均）を計算します。
| 可 |
bit_and ( smallint )
→ smallint
bit_and ( integer )
→ integer
bit_and ( bigint )
→ bigint
bit_and ( bit )
→ bit
全ての非NULLの入力値のビット積を計算します。
| 可 |
bit_or ( smallint )
→ smallint
bit_or ( integer )
→ integer
bit_or ( bigint )
→ bigint
bit_or ( bit )
→ bit
全ての非NULLの入力値のビット和を計算します。
| 可 |
bit_xor ( smallint )
→ smallint
bit_xor ( integer )
→ integer
bit_xor ( bigint )
→ bigint
bit_xor ( bit )
→ bit
すべての非NULL入力値のビット毎の排他的論理和を計算します。
順序付けられない値の集合のチェックサムとして有用かもしれません。
| 可 |
bool_and ( boolean )
→ boolean
全ての入力が真ならば真、そうでなければ偽を返します。
| 可 |
bool_or ( boolean )
→ boolean
入力のどれかが真ならば真、そうでなければ偽を返します。
| 可 |
count ( * )
→ bigint
入力行数を返します。
| 可 |
count ( "any" )
→ bigint
非NULLの入力行数を返します。
| 可 |
every ( boolean )
→ boolean
これはSQL標準のbool_andと等価です。
| 可 |
json_agg ( anyelement )
→ json
jsonb_agg ( anyelement )
→ jsonb
NULLも含めてすべての入力値を収集し、JSON配列に格納します。
入力はto_jsonあるいはto_jsonbでJSONに変換されます。
| 非 |
json_objectagg (
[ { key_expression { VALUE | ':' } value_expression } ]
[ { NULL | ABSENT } ON NULL ]
[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
JSON_オブジェクトのように動作しますが、集約関数として動作するため、1つのkey_expressionと1つのvalue_expression引数のみを使用します。
SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v)
→ { "a" : "2022-05-10", "b" : "2022-05-11" }
| 非 |
json_object_agg ( key
"any", value
"any" )
→ json
jsonb_object_agg ( key
"any", value
"any" )
→ jsonb
すべてのキー／値ペアをJSONオブジェクトに格納します。
キー引数はテキストに変換されます。値引数はto_jsonあるいはto_jsonbにしたがって変換されます。
値はNULLでも構いませんが、キーはNULLにはできません。
| 非 |
json_object_agg_strict (
key "any",
value "any" )
→ json
jsonb_object_agg_strict (
key "any",
value "any" )
→ jsonb
すべてのキー／値ペアをJSONオブジェクトに格納します。
キー引数はテキストに変換されます。値はto_jsonあるいはto_jsonbにしたがって変換されます。
keyはNULLにはできません。
valueがNULLなら、そのエントリはスキップされます。
| 非 |
json_object_agg_unique (
key "any",
value "any" )
→ json
jsonb_object_agg_unique (
key "any",
value "any" )
→ jsonb
すべてのキー／値ペアをJSONオブジェクトに格納します。
キー引数はテキストに変換されます。値はto_jsonあるいはto_jsonbにしたがって変換されます。
値はNULLでも構いませんが、キーはNULLにはできません。
重複キーがある場合、エラーが発生します。
| 非 |
json_arrayagg (
[ value_expression ]
[ ORDER BY sort_expression ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
JSON_配列と同じように動作しますが、集約関数として動作するため、1つのvalue_式パラメータのみを使用します。
ABSENT ON NULLが指定されている場合、NULL値は無視されます。
ORDER BYが指定されている場合、要素は入力順ではなく、配列の順に表示されます。
SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v)
→ [2, 1]
| 非 |
json_object_agg_unique_strict (
key "any",
value "any" )
→ json
jsonb_object_agg_unique_strict (
key "any",
value "any" )
→ jsonb
すべてのキー／値ペアをJSONオブジェクトに格納します。
キー引数はテキストに変換されます。値はto_jsonあるいはto_jsonbにしたがって変換されます。
keyはNULLにはできません。
valueがNULLなら、そのエントリはスキップされます。
重複キーがある場合、エラーが発生します。
| 非 |
max ( see text )
→ same as input type
非NULL入力値の最大を計算します。
数値、文字列、日時、列挙型およびinet、interval、money、oid、pg_lsn、tid、xid8、およびこれらすべての配列でも同様に利用できます。
| 可 |
min ( see text )
→ same as input type
非NULL入力値の最小を計算します。
数値、文字列、日時、列挙型およびinet、interval、money、oid、pg_lsn、tid、xid8、およびこれらすべての配列でも同様に利用できます。
| 可 |
range_agg ( value
anyrange )
→ anymultirange
range_agg ( value
anymultirange )
→ anymultirange
非NULL入力の和を計算します。
| 非 |
range_intersect_agg ( value
anyrange )
→ anyrange
range_intersect_agg ( value
anymultirange )
→ anymultirange
非NULL入力の共通部分を計算します。
| 非 |
json_agg_strict ( anyelement )
→ json
jsonb_agg_strict ( anyelement )
→ jsonb
NULLをスキップして、すべての入力値をJSON配列に収集します。
値はto_JSONまたはto_JSONに従ってjsonbに変換されます。
| 非 |
string_agg ( value
text, delimiter text )
→ text
string_agg ( value
bytea, delimiter bytea )
→ bytea
非NULL入力を結合して文字列に格納します。
最初の値以降、各値の前にdelimiterで指定した値が（NULLでなければ）追加されます。
| 可 |
sum ( smallint )
→ bigint
sum ( integer )
→ bigint
sum ( bigint )
→ numeric
sum ( numeric )
→ numeric
sum ( real )
→ real
sum ( double precision )
→ double precision
sum ( interval )
→ interval
sum ( money )
→ money
非NULL入力値の合計を計算します。
| 可 |
xmlagg ( xml )
→ xml
非NULLのXML入力値を結合します。（9.15.1.7参照。）
| 非 |
上記の関数は、count関数を除き、1行も選択されなかった場合NULL値を返すことに注意してください。
特に、行の選択がないsum関数は、予想されるであろうゼロではなくNULLを返し、そしてarray_aggは、入力行が存在しない場合に、空配列ではなくNULLを返します。
必要であれば、NULLをゼロまたは空配列と置換する目的でcoalesce関数を使うことができます。
集約関数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;
外側の問い合わせのレベルで結合などの追加処理がある場合、この方法は失敗するかもしれないことに注意して下さい。
なぜなら、集約の計算の前に副問い合わせの出力を並べ替える必要があるかも知れないからです。
注記
bool_and、bool_or論理集約関数は標準SQLの集約関数every、anyまたはsomeに対応します。
PostgreSQLはeveryをサポートしますが、any、あるいはsomeはサポートしません。
anyとsomeの標準の構文には曖昧さがあるからです。
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
ここで、副問い合わせが論理値での１行を返す場合、ANYは副問い合わせを導入するもの、もしくは集約関数であるものいずれかとみなすことができます。
従って、これらの集約関数に標準の名前を付けることはできません。
注記
他のSQLデータベース管理システムでの作業に親しんだユーザは、count集約関数がテーブル全体に適用される場合の性能に失望するかも知れません。
SELECT count(*) FROM sometable;
のような問い合わせはテーブルサイズに比例した労力が必要です。PostgreSQLはテーブル全体か、そのテーブルの全ての行を含んだインデックス全体のスキャンを必要とします。
統計解析処理によく使用される集約関数を表 9.60に示します。
（これらは、より一般的に使用される集約関数との混乱を防ぐために別出ししました。）
numeric_typeを受け付けると表示されている関数は、smallint、integer、bigint、numeric、real、double precisionのすべての型で利用可能です。
説明の部分におけるNは、すべての入力式が非NULLの入力行の個数を表します。
すべての場合にて、例えばNが0の時など計算が無意味である場合にはNULLが返されます。
表9.60 統計処理用の集約関数
関数
説明
| 部分モード |
|---|
corr ( Y double precision, X double precision )
→ double precision
相関係数を計算します。
| 可 |
covar_pop ( Y double precision, X double precision )
→ double precision
母共分散を計算します。
| 可 |
covar_samp ( Y double precision, X double precision )
→ double precision
標本の共分散を計算します。
| 可 |
regr_avgx ( Y double precision, X double precision )
→ double precision
独立変数の平均値を計算します。
sum(X)/N.
| 可 |
regr_avgy ( Y double precision, X double precision )
→ double precision
従属変数の平均値を計算します。
sum(Y)/N.
| 可 |
regr_count ( Y double precision, X double precision )
→ bigint
両方の入力が非NULLとなる行数を計算します。
| 可 |
regr_intercept ( Y double precision, X double precision )
→ double precision
(X, Y)の組み合わせで決まる、最小二乗法による線形方程式のY切片を計算します。
| 可 |
regr_r2 ( Y double precision, X double precision )
→ double precision
相関係数の二乗を計算します。
| 可 |
regr_slope ( Y double precision, X double precision )
→ double precision
(X, Y)の組み合わせで決まる、最小二乗法による線型方程式の勾配を計算します。
| 可 |
regr_sxx ( Y double precision, X double precision )
→ double precision
独立変数の「二乗和」、sum(X^2) - sum(X)^2/Nを計算します。
| 可 |
regr_sxy ( Y double precision, X double precision )
→ double precision
独立変数と従属変数の「積の和」、sum(X*Y) - sum(X) * sum(Y)/Nを計算します。
| 可 |
regr_syy ( Y double precision, X double precision )
→ double precision
従属変数の「積の和」、sum(Y^2) - sum(Y)^2/Nを計算します。
| 可 |
stddev ( numeric_type )
→ double precisionあるいはrealに対してはdouble precision、それ以外はnumeric
これはstddev_sampの歴史的な別名です。
| 可 |
stddev_pop ( numeric_type )
→ double precisionあるいはrealに対してはdouble precision、それ以外はnumeric
入力値の母標準偏差を計算します。
| 可 |
stddev_samp ( numeric_type )
→ double precisionあるいはrealに対してはdouble precision、それ以外はnumeric
入力値の標本標準偏差を計算します。
| 可 |
variance ( numeric_type )
→ double precisionあるいはrealに対してはdouble precision、それ以外はnumeric
これはvar_sampの歴史的な別名です。
| 可 |
var_pop ( numeric_type )
→ double precisionあるいはrealに対してはdouble precision、それ以外はnumeric
入力値の母分散（母標準偏差の二乗）を計算します。
| 可 |
var_samp ( numeric_type )
→ double precisionあるいはrealに対してはdouble precision、それ以外はnumeric
入力値の標本分散（標本標準偏差の二乗）を計算します。
| 可 |
表 9.61に順序集合集約構文を使う集約関数を示します。
これらの関数は「逆分散」関数として参照されることがあります。
これらの集約入力はORDER BYで導入され、集約ではないdirect argumentを取ることもでき、一度だけ計算されます。
fractionパラメータを取る関数では、その値は0と1の間でなければなりません。そうでなければエラーが生じます。
ただしNULLのfraction値は単にNULLの結果をもたらします。
表9.61 順序集合集約関数
関数
説明
| 部分モード |
|---|
mode () WITHIN GROUP ( ORDER BY anyelement )
→ anyelement
集約引数の最頻値、最も頻出する値（複数の同じ度数の結果があれば、任意に選んだ最初のもの）を計算します。
集約引数はソート可能な型でなければなりません。
| 非 |
percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY double precision )
→ double precision
percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY interval )
→ interval
連続百分位数、引数の値の順序付け集合中で指定されたfractionに対応する値を計算します。
これは必要なら隣り合う入力項目を補間します。
| 非 |
percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY double precision )
→ double precision[]
percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY interval )
→ interval[]
複数の連続百分位数を計算します。
結果はfractionsパラメータと同じ次元数の配列です。各非NULL要素は（必要なら隣り合う入力項目を補間して）その百分位数に対応する値で置き換えられます。
| 非 |
percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement )
→ anyelement
離散百分位数を計算します。集約引数の順序付け集合中で、その位置が指定したfractionと等しいか越えた最初の値です。
集約引数はソート可能な型でなければなりません。
| 非 |
percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement )
→ anyarray
複数の離散百分位数を計算します
結果はfractionsパラメータと同じ次元数の配列です。各非NULL要素はその百分位数に対応する値で置き換えられます。
集約引数はソート可能な型でなければなりません。
| 非 |
表 9.62に列挙されている「仮想集合」集約は、それぞれ9.22で定義されている同じ名前のウィンドウ関数と関連します。
どの場合も、集約結果は、argsから構築される「仮想的な」行に対して、関連するウィンドウ関数が返す値で、そのような行がsorted_argsから計算されるソートされた行のグループに追加される場合を想定します。
これらの関数に対してargsで与えられる直接引数のリストは、sorted_argsで与えられる集約された引数の数と型に一致しなければなりません。
ほとんどの組み込み集約とは異なり、この集約はSTRICTではありません、すなわち、NULLを含む入力行を落としません。
NULL値はORDER BY節で指定されるルールに従って並べられます。
表9.62 仮想集合集約関数
関数
説明
| 部分モード |
|---|
rank ( args ) WITHIN GROUP ( ORDER BY sorted_args )
→ bigint
重複する行のギャップを含む仮想の行の順位を計算します。すなわち、ピアグループの先頭の行の番号です。
| 非 |
dense_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args )
→ bigint
重複する行のギャップなしの仮想の行の順位を計算します。この関数は実効的にピアグループを数えます。
| 非 |
percent_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args )
→ double precision
仮想行の相対的な順位を計算します。すなわち、(rank - 1) / (total rows - 1)です。
ですから値の範囲は境界を含んで0から1までです。
| 非 |
cume_dist ( args ) WITHIN GROUP ( ORDER BY sorted_args )
→ double precision
現在行の相対順位を計算します。すなわち、(仮想行より先行する、あるいはピアの行数) / (合計行数)です。
ですから範囲は1/Nから1です。
| 非 |
表9.63 グループ化演算
関数
説明
|
|---|
GROUPING ( group_by_expression(s) )
→ integer
どのGROUP BY式が現在のグループ化セットに含まれないかを示す整数のビットマスクを返します。
最も右側の引数が最下位ビットになるようにビットが割り当てられます。
各ビットは対応する式が結果の行を生成するグループ化セットのグループ化条件に含まれていれば0、そうでなければ1です。
|
表 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)
ここで、最初の4行のグループ化値0はこれらがグループ化列に対して正常にグループ化されたことを示します。
値1はmodelが最後とその一つ前の行ではグループ化されなかったことを、値3はmakeもmodelも最後の行でグループ化されなかったことを意味します（ですから最後の行はすべての入力行に対する集約になっています）。