Product Documentation

Appearance

CUME_DIST ​

Calculate the cumulative distribution of each value in an ordered set.

CUME_DIST() OVER { window_name | ( [ window_definition ] ) }

This function returns values in the range of 0 to 1. Tie values always evaluate to the same cumulative distribution value. The ORDER BY clause is not required; however, if it is omitted, the return value is 1 for all rows. The frame clause, if specified, is ignored. For the full window_definition syntax, see Syntax for Window Functions.

Examples ​

Return the cumulative distribution for teams based on an ascending order of average attendance avg_att values.

premdb=# select name, avg_att, 
cume_dist() over(order by avg_att)::dec(3,2) 
from team 
where avg_att>0
order by avg_att;
        name         | avg_att | cume_dist 
----------------------+---------+-----------
 Bournemouth          |  11.189 |      0.05
 Watford              |  20.594 |      0.10
 Swansea City         |  20.711 |      0.15
 West Bromwich Albion |  24.631 |      0.20
 Crystal Palace       |  24.636 |      0.25
 Norwich City         |  26.972 |      0.30
 Stoke City           |  27.534 |      0.35
 Southampton          |  30.751 |      0.40
 Leicester City       |  32.201 |      0.45
 Aston Villa          |  33.690 |      0.50
 West Ham United      |  34.910 |      0.55
 Tottenham Hotspur    |  35.776 |      0.60
 Everton              |  38.124 |      0.65
 Chelsea              |  41.500 |      0.70
 Sunderland           |  43.071 |      0.75
 Liverpool            |  43.910 |      0.80
 Newcastle United     |  49.754 |      0.85
 Manchester City      |  54.041 |      0.90
 Arsenal              |  59.944 |      0.95
 Manchester United    |  75.286 |      1.00
(20 rows)

Return the cumulative distribution for teams in each of three cities, based on an ascending order of capacity values.

premdb=# select name, city, capacity, 
cume_dist() over(partition by city order by capacity)::dec(3,2) 
from team 
where city in('London','Birmingham','Manchester') 
order by city;
        name         |    city    | capacity | cume_dist 
----------------------+------------+----------+-----------
 West Bromwich Albion | Birmingham |    27000 |      0.33
 Birmingham City      | Birmingham |    30016 |      0.67
 Aston Villa          | Birmingham |    42785 |      1.00
 Queens Park Rangers  | London     |    18439 |      0.11
 Fulham               | London     |    25700 |      0.22
 Crystal Palace       | London     |    26255 |      0.44
 Wimbledon            | London     |    26255 |      0.44
 Charlton Athletic    | London     |    27111 |      0.56
 West Ham United      | London     |    35016 |      0.67
 Tottenham Hotspur    | London     |    36284 |      0.78
 Chelsea              | London     |    41663 |      0.89
 Arsenal              | London     |    60260 |      1.00
 Manchester City      | Manchester |    55097 |      0.50
 Manchester United    | Manchester |    75635 |      1.00
(14 rows)

Parent topic:Window Functions