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
)
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

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:

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);   
Code language: SQL (Structured Query Language) (sql)

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.