PostgreSQL – PERCENT_RANK Function
The PERCENT_RANK() function is used to calculate the relative rank of a row within a result set as a fraction between 0 and 1. It gives the percentage of rows that are ranked lower than the current row. This function is often used to determine the percentile of a particular value within a dataset.
The syntax of the PERCENT_RANK() function is as follows:
PERCENT_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression)PARTITION BY is an optional clause that divides the result set into partitions or groups. The ranking is calculated separately within each partition. If omitted, the ranking is calculated across the entire result set.
ORDER BY specifies the column(s) by which you want to order the result set for ranking.
Below is an example of using the PERCENT_RANK() function in PostgreSQL:
Suppose you have a table named “scores” with columns “player_name” and “score”, and you want to calculate the percent rank of players’ scores:
SELECT player_name, score, PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank FROM scores;In this example, the PERCENT_RANK() function calculates the percent rank of players’ scores in descending order. It assigns a value between 0 and 1 to each row, indicating the percentage of rows with lower scores. For example:
player_name | score | percent_rank
-------------+-------+--------------
Player A | 100 | 0.0
Player B | 95 | 0.33
Player C | 90 | 0.66
Player D | 85 | 1.0
In this output, “Player A” has the highest score and therefore has a percent rank of 0.0, while “Player D” has the lowest score and a percent rank of 1.0.
The PERCENT_RANK() function can be useful when you want to understand where a particular value stands in comparison to other values within a dataset. It’s commonly used in statistical analysis and when calculating percentiles for data distribution.