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
)
Code language: SQL (Structured Query Language) (sql)
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],... ]
Code language: SQL (Structured Query Language) (sql)
The ORDER BY
clause specifies the order of rows in each partition.
The query_partition_clause
has the following syntax:
PARTITION BY expression1 [,expression2,..]
Code language: SQL (Structured Query Language) (sql)
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
Let’s take some examples of using the PERCENT_RANK() function.
1) 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;
Code language: SQL (Structured Query Language) (sql)
The following picture shows the result:
2) Using Oracle PERCENT_RANK() function over 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);
Code language: SQL (Structured Query Language) (sql)
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.