Purpose
PERCENT_RANK function computes the relative rank of each row in the partition. The result is computed as follows,
Syntax
percent_rank::=
Usage Notes
- PERCENT_RANK is only available as an analytic function (i.e., in combination with an over_clause). For more information, refer to Analytic Functions section.
- PERCENT_RANK does not allow the use of a window_frame_clause. It uses the entire partition as window for the computation of the result.
Example
Analytic Function
SELECT
id, department, current_salary,
PERCENT_RANK() OVER (PARTITION BY department ORDER BY current_salary) PERCENT_RANK
FROM employee_table ORDER BY department, current_salary;Result
| ID | DEPARTMENT | CURRENT_SALARY | PERCENT_RANK |
| 2001 | ACCOUNTS | 55000 | 0 |
| 2002 | ACCOUNTS | 65000 | 0.25 |
| 2004 | ACCOUNTS | 70000 | 0.5 |
| 2003 | ACCOUNTS | 80000 | 0.75 |
| 2005 | ACCOUNTS | 80000 | 0.75 |
| 1001 | HR | 55000 | 0 |
| 1002 | HR | 70000 | 0.33333333 |
| 1004 | HR | 70000 | 0.33333333 |
| 1003 | HR | 90000 | 1 |
