PostgreSQL9.4版本之后很容易对一组值计算百分位数,主要是用有序集合的聚集函数percentile_cont
和 percentile_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_series
和percentitle_
函数.下面示例生成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次。
如果想仅扫描一次,可以使用窗口函数ntile
。ntile
分配数据集中的每个值到组中,组的数量我们可以指定。完整示例需要子查询实现。我们需要计算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