Introduction to PostgreSQL PERCENT_RANK() function

ThePERCENT_RANK() function is like theCUME_DIST() function. ThePERCENT_RANK() function evaluates the relative standing of a value within a set of values.

The following illustrates the syntax of thePERCENT_RANK() function:

PERCENT_RANK()OVER (    [PARTITION BY partition_expression, ... ]    ORDER BY sort_expression [ASC | DESC], ...)

In this syntax:

PARTITION BY

ThePARTITION BY clause divides rows into multiple partitions to which thePERCENT_RANK() function is applied.

ThePARTITION BY clause is optional. If you omit it, the function treats the whole result set as a single partition.

ORDER BY

TheORDER BY clause specifies the order of rows in each partition to which the function is applied.

Return value

ThePERCENT_RANK() function returns a result that is greater than 0 and less than or equal to 1.

0 < PERCENT_RANK()<= 1

The first value always receives a rank of zero. Tie values evaluate to the same cumulative distribution value.

PostgreSQL PERCENT_RANK() examples

We will use thesales_stats table created in theCUME_DIST() function tutorial for the demonstration.

SELECTyear,name,amountFROMactual_salesORDER BYyear,name;

1) Using PostgreSQL PERCENT_RANK() function over a result set example

The following example uses thePERCENT_RANK() function to calculate the sales percentile of each employee in 2019:

SELECT    name,amount,    PERCENT_RANK()OVER (        ORDER BY amount    )FROM    sales_statsWHERE    year = 2019;

Here is the output:

2) Using PostgreSQL PERCENT_RANK() function over a partition example

This statement uses thePERCENT_RANK() function to calculate the sales amount percentile by sales employees in both 2018 and 2019.

SELECT    name,amount,    PERCENT_RANK()OVER (PARTITION BY year        ORDER BY amount    )FROM    sales_stats;

Here is the output:

In this example:

• ThePARTITION BYclause distributed the rows in thesales_stats table into two partitions, one for 2018 and the other for 2019.
• TheORDER BY clause sorted rows in each partition by sales amount.
• ThePERCENT_RANK() function is applied to each ordered partition to calculate the percent rank.

In this tutorial, you have learned how to use the PostgreSQLPERCENT_RANK() function to calculate the relative rank of a value within a set of values.

Last updated onApril 11, 2020

On this page

• Introduction to PostgreSQL PERCENT_RANK() function
• PostgreSQL PERCENT_RANK() examples