PostgreSQL中percentile_cont函数计算多组分位数的正确用法
正确实现方案
核心逻辑
你完全可以用percentile_cont的数组入参特性,省掉重复写三次t2 - t1、重复排三次序的冗余逻辑,只需要搭一层简单的子查询就够,根本不需要搞复杂的多层嵌套。
推荐写法
SELECT agg.delay, -- 直接从聚合好的分位数数组里按下标取值,完全不碰原始计算逻辑 agg.perc_arr[1] AS q25, agg.perc_arr[2] AS median, agg.perc_arr[3] AS q75, agg.bool1, agg.cat1 FROM ( SELECT -- 聚合阶段只算一次差值、排一次序 AVG(t2 - t1) AS delay, percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY t2 - t1) AS perc_arr, p.bool1, p.cat1 FROM people p GROUP BY p.bool1, p.cat1 ) agg ORDER BY agg.cat1, agg.bool1;
这种写法的实际好处:
- 整段查询里
t2 - t1只出现两次:一次算平均值,一次给分位数函数当排序依据,没有重复写三遍相同的表达式 - 分位计算只执行一次排序,一次算出三个分位值打包成数组返回,比原来写三个单独的
percentile_cont调用要快很多——原来的写法每调用一次单参数的分位函数,数据库就要单独对t2-t1的结果排一次序,三次就要排三回 - 以后要调整分位值(比如多算个90分位),只要改传入的数组就行,不用挨个改好几个SELECT子句的逻辑
容易踩的坑
- PostgreSQL的数组下标默认从1开始计数,和你传入的分位顺序完全对应:你传
[0.25, 0.5, 0.75],数组第一个元素就是25分位值、第二个是中位数、第三个是75分位值,下标千万别从0开始算,不然取出来的全是空值 - 别图省事直接在最外层SELECT里多次引用
percentile_cont的返回值拆列,那样数据库会因为同一个聚合表达式写了三遍,触发三次分位计算,完全白瞎了数组入参的性能优势 - 整套写法完全兼容PostgreSQL 9.4的函数规则,不存在版本适配问题
内容的提问来源于stack exchange,提问作者Mittenchops
相关产品推荐
相关产品推荐

