Product Documentation

Appearance

PERCENTILE_DISC (window function) ​

Given an ordered set of values, this aggregate window function returns the discrete percentile value, which is the first input value whose position in the ordering equals or exceeds the specified fraction.

PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY sort_expression) OVER { window_name | ( [ window_definition ] ) }

Specify a fraction between 0 and 1. The sort expression data type must be numeric.

The window_definition for this function can have a PARTITION BY clause but no ORDER BY clause or frame clause. For the full window_definition syntax, see Syntax for Window Functions.

For example:

premdb=# select city, 
percentile_disc(0.9) within group(order by avg_att) over(partition by city) 
from team where avg_att>0 order by 2 desc;
   city     | percentile_disc 
-------------+-----------------
 Manchester  |          75.286
 Manchester  |          75.286
 London      |          59.944
 London      |          59.944
 London      |          59.944
 London      |          59.944
 London      |          59.944
 Newcastle   |          49.754
 Liverpool   |          43.910
 Liverpool   |          43.910
 Sunderland  |          43.071
 Birmingham  |          33.690
 Birmingham  |          33.690
 Leicester   |          32.201
 Southampton |          30.751
 Stoke       |          27.534
 Norwich     |          26.972
 Swansea     |          20.711
 Watford     |          20.594
 Bournemouth |          11.189
(20 rows)

Parent topic:Window Functions