PERCENT_RANK function

The PERCENT_RANK function is an OLAP ranking function that calculates a ranking value for each row in an OLAP window, normalized to a range from 0 to 1.

Each PERCENT_RANK value is computed as the row's RANK minus 1, divided by the number of rows in the partition minus 1. Values closer to 1 generally represent higher rankings and values closer to 0 generally represent lower rankings.

Syntax

(1)
PERCENT_RANK function

1  PERCENT_RANK  ( )  %OVER clause
for Ranking functions1
Notes:

Usage

This function takes no argument, but the empty parentheses must be specified. If the optional window PARTITION clause is also specified, the rankings are calculated within the subset of rows that each partition defines. If there is a single row in the partition, its PERCENT_RANK value is 0.

Example: PERCENT_RANK function

The following query ranks sales people by the amount of their sales. 

SELECT emp_num, sales,
  PERCENT_RANK() OVER (ORDER BY sales) AS per_rank
FROM sales;

    emp_num      sales  per_rank    

        101      2,000         0    
        102      2,400       0.2    
        103      2,400       0.2     
        104      2,500       0.6     
        105      2,500       0.6     
        106      2,650       1.0