Name
PERCENT_RANK
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.
ANSI SQL Standard Syntax
PERCENT_RANK( ) OVER ({window_name
| (window_specification
)}
Oracle
Oracle also requires the ordering
clause and does not allow the framing
clause:
PERCENT_RANK( ) OVER ([partitioning
]ordering
)
SQL Server
SQL Server does not support PERCENT_RANK.
Example
The following Oracle-based example assigns relative ranks to the 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:
Row NUM=2 is the second row in its partition; thus, it ranks #2.
Subtract 1 from 2 to get a divisor of 1.
The dividend is the total number of rows in the partition, or 3.
Subtract 1 from 3 to get a dividend of 2.
The result of 1/3 is .33 repeating, as shown in the example output.
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.