Skip to main content
 首页 » 数据库

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

2022年07月19日160TianFang

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中计算百分位数和中位数。通过示例进行说明,并对比不同方法的实现差异和效率。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/116671607
阅读延展