5

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?

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
Stephen Smith
  • 347
  • 4
  • 12

1 Answers1

12

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;

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;

So, you can use a 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
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
  • 2
    When I do this, I get the following error: OVER is not supported for ordered-set aggregate percentile_cont ...which is weird because I have Postgres 9.5, and I thought it became supported in 9.4? – Stephen Smith Sep 25 '16 at 04:31
  • 1
    @StephenSmith . . . The documentation -- if you read it carefully -- is clear that the percentile functions are only aggregation functions and not window functions. – Gordon Linoff Sep 25 '16 at 12:00