Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

PERCENTILE_CONT (window function) ​

Given an ordered set of values, this aggregate window function returns the continuous percentile value, which is the value from the set that corresponds to the specified fraction. For example, percentile_cont(0.5) returns the median value. If necessary, the percentile value is interpolated in the set.

PERCENTILE_CONT(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_cont(0.5) within group(order by avg_att) over(partition by city) 
from team where avg_att>0 order by 2 desc;
   city     | percentile_cont 
-------------+-----------------
 Manchester  |         64.6635
 Manchester  |         64.6635
 Newcastle   |          49.754
 Sunderland  |          43.071
 Liverpool   |          41.017
 Liverpool   |          41.017
 London      |          35.776
 London      |          35.776
 London      |          35.776
 London      |          35.776
 London      |          35.776
 Leicester   |          32.201
 Southampton |          30.751
 Birmingham  |         29.1605
 Birmingham  |         29.1605
 Stoke       |          27.534
 Norwich     |          26.972
 Swansea     |          20.711
 Watford     |          20.594
 Bournemouth |          11.189
(20 rows)

Parent topic:Window Functions