PostgreSql 常用聚合函数

基础准备

CREATETEMPTABLEdemo(idint,vint,ttext);INSERTINTOdemoVALUES(1,10,'a'),(2,20,'b'),(3,NULL,'c');

id	v	t
1	10	a
2	20	b
3	NULL	c

注意：版本基于 PG 15，全部自带，无需扩展

A. 基础统计

SELECTcount(*)AScnt,-- 3count(v)AScnt_v,-- 2（NULL 不计）sum(v)ASs,-- 30avg(v)ASa,-- 15max(v)ASmx,-- 20min(v)ASmn-- 10FROMdemo;

结果：

cnt	cnt_v	s	a	mx	mn
3	2	30	15	20	10

B. 扩展统计

SELECTstddev_pop(v)ASstd_pop,-- 5stddev_samp(v)ASstd_samp,-- 7.0710678var_pop(v)ASvar_pop,-- 25var_samp(v)ASvar_samp-- 50FROMdemo;

结果：

std_pop	std_samp	var_pop	var_samp
5	7.07106781	25	50

C. 位运算聚合

SELECTbit_and(v)ASbit_and,-- 0 (10 & 20 = 0)bit_or(v)ASbit_or,-- 30 (10 | 20 = 30)bit_xor(v)ASbit_xor-- 30 (10 # 20 = 30)FROMdemo;

结果：

bit_and	bit_or	bit_xor
0	30	30

D. 字符串聚合

SELECTstring_agg(t,',')ASstr1,-- a,b,cstring_agg(t,','ORDERBYidDESC)ASstr2-- c,b,aFROMdemo;

结果：

str1	str2
a,b,c	c,b,a

E. 数组 / JSON 聚合

SELECTarray_agg(v)ASarr,-- {10,20}array_agg(vORDERBYvDESC)ASarr_desc,-- {20,10}json_agg(v)ASj,-- [10,20]jsonb_agg(t)ASjb-- ["a", "b", "c"]FROMdemo;

结果：

arr	arr_desc	j	jb
{10,20}	{20,10}	[10,20]	[“a”, “b”, “c”]

F. 排重聚合

SELECTcount(DISTINCTv)AScdv,-- 2string_agg(DISTINCTt,'')ASdst-- abcFROMdemo;

结果：

cdv	dst
2	abc

G. 有序集合（需要 WITHIN GROUP）

SELECTpercentile_cont(0.5)WITHINGROUP(ORDERBYv)ASmedian,-- 15percentile_disc(0.5)WITHINGROUP(ORDERBYv)ASdisc,-- 10mode()WITHINGROUP(ORDERBYt)ASmode_t-- a（出现最多）FROMdemo;

结果：

median	disc	mode_t
15	10	a

H. 假设集合（同样 WITHIN GROUP）

SELECTrank(15)WITHINGROUP(ORDERBYv)ASrnk,-- 2percent_rank(15)WITHINGROUP(ORDERBYv)ASpct,-- 0.5cume_dist()WITHINGROUP(ORDERBYv)AScume-- 1.0FROMdemo;

结果：

rnk	pct	cume
2	0.5	1.0