带窗函数的百分数计算 [英] Percentile calculation with a window function

问题描述

我知道您可以使用窗口函数来获得数据子集的平均值，总数，最小值和最大值。但是，是否有可能通过窗口函数获得中位数或25％而不是平均值？

I know you can get the average, total, min, and max over a subset of the data using a window function. But is it possible to get, say, the median, or the 25th percentile instead of the average with the window function?

换句话说，我如何将其重写为获取ID和每个区域内第25个或第50个百分位数的销售数字，而不是平均值？

Put another way, how do I rewrite this to get the id and the 25th or 50th percentile sales numbers within each district rather than the average?

SELECT id, avg(sales)
    OVER (PARTITION BY district) AS district_average
FROM t

推荐答案

您可以使用 percentile_cont（）或 percentile_disc（）将其编写为聚合函数：

You can write this as an aggregation function using percentile_cont() or percentile_disc():

select district, percentile_cont(0.25) within group (order by sales)
from t
group by district;

不幸的是，Postgres当前不支持将它们作为窗口函数：

Unfortunately, Postgres doesn't currently support these as a window functions:

select id, percentile_cont(0.25) within group (order by sales) over (partition by district) 
from t;

因此，您可以使用 join ：

select t.*, p_25, p_75
from t join
     (select district,
             percentile_cont(0.25) within group (order by sales) as p_25,
             percentile_cont(0.75) within group (order by sales) as p_75
      from t
      group by district
     ) td
     on t.district = td.district

这篇关于带窗函数的百分数计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！