Calculates the relative rank of the current row within an ordered data set, as
( rank - 1 ) / ( rows - 1 )
where rank is the current row’s rank within the partition, and rows is the number of rows in the partition. PERCENT_RANK always returns values from 0 to 1 inclusive. The first row in any set has a PERCENT_RANK of 0.
Syntax
PERCENT_RANK() OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ASC|DESC] )
Parameters
| Parameter | Description | Supported input types |
|---|
| Parameter | Description | |
<partition_by> | An expression used for the partition by clause. | Any |
<order_by> | An expression used for the order by clause. | Any |
Return Type
DOUBLE PRECISION
This function respects NULL values, and results will be ordered with default null ordering NULLS LAST unless otherwise specified in the ORDER BY clause.
Example
The example below calculates the relative rank of each player’s score within their level. The highest score has a PERCENT_RANK of 0, and the lowest has 1.
SELECT
nickname,
current_score,
PERCENT_RANK() OVER (PARTITION BY level ORDER BY current_score DESC) AS percent_rank
FROM
(VALUES
('kennethpark', 9, 90),
('sabrina21', 9, 85),
('rileyjon', 9, 80),
('ymatthews', 9, 79)
) AS t(nickname, level, current_score)
ORDER BY level, nickname;
| nickname text | current_score int | percent_rank double |
|---|
| kennethpark | 90 | 0 |
| rileyjon | 80 | 0.6666666666666666 |
| sabrina21 | 85 | 0.3333333333333333 |
| ymatthews | 79 | 1 |
Rows: 4Execution time: 3.04ms
