SQL in a Nutshell, 2nd Edition

Name

PERCENT_RANK

Synopsis

Computes the relative rank of a row by dividing that row’s rank less 1 by the number of rows in the partition, also less 1:

(rank - 1) / (rows - 1)

Compare this calculation to that used for CUME_DIST.

SQL2003 Syntax

PERCENT_RANK( ) OVER ({window_name|(window_specification)}

DB2

DB2 does not support the PERCENT_RANK( ) window function.

Oracle

Oracle requires the ordering clause and does not allow the framing clause:

PERCENT_RANK( ) OVER ([partitioning] ordering)

Example

The following, Oracle-based example assigns a relative rank to values of NUM, partitioning the data on the ODD column:

SELECT NUM, ODD, PERCENT_RANK( ) OVER
      (PARTITION BY ODD ORDER BY NUM) cumedist
FROM test4;
       NUM        ODD   CUMEDIST
---------- ---------- ----------
         0          0          0
         2          0         .5
         4          0          1
         1          1          0
         3          1 .333333333
         3          1 .333333333
         5          1          1

Following is an explanation of the calculation behind the rank for the row in which NUM=2:

1. Row NUM=2 is the second row in its partition; thus, it ranks #2.

2. Subtract 1 from 2 to get a divisor of 1.

3. The dividend is the total number of rows in the partition, or 3.

4. Subtract 1 from 3 to get a dividend of 2.

5. The result of 1/3 is.33 repeating, as shown in the example.