PostgreSQL 分组、聚合函数

根据老师表(teacher)，老师任课关系表(teacher2class)，课程表(class)，通过表连接，得到老师的任课结构表，如下：

select t.id, t.name, c.title from teacher t
    left join teacher2class t2c on t.id = t2c.teacher_id
    left join class c on t2c.class_id = c.id;

现希望根据老师分组，将老师认的课程聚合在一起，根据分组和聚合函数，修改SQL代码，到的最终结果，如下：

select t.id, t.name, json_agg(c.title) from teacher t
    left join teacher2class t2c on t.id = t2c.teacher_id
    left join class c on t2c.class_id = c.id
    group by t.id;

通用聚合函数

test=# select * from tb_test; -- 获取通用聚合函数测试表格
 id | name | sex 
----+------+-----
  1 | 张三 | m
  2 | 李四 | m
  3 | 王五 | f

函数	参数类型	返回类型	描述	示例	结果
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=# select * from tbl_test; -- 获取统计聚合函数的测试表格
 id | name | sex | id1 
----+------+-----+-----
  1 | 张三 | m   |   1
  2 | 李四 | m   |   1
  3 | 王五 | f   |   1

函数	参数类型	返回类型	描述	示例	结果
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(XY) - 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

顺序集聚合函数

-- 获取顺序集聚合函数的测试表格
select  *  from  tb_test; 
 id  |  name  |  sex  |  id1 
 -- --+------+-----+----- 
  1  |  张三  |  m    |    1 
  2  |  李四  |  m    |    1 
  3  |  王五  |  f    |    1 
  2  |  ww    |  f    |    1

函数	直接参数类型	聚合参数类型	返回类型	描述	示例	结果
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	同排序类型	连续百分比：返回与指定顺序中指定分数相对应的值，并在需要时在相邻输入项之间进行插值	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	排序表达式的类型的数组	多个连续百分位：返回与形状匹配的结果数组分数参数，每个非null元素替换为与该百分位对应的值
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)	double precision	any sortable type	same as sort expression	离散百分位数：返回第一个输入值，该值在顺序中的位置等于或超过指定的分数
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)	double precision[]	any sortable type	array of sort expression's type	多个离散百分比：返回与分数参数的形状匹配的结果数组，每个非null元素均替换为与该百分比对应的输入值

https://www.cnblogs.com/alianbog/p/5674838.html