聚集函數會從一組輸入值計算出單一結果。內建的通用聚集函數列於表 9.60，而統計聚集函數則列於表 9.61。內建的組內排序集聚集函數列於表 9.62，而內建的組內假設集聚集函數則列於表 9.63。與聚集函數密切相關的群組作業列於表 9.64。聚集函數的特殊語法考量事項說明於第 4.2.7 節。有關其他入門資訊，請參閱第 2.7 節。
支援部分模式的聚集函數有資格參與各種最佳化，例如平行聚集。
雖然以下所有聚集函數都接受選用的 ORDER BY 子句 (如第 4.2.7 節所述)，但該子句僅新增至輸出受排序影響的聚集函數。
表 9.60. 通用聚集函數
|
函數
描述
|
部分模式 |
|
any_value ( anyelement ) → 與輸入類型相同
從非 Null 輸入值傳回任意值。
|
是 |
|
array_agg ( anynonarray ORDER BY input_sort_columns ) → anyarray
將所有輸入值 (包括 Null 值) 收集到陣列中。
|
是 |
|
array_agg ( anyarray ORDER BY input_sort_columns ) → anyarray
將所有輸入陣列串連成一個維度更高的陣列。(輸入必須具有相同的維度，且不能為空或 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 輸入值的位元 AND 運算。
|
是 |
|
bit_or ( smallint ) → smallint
bit_or ( integer ) → integer
bit_or ( bigint ) → bigint
bit_or ( bit ) → bit
計算所有非空輸入值的位元 OR 運算。
|
是 |
|
bit_xor ( smallint ) → smallint
bit_xor ( integer ) → integer
bit_xor ( bigint ) → bigint
bit_xor ( bit ) → bit
計算所有非空輸入值的位元互斥 OR 運算。可用作無序值集合的校驗和。
|
是 |
|
bool_and ( boolean ) → boolean
如果所有非空輸入值均為 true，則傳回 true，否則傳回 false。
|
是 |
|
bool_or ( boolean ) → boolean
如果任何非空輸入值為 true，則傳回 true，否則傳回 false。
|
是 |
|
count ( * ) → bigint
計算輸入列的數量。
|
是 |
|
count ( "any" ) → bigint
計算輸入值不為 null 的輸入列的數量。
|
是 |
|
every ( boolean ) → boolean
這是 SQL 標準中與 bool_and 等效的函數。
|
是 |
|
json_agg ( anyelement ORDER BY input_sort_columns ) → json
jsonb_agg ( anyelement ORDER BY input_sort_columns ) → jsonb
將所有輸入值（包括 null 值）收集到 JSON 陣列中。數值會依照 to_json 或 to_jsonb 轉換為 JSON。
|
否 |
|
json_agg_strict ( anyelement ) → json
jsonb_agg_strict ( anyelement ) → jsonb
將所有輸入值（跳過 null 值）收集到 JSON 陣列中。數值會依照 to_json 或 to_jsonb 轉換為 JSON。
|
否 |
|
json_arrayagg ( [ value_expression ] [ ORDER BY sort_expression ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
與 json_array 的行為方式相同，但作為聚合函數，因此它只接受一個 value_expression 參數。 如果指定了 ABSENT ON NULL，則會省略所有 NULL 值。 如果指定了 ORDER BY，則元素將按該順序出現在陣列中，而不是按輸入順序排列。
SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v) → [2, 1]
|
否 |
|
json_objectagg ( [ { key_expression { VALUE | ':' } value_expression } ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
行為與 json_object 類似，但作為聚合函數，因此它只接受一個 key_expression 和一個 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" ORDER BY input_sort_columns ) → json
jsonb_object_agg ( key "any", value "any" ORDER BY input_sort_columns ) → 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 物件中。Key 參數會強制轉換為 text；Value 參數則根據 to_json 或 to_jsonb 進行轉換。Value 可以是 null，但 Key 不能是。如果存在重複的 Key，則會拋出錯誤。
|
否 |
|
json_object_agg_unique_strict ( key "any", value "any" ) → json
jsonb_object_agg_unique_strict ( key "any", value "any" ) → jsonb
將所有鍵/值對收集到一個 JSON 物件中。Key 參數會強制轉換為 text；Value 參數則根據 to_json 或 to_jsonb 進行轉換。 key 不能為 null。如果 value 為 null，則會跳過該條目。如果存在重複的 Key，則會拋出錯誤。
|
否 |
|
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 輸入值的交集。
|
否 |
|
string_agg ( value text, delimiter text ) → text
string_agg ( value bytea, delimiter bytea ORDER BY input_sort_columns ) → 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 ORDER BY input_sort_columns ) → xml
將非 null XML 輸入值連接起來 (請參閱 Section 9.15.1.8)。
|
否 |
應該注意的是，除了 count 之外，如果沒有選擇任何列，這些函數會傳回 null 值。特別是，如果沒有列，sum 會傳回 null，而不是像預期那樣傳回零，並且如果沒有輸入列，array_agg 會傳回 null，而不是空陣列。 必要時可以使用 coalesce 函數用零或空陣列代替 null。
聚合函數 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，以及類似的使用者定義的聚合函數，會根據輸入值的順序產生有意義的不同結果值。預設情況下，此排序是未指定的，但可以透過在聚合呼叫中編寫 ORDER BY 子句來控制，如 Section 4.2.7 所示。或者，從排序的子查詢提供輸入值通常可以運作。例如
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，因為標準語法中存在歧義
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
如果子查詢傳回具有布林值的單列，則此處的 ANY 可以被視為引入子查詢，或視為聚合函數。因此，不能將標準名稱賦予這些聚合。
注意
習慣使用其他 SQL 資料庫管理系統的使用者可能會對 count 聚合應用於整個表格時的效能感到失望。像這樣的查詢
SELECT count(*) FROM sometable;
將需要與表格大小成比例的工作量：PostgreSQL 需要掃描整個表格或包含表格中所有列的索引的全部。
如表 9.61 所示，這些是通常用於統計分析的彙總函式。（它們被分開列出只是為了避免使更常用彙總函式的列表變得雜亂。）顯示接受 numeric_type 的函式適用於所有 smallint、integer、bigint、numeric、real 和 double precision 類型。說明中提到 N 的地方，表示所有輸入表達式均為非空值的輸入列數。在所有情況下，如果計算沒有意義，例如當 N 為零時，則會返回 null。
表 9.61. 統計用的彙總函式
|
函數
描述
|
部分模式 |
|
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
計算兩個輸入均為非空值的列數。
|
是 |
|
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 ) → real 或 double precision 為 double precision，否則為 numeric
這是 stddev_samp 的歷史別名。
|
是 |
|
stddev_pop ( numeric_type ) → real 或 double precision 為 double precision，否則為 numeric
計算輸入值的母體標準差。
|
是 |
|
stddev_samp ( numeric_type ) → real 或 double precision 為 double precision，否則為 numeric
計算輸入值的樣本標準差。
|
是 |
|
variance ( numeric_type ) → 若 real 或 double precision 則為 double precision，否則為 numeric
這是 var_samp 的歷史別名。
|
是 |
|
var_pop ( numeric_type ) → 若 real 或 double precision 則為 double precision，否則為 numeric
計算輸入值的母體變異數（母體標準差的平方）。
|
是 |
|
var_samp ( numeric_type ) → 若 real 或 double precision 則為 double precision，否則為 numeric
計算輸入值的樣本變異數（樣本標準差的平方）。
|
是 |
表 9.62 顯示了一些使用 ordered-set aggregate 語法的彙總函數。這些函數有時被稱為「“反向分佈”」函數。它們的彙總輸入由 ORDER BY 引入，並且它們也可能採用一個 direct argument，該參數不會被彙總，但只計算一次。所有這些函數都會忽略其彙總輸入中的空值。對於那些採用 fraction 參數的函數，fraction 值必須介於 0 和 1 之間；如果不是，則會拋出錯誤。但是，空值的 fraction 值只會產生一個空結果。
表 9.62. Ordered-Set 彙總函數
|
函數
描述
|
部分模式 |
|
mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement
計算 mode，即彙總參數的最常出現的值（如果有多個頻率相同的值，則任意選擇第一個）。彙總參數必須是可排序的類型。
|
否 |
|
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 參數具有相同維度的陣列，每個非空元素都被替換為與該百分位數對應的（可能內插的）值。
|
否 |
|
percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement
計算 離散百分位數，即彙總參數值的排序集合中，排序位置等於或超過指定的 fraction 的第一個值。 彙總參數必須是可排序的類型。
|
否 |
|
percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray
計算多個離散百分位數。 結果是一個與 fractions 參數具有相同維度的陣列，每個非空元素都被替換為與該百分位數對應的輸入值。 彙總參數必須是可排序的類型。
|
否 |
表 9.63 中列出的每個「“假設集合”」彙總都與 第 9.22 節 中定義的同名視窗函數相關聯。 在每種情況下，彙總的結果都是關聯的視窗函數針對由 args 構建的「“假設”」行所返回的值，如果這樣的一行已添加到由 sorted_args 表示的排序的行群組中。 對於這些函數中的每一個，args 中給出的直接參數列表必須與 sorted_args 中給出的彙總參數的數量和類型相符。 與大多數內建彙總不同，這些彙總不是嚴格的，也就是說它們不會刪除包含空值的輸入列。 空值根據 ORDER BY 子句中指定的規則進行排序。
表 9.63. 假設集合彙總函數
|
函數
描述
|
部分模式 |
|
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) / (總行數 - 1)。 因此，該值範圍從 0 到 1（含）。
|
否 |
|
cume_dist ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision
計算累積分布，也就是（假設列之前或同等列的數量）/（總列數）。因此，該值範圍從 1/N 到 1。
|
否 |
表 9.64. 群組操作
|
函數
描述
|
|
GROUPING ( group_by_expression(s) ) → integer
傳回一個位元遮罩，指示哪些 GROUP BY 運算式未包含在目前的群組集中。位元是以最右邊的引數對應於最低有效位元的方式指派；如果對應的運算式包含在產生目前結果列的群組集的群組準則中，則每個位元為 0，如果未包含，則為 1。
|
表 9.64 中顯示的群組操作與群組集一起使用（請參閱 第 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)
在這裡，前四列中的 grouping 值 0 表示它們已在兩個群組欄位上正常分組。值 1 表示 model 未在倒數第二列中分組，值 3 表示 make 和 model 都未在最後一列中分組（因此它是對所有輸入列的彙總）。