PERCENT_RANK

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