PERCENT_RANK of Analytic Functions in Oracle SQL

PERCENT_RANK of Analytic Functions in Oracle SQL

PERCENT_RANK
PERCENT_RANK is similar to the CUME_DIST function. It return result in between 0 to 1. First row result is zero.

As Aggregate function, calculation done as the rank of row r minus 1 divided by the number of rows in the aggregate group.
As Analytic function, calculation done as the rank of r minus 1, divided by 1 less than the number of rows.

Syntax:

PERCENT_RANK (expr) within group (order by expr | desc/asc | nulls | first/last)

Examples of PERCENT_RANK Aggregate function:
Example show you the example of percent_rank function and then manually calculation show you how percent_rank function work.

SELECT PERCENT_RANK(10000) WITHIN GROUP (ORDER BY salary) AS percent_rank_sal,
ROUND(PERCENT_RANK(10000) WITHIN GROUP (ORDER BY salary)*100,2) AS percentwise
FROM employees;

PERCENT_RANK_SAL PERCENTWISE
---------------- -----------
.822429907 82.24

Calculate manually percent_Rank:

SQL> select count(*) from employees;

COUNT(*)
----------
107
SQL> select count(*) from employees where salary select 88/107 from dual;

88/107
----------
.822429907

Example of PERCENT_RANK analytic function
Note: First row result is zero.

SELECT employee_id,
department_id,
salary,
PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank_sal,
ROUND(PERCENT_RANK() OVER (ORDER BY salary)*100) AS percentwise
FROM employees where department_id = 30;

EMPLOYEE_ID DEPARTMENT_ID     SALARY PERCENT_RANK_SAL PERCENTWISE
----------- ------------- ---------- ---------------- -----------
        119            30       2500                0           0
        118            30       2600               .2          20
        117            30       2800               .4          40
        116            30       2900               .6          60
        115            30       3100               .8          80
        114            30      11000                1         100

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.