如何在Postgres中将percentile_conts与多个分位数一起使用

Question

我目前有一个查询的工作方式如下：

select AVG(t2 - t1) as delay,
percentile_cont(0.25) within group (order by (t2 - t1)) as q25,
percentile_cont(0.5) within group (order by (t2 - t1)) as median,
percentile_cont(0.75) within group (order by (t2 - t1)) as q75,
p.bool1,
p.cat1
from people p
group by p.bool1, p.cat1
order by p.cat1,p.bool1

然而，我在postgres函数聚合页面上看到：https://www.postgresql.org/docs/9.4/functions-aggregate.html

我应该能够指定多个分位数：

percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)  double precision[]  double precision or interval    array of sort expression's type     multiple continuous percentile: returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the value corresponding to that percentile

我想使用这个，这样我就不会为每个分位数重新计算t2-t1。获取多个分位数的正确语法是什么？我需要一个子查询吗？

Answer 1

我想使用它，这样我就不会为每个分位数重新计算t2-t1

在这种情况下，横向连接可能会有所帮助：

select AVG(t2 - t1) as delay,
percentile_cont(0.25) within group (order by s.col) as q25,
percentile_cont(0.5) within group (order by s.col) as median,
percentile_cont(0.75) within group (order by s.col) as q75,
p.bool1,
p.cat1
from people p
,LATERAL(SELECT t2 - t1 AS col) s
group by p.bool1, p.cat1
order by p.cat1,p.bool1;

相关：PostgreSQL：在同一查询中使用计算列

---

阵列定义为：ARRAY[0.25, 0.5, 0.75]或'{0.25, 0.5, 0.75}'::double precision[]

select AVG(t2 - t1) as delay,
-- 1
percentile_cont(ARRAY[0.25, 0.5, 0.75]) within group (order by (t2 - t1)) as q25,
-- 2
percentile_cont('{0.25, 0.5, 0.75}'::double precision[]) 
within group (order by (t2 - t1)) as q
p.bool1,
p.cat1
from people p
group by p.bool1, p.cat1
order by p.cat1,p.bool1;

db<gt；小提琴演示

---

是否有一种简单的方法可以内联指定每个产生的百分位数字段的名称，就像它们与q25、q50、q75等一样

WITH cte AS (
select AVG(t2 - t1) as delay,
percentile_cont(ARRAY[0.25, 0.5, 0.75]) within group (order by (t2 - t1)) as q,
p.bool1,
p.cat1
from people p
group by p.bool1, p.cat1
)
select cte.*, q[1] AS q25, q[2] AS q50, q[3] AS q75
from cte
order by cat1,bool1;

db<gt；小提琴demo2