Oracle PERCENT_RANK

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 BYclause 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.