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 |