Product Documentation

Appearance

MEDIAN (window function) ​

Return the median value from an ordered set, equivalent to percentile_cont(0.5).

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

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

Parent topic:Window Functions