PostgreSQL中计算百分位数和中位数

本文介绍PostgreSQL中计算百分位数和中位数的方法,包括使用percentile_cont和percentile_disc函数,以及如何通过不同方式提高计算效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

PostgreSQL9.4版本之后很容易对一组值计算百分位数,主要是用有序集合的聚集函数percentile_contpercentile_disc

这两个函数类似,但对合并结果有些差异:

  • percentile_disc 返回最接近请求百分位的离散值。
  • percentile_cont 返回基于分布的多个值的连续值(插值)。更精确,包括两个输入值直接的数(带小数)。

下面通过示例进行讲解,首先我们准备一个示例表和数据。

create table thing (
	value int
);

insert into thing select generate_series(1,100,1); 

计算中位数

中位数即百分位为50%的的数。通过下面查询获取:

select percentile_disc(0.5) within group (order by value)
from thing

对于百分位数位于两个值之间,使用percentile_cont回返回他们的插值:

select percentile_cont(0.5) within group (order by value)
from thing;

插值:简单地说,假如又两个值[1,2],使用percentile_disc(0.5)返回1。使用percentile_cont(0.5)返回1.5,即两个数的平均值,因为非奇数,没有单个数表示中位数。

计算单个百分位数

可以使用0~1之间的小数表示任意百分位数。一个查询中可以使用多次,请看示例:

select
  percentile_disc(0.25) within group (order by value),
  percentile_disc(0.5) within group (order by value),
  percentile_disc(0.75) within group (order by value)
from thing;

另外within group也能和其他子句一起使用,如 group by。下面示例计算小于75和其他的百分位数。

select
  value < 75 as less_than_75,
  percentile_disc(0.75) within group (order by value)
from thing
group by 1;

group by 1 表示按照select 中第一个表达式进行分组。

计算所有百分位

有时可能需要计算1到100之间的所有百分位,用于获取数据大概分布状况,或用于缓存生成物化视图。
其中一个方法是组合使用generate_seriespercentitle_函数.下面示例生成1~100之间每个百分位数据:

select k, percentile_disc(k) within group (order by value)
from thing, generate_series(0.01, 1, 0.01) as k
group by k

generate_series生成临时表包括0.01,0.02,…等。generate_series的参数可以根据需求进行修改,如generate_series(0.25, 1, 0.25)生成四分位数。

上面方法效率较低。因为每个百分位计算都需要查询整个数据集,对于计算100个百分位数就需要扫描100次。

如果想仅扫描一次,可以使用窗口函数ntilentile分配数据集中的每个值到组中,组的数量我们可以指定。完整示例需要子查询实现。我们需要计算100个百分位,因此分为100个组:

select value, ntile(100) over (order by value) 
from thing

该查询返回所有值以及对应的组,下一步实现组到百分位数的转换。因为每个值已经均匀分布100个组,我们可以查看每个组的最大值。最终稿计算1~100的百分位语句为:

select max(buckets.value)/100.0, ntile as percentile
from
  (select thing.value, ntile(100) over (order by thing.value) from thing) as buckets
group by 2 order by 2

这种方式应该比上面的方法快100倍,因为它仅扫描一次,而不是100次。

总结

本文介绍了PostgreSQL中计算百分位数和中位数。通过示例进行说明,并对比不同方法的实现差异和效率。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值