postgresql----聚合函数

时间:2022-07-25 23:30:50

聚合函数是从一组输入中计算出一个结果的函数。

测试表

test=# \d tbl_test
Table "public.tbl_test"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(32) |
sex | character varying(1) | test=# select * from tbl_test;
id | name | sex
----+------+-----
1 | 张三 | m
2 | 李四 | m
3 | 王五 | f
(3 rows)

通用聚合函数

函数 参数类型 返回类型 描述 示例 结果
array_agg(expression) 任意非数组类型 参数类型的数组 将入参包括NULL连接成一个数组 select array_agg(id) from tbl_test; {1,2,3}
array_agg(expression) 任意数组类型 入参数据类型

将入参数组连接成更高维度的数组,输入的数组必须是相同的维度,且不允许是空或NULL

select array_agg(array['b','c','a']);  {{b,c,a}}
avg(expression) smallint, int, bigint, real, double precision, numeric, or interval 整形返回numeric,浮点型返回double precision,其他和入参类型相同 平均值 select avg(id) from tbl_test; 2.0000000000000000
bit_and(expression) smallintintbigint, or bit 和入参类型相同 所有非NULL输入值的按位与,如果全为NULL则返回NULL select bit_and(id) from tbl_test; 0
bit_or(expression) smallintintbigint, or bit 和入参类型相同 所有非NULL输入值的按位或,如果全为NULL则返回NULL select bit_or(id) from tbl_test; 3
bool_and(expression) bool bool 如果输入全是true则返回true,否则为false select bool_or(id::bool) from tbl_test; t
bool_or(expression) bool bool 如果输入至少一个true,则返回true,否则返回false select bool_or((id-1)::bool) from tbl_test; t
count(*)   bigint 输入行数 select count(*) from tbl_test; 3
count(expression) any bigint 输入行中非NULL的行数 select count(id) from tbl_test; 3
every(expression) bool bool 功能同bool_and    
json_agg(expression) any json 将输入聚合成一个json数组 select json_agg(id) from tbl_test; [1, 2, 3]
jsonb_agg(expression) any jsonb 将输入聚合成一个json数组 select jsonb_agg(id) from tbl_test; [1, 2, 3]
json_object_agg(name,value) (any, any) json 将输入组成一个key/value对的json对象 select json_object_agg('a','one'); { "a" : "one" }
jsonb_object_agg(name,value) (any, any) jsonb 将输入组成一个key/value对的json对象 select jsonb_object_agg('a','one'); {"a": "one"}
max(expression)     输入最大值

select max(id) from tbl_test;

3
min(expression)     输入最小值 select min(id) from tbl_test; 1
string_agg(expression,delimiter) (texttext) or (byteabytea) 同参数类型 将输入使用delimiter连接成一个text select string_agg(name,',') from tbl_test;  张三,李四,王五
sum(expression) smallintintbigintrealdouble precisionnumericinterval, or money   输入和 select sum(id) from tbl_test; 6
xmlagg(expression) xml xml   请参考xml类型及其函数  

修改表

test=# alter table tbl_test add column id1 int default 1;
ALTER TABLE
test=# select * from tbl_test;
id | name | sex | id1
----+------+-----+-----
1 | 张三 | m | 1
2 | 李四 | m | 1
3 | 王五 | f | 1
(3 rows)

统计聚合函数

函数 参数类型 返回类型 描述 示例 结果
corr(YX) double precision double precision 相关系数 select corr(id,id) from tbl_test; 1
covar_pop(YX) double precision double precision 总体协方差 select covar_pop(id,id) from tbl_test; 0.666666666666667
covar_samp(YX) double precision double precision 样本协方差 select covar_samp(id,id1) from tbl_test; 0
regr_avgx(YX) double precision double precision 自变量平均值(sum(X)/N) select regr_avgx(id,id1) from tbl_test; 1
regr_avgy(YX) double precision double precision 因变量平均值(sum(Y)/N) select regr_avgy(id,id1) from tbl_test; 2
regr_count(YX) double precision bigint 两个参数都不为NULL的行数  select regr_count(id,id1) from tbl_test; 3
regr_intercept(YX) double precision double precision 根据所有输入点(X,Y)利用最小二乘法计算一个线性方程式。然后返回该直线的Y轴截距 select regr_intercept(id,id) from tbl_test; 0
regr_r2(YX) double precision double precision 相关系数平方 select regr_r2(id,id) from tbl_test; 1
regr_slope(YX) double precision double precision 根据所有输入点(X,Y)利用最小二乘法计算一个线性方程式。然后返回该直线的斜率 select regr_slope(id,id) from tbl_test; 1
regr_sxx(YX) double precision double precision sum(X^2) - sum(X)^2/N  select regr_sxx(id,id) from tbl_test; 2
regr_sxy(YX) double precision double precision sum(X*Y) - sum(X) * sum(Y)/N  select regr_sxy(id,id) from tbl_test; 2
regr_syy(YX) double precision double precision sum(Y^2) - sum(Y)^2/N  select regr_syy(id,id) from tbl_test; 2
stddev(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

同stddev_samp    
stddev_pop(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

总体标准差 select stddev_pop(id) from tbl_test; 0.81649658092772603273
stddev_samp(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

样本标准差 select stddev_samp(id) from tbl_test; 1.00000000000000000000
variance(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

同var_samp    
var_pop(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

总体方差 select var_pop(id) from tbl_test; 0.66666666666666666667
var_samp(expression)

smallint, int, bigint, real,

double precision, or numeric

double precision for floating-point arguments,

otherwise numeric

样本方差 select var_samp(id) from tbl_test; 1.00000000000000000000
test=# insert into tbl_test values (2,'ww','f');
INSERT 0 1
test=# select * from tbl_test;
id | name | sex | id1
----+------+-----+-----
1 | 张三 | m | 1
2 | 李四 | m | 1
3 | 王五 | f | 1
2 | ww | f | 1
(4 rows)

顺序集聚合函数

函数 直接参数类型 聚合参数类型 返回类型 描述 示例 结果
mode() WITHIN GROUP (ORDER BYsort_expression)   任意可排序类型 同排序类型

返回最频繁的输入值(如果有

多个同样频繁的结果,则返回第一个)

select mode() within group (order by id) from tbl_test; 2
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) double precision double precisionor interval 同排序类型 continuous percentile: returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed select percentile_cont(0.25) WITHIN GROUP (ORDER BY id) from tbl_test; 1.75
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression) double precision[] double precisionor interval array of sort expression's type multiple continuous percentile: returns an array of results matching the shape of the fractionsparameter, with each non-null element replaced by the value corresponding to that percentile    
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) double precision any sortable type same as sort expression discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction    
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression) double precision[] any sortable type array of sort expression's type multiple discrete percentile: returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the input value corresponding to that percentile