postgresql----聚合函数
聚合函数是从一组输入中计算出一个结果的函数。
测试表
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) | smallint, int, bigint, or bit | 和入参类型相同 | 所有非NULL输入值的按位与，如果全为NULL则返回NULL | select bit_and(id) from tbl_test; | 0 |
| bit_or(expression) | smallint, int, bigint, 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) | (text, text) or (bytea, bytea) | 同参数类型 | 将输入使用delimiter连接成一个text | select string_agg(name,',') from tbl_test; | 张三,李四,王五 |
| sum(expression) | smallint, int, bigint, real, double precision, numeric, interval, 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(Y, X) | double precision | double precision | 相关系数 | select corr(id,id) from tbl_test; | 1 |
| covar_pop(Y, X) | double precision | double precision | 总体协方差 | select covar_pop(id,id) from tbl_test; | 0.666666666666667 |
| covar_samp(Y, X) | double precision | double precision | 样本协方差 | select covar_samp(id,id1) from tbl_test; | 0 |
| regr_avgx(Y, X) | double precision | double precision | 自变量平均值(sum(X)/N) | select regr_avgx(id,id1) from tbl_test; | 1 |
| regr_avgy(Y, X) | double precision | double precision | 因变量平均值(sum(Y)/N) | select regr_avgy(id,id1) from tbl_test; | 2 |
| regr_count(Y, X) | double precision | bigint | 两个参数都不为NULL的行数 | select regr_count(id,id1) from tbl_test; | 3 |
| regr_intercept(Y, X) | double precision | double precision | 根据所有输入点(X,Y)利用最小二乘法计算一个线性方程式。然后返回该直线的Y轴截距 | select regr_intercept(id,id) from tbl_test; | 0 |
| regr_r2(Y, X) | double precision | double precision | 相关系数平方 | select regr_r2(id,id) from tbl_test; | 1 |
| regr_slope(Y, X) | double precision | double precision | 根据所有输入点(X,Y)利用最小二乘法计算一个线性方程式。然后返回该直线的斜率 | select regr_slope(id,id) from tbl_test; | 1 |
| regr_sxx(Y, X) | double precision | double precision | sum(X^2) - sum(X)^2/N | select regr_sxx(id,id) from tbl_test; | 2 |
| regr_sxy(Y, X) | double precision | double precision | sum(X*Y) - sum(X) * sum(Y)/N | select regr_sxy(id,id) from tbl_test; | 2 |
| regr_syy(Y, X) | 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 |
