Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Analytic Functions / Oracle PERCENT_RANK

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:

Oracle PERCENT_RANK Function Example

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:

Oracle PERCENT_RANK over partition example

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.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle NTILE
Next Oracle RANK

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

Oracle Analytic Functions

  • CUME_DIST
  • DENSE_RANK
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • RANK
  • ROW_NUMBER

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.