PERCENT_RANK window function
Calculates the percent rank of a given row. The percent rank is determined using this formula:
(x - 1) / (the number of rows in the window or partition - 1)
where x is the rank of the current row. The following dataset illustrates use of this formula:
Row# Value Rank Calculation PERCENT_RANK
1 15 1 (1-1)/(7-1) 0.0000
2 20 2 (2-1)/(7-1) 0.1666
3 20 2 (2-1)/(7-1) 0.1666
4 20 2 (2-1)/(7-1) 0.1666
5 30 5 (5-1)/(7-1) 0.6666
6 30 5 (5-1)/(7-1) 0.6666
7 40 7 (7-1)/(7-1) 1.0000
The return value range is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0.
Syntax
PERCENT_RANK () OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )
Arguments
- ( )
-
The function takes no arguments, but the empty parentheses are required.
- OVER
-
A clause that specifies the window partitioning. The OVER clause cannot contain a window frame specification.
- PARTITION BY partition_expression
-
Optional. An expression that sets the range of records for each group in the OVER clause.
- ORDER BY order_list
-
Optional. The expression on which to calculate percent rank. The expression must have either a numeric data type or be implicitly convertible to one. If ORDER BY is omitted, the return value is 0 for all rows.
If ORDER BY does not produce a unique ordering, the order of the rows is nondeterministic. For more information, see Unique ordering of data for window functions.
Return type
FLOAT8
Examples
The following example calculates the percent rank of the sales quantities for each seller:
select sellerid, qty, percent_rank()
over (partition by sellerid order by qty)
from winsales;
sellerid qty percent_rank
----------------------------------------
1 10.00 0.0
1 10.64 0.5
1 30.37 1.0
3 10.04 0.0
3 15.15 0.33
3 20.75 0.67
3 30.55 1.0
2 20.09 0.0
2 20.12 1.0
4 10.12 0.0
4 40.23 1.0
For a description of the WINSALES table, see Sample table for window function examples.
