Product Documentation

Appearance

PERCENT_RANK ​

Calculate the percent ranking of each value in an ordered set.

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

The following formula is used to calculate percent rankings for each partition:

(rank - 1) / (count - 1)

This function returns values in the range of 0 to 1. The first row in any set returns a ranking of 0. The ORDER BY clause is not required; however, if it is omitted, the ranking is 0 for all rows. The frame clause, if specified, is ignored. For the full window_definition syntax, see Syntax for Window Functions.

Examples ​

Return the percent rankings for teams based on an ascending order of average attendance avg_att values.

premdb=# select name, avg_att, 
percent_rank() over(order by avg_att)::dec(3,2) 
from team 
where avg_att>0 
order by avg_att;
        name         | avg_att | percent_rank 
----------------------+---------+--------------
 Bournemouth          |  11.189 |         0.00
 Watford              |  20.594 |         0.05
 Swansea City         |  20.711 |         0.11
 West Bromwich Albion |  24.631 |         0.16
 Crystal Palace       |  24.636 |         0.21
 Norwich City         |  26.972 |         0.26
 Stoke City           |  27.534 |         0.32
 Southampton          |  30.751 |         0.37
 Leicester City       |  32.201 |         0.42
 Aston Villa          |  33.690 |         0.47
 West Ham United      |  34.910 |         0.53
 Tottenham Hotspur    |  35.776 |         0.58
 Everton              |  38.124 |         0.63
 Chelsea              |  41.500 |         0.68
 Sunderland           |  43.071 |         0.74
 Liverpool            |  43.910 |         0.79
 Newcastle United     |  49.754 |         0.84
 Manchester City      |  54.041 |         0.89
 Arsenal              |  59.944 |         0.95
 Manchester United    |  75.286 |         1.00
(20 rows)

Return percent rankings for teams in each of three cities, based on an ascending order of capacity values.

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

Parent topic:Window Functions