SQL Functions (Analytic) PERCENT_RANK and CUME_DIST
Example
The PERCENT_RANK function calculates the ranking of a row relative to the row set. The percentage is based on the number of rows in the group that have a lower value than the current row.
The first value in the result set always has a percent rank of zero. The value for the highest-ranked – or last – value in the set is always one.
The CUME_DIST function calculates the relative position of a specified value in a group of values, by determining the percentage of values less than or equal to that value. This is called the cumulative distribution.
SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
AS "Cumulative Distribution"
FROM Employee;
In this example, you use an ORDER clause to partition – or group – the rows retrieved by the SELECT statement based on employees' job titles, with the results in each group sorted based on the numbers of sick leave hours that employees have used.
| BusinessEntityID | JobTitle | SickLeaveHours | Percent Rank | Cumulative Distribution |
|---|---|---|---|---|
| 267 | Application Specialist | 57 | 0 | 0.25 |
| 268 | Application Specialist | 56 | 0.333333333333333 | 0.75 |
| 269 | Application Specialist | 56 | 0.333333333333333 | 0.75 |
| 272 | Application Specialist | 55 | 1 | 1 |
| 262 | Assitant to the Cheif Financial Officer | 48 | 0 | 1 |
| 239 | Benefits Specialist | 45 | 0 | 1 |
| 252 | Buyer | 50 | 0 | 0.111111111111111 |
| 251 | Buyer | 49 | 0.125 | 0.333333333333333 |
| 256 | Buyer | 49 | 0.125 | 0.333333333333333 |
| 253 | Buyer | 48 | 0.375 | 0.555555555555555 |
| 254 | Buyer | 48 | 0.375 | 0.555555555555555 |
The PERCENT_RANK function ranks the entries within each group. For each entry, it returns the percentage of entries in the same group that have lower values.
The CUME_DIST function is similar, except that it returns the percentage of values less than or equal to the current value.
