Oracle PERCENT_RANK Function

A rank of 3 means little without knowing how many rows there are. PERCENT_RANK expresses a row's rank as a fraction of its group, from 0 for the first row to 1 for the last, so positions can be compared across groups of different sizes: where does this salary stand within its department?

Code for This Guide

The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

percent_rank() over ([partition by expr [, ...]] order by expr [, ...])

PERCENT_RANK = (RANK - 1) / (number of rows in the partition - 1). The first row is always 0 and the last 1; a partition of one row gives 0.

Relative Position of Salaries

This query shows PERCENT_RANK and CUME_DIST for the salaries of one department.

Example:

select last_name, salary,
       round(percent_rank() over (order by salary), 3) as percent_rank,
       round(cume_dist()    over (order by salary), 3) as cume_dist
from   employees
where  department_id = 40
order  by salary;

Output:

LAST_NAME       SALARY    PERCENT_RANK    CUME_DIST
____________ _________ _______________ ____________
Tanaka            7600               0          0.1
Gupta             7800           0.111          0.2
Hussain           8000           0.222          0.3
Johnson           8200           0.333          0.4
Martínez         13000           0.444          0.5
Patel            13800           0.556          0.6
Khan             14200           0.667          0.7
Thompson         14500           0.778          0.8
Kapoor           21000           0.889          0.9
Evans            34000               1            1

10 rows selected.

With ten employees, each step is 1/9: 0, 0.111, 0.222, up to 1. CUME_DIST, in the last column, starts at 0.1 instead, because it counts the current row too.

Within Each Group

Example:

-- where each employee stands within their own department
select department_id, last_name, salary,
       round(percent_rank() over (partition by department_id order by salary), 2) as pct_rank
from   employees
where  department_id in (50, 60)
order  by department_id, salary;

Output:

   DEPARTMENT_ID LAST_NAME       SALARY    PCT_RANK
________________ ____________ _________ ___________
              50 Schmidt           8300           0
              50 Williams          8800        0.33
              50 Reddy            15500        0.67
              50 Mehta            33000           1
              60 Mensah            7400           0
              60 Costa            11200         0.5
              60 Okafor           29000           1

7 rows selected.

The department of four goes 0, 0.33, 0.67, 1; the department of three 0, 0.5, 1. Each top earner is at 1 regardless of department size.

Things to Know

• Tied rows share a PERCENT_RANK, as they share a RANK.
• PERCENT_RANK(value) WITHIN GROUP (ORDER BY ...) is the aggregate form, for where a hypothetical value would fall.
• Multiply by 100 for a percentile-style number.

Related Guides

• Oracle RANK Function

Conclusion

PERCENT_RANK returns a row's rank as a fraction from 0 to 1 within its partition, (rank - 1) / (rows - 1). Use it to compare relative positions across groups of different sizes.