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
Smart way of Technology 