Summary: in this tutorial, you will learn how to calculate a cumulative distribution of a value in a set of values by using the Oracle PERCENT_RANK()
function.
Introduction to Oracle PERCENT_RANK()
function
The PERCENT_RANK()
function is similar to the CUME_DIST()
function. The PERCENT_RANK()
function calculates the cumulative distribution of a value in a set of values. The result of PERCENT_RANK()
function is between 0 and 1, inclusive. Tie values evaluate to the same cumulative distribution value.
The following illustrates the syntax of the Oracle PERCENT_RANK()
function:
PERCENT_RANK() OVER ( [ query_partition_clause ] order_by_clause )
Because PERCENT_RANK()
is order sensitive, the order_by_clause
is required. The order_by_clause
has the following form:
ORDER BY expression1 [ASC | DESC ] [NULLS FIRST | LAST] [, expression2 [ASC | DESC ] [NULLS FIRST | LAST],... ]
The ORDER BY
clause specifies the order of rows in each partition.
The query_partition_clause
has the following syntax:
PARTITION BY expression1 [,expression2,..]
The PARTITION BY
clause divides the result set into multiple partitions. It is an optional clause. Omitting this clause means that the function will treat the whole result set as a single partition.
Oracle PERCENT_RANK()
examples
Using Oracle PERCENT_RANK()
function over the result set example
The following statement calculates the sales percentile for each salesman in 2017:
SELECT
salesman_id,
sales,
ROUND(
PERCENT_RANK() OVER (
ORDER BY sales DESC
) * 100,2) || '%' percent_rank
FROM
salesman_performance
WHERE
YEAR = 2017;
The following picture shows the result:

Using Oracle PERCENT_RANK()
function over the partition example
The following statement calculates the sales percentile for each salesman in 2016 and 2017.
SELECT
salesman_id,
year,
sales,
ROUND(PERCENT_RANK() OVER (
PARTITION BY year
ORDER BY sales DESC
) * 100,2) || '%' percent_rank
FROM
salesman_performance
WHERE
year in (2016,2017);
The output is:

In this example:
- The
PARTITION BY
clause divided the result set into two partitions by year, 2016 and 2017. - The
ORDER BY
clause sorted rows in each partition by sales amount from high to low. - The
PERCENT_RANK()
function was then applied to the value of each row in each partition.
In this tutorial, you have learned how to use the Oracle PERCENT_RANK()
function to calculate the cumulative distribution of a value in a set of values.