SQL Functions (Analytic) PERCENTILE_DISC and PERCENTILE_CONT
Example
The PERCENTILE_DISC function lists the value of the first entry where the cumulative distribution is higher than the percentile that you provide using the numeric_literal parameter.
The values are grouped by rowset or partition, as specified by the WITHIN GROUP clause.
The PERCENTILE_CONT function is similar to the PERCENTILE_DISC function, but returns the average of the sum of the first matching entry and the next entry.
SELECT BusinessEntityID, JobTitle, SickLeaveHours,
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
AS "Cumulative Distribution",
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;
To find the exact value from the row that matches or exceeds the 0.5 percentile, you pass the percentile as the numeric literal in the PERCENTILE_DISC function. The Percentile Discreet column in a result set lists the value of the row at which the cumulative distribution is higher than the specified percentile.
| BusinessEntityID | JobTitle | SickLeaveHours | Cumulative Distribution | Percentile Discreet |
|---|---|---|---|---|
| 272 | Application Specialist | 55 | 0.25 | 56 |
| 268 | Application Specialist | 56 | 0.75 | 56 |
| 269 | Application Specialist | 56 | 0.75 | 56 |
| 267 | Application Specialist | 57 | 1 | 56 |
To base the calculation on a set of values, you use the PERCENTILE_CONT function. The "Percentile Continuous" column in the results lists the average value of the sum of the result value and the next highest matching value.
SELECT BusinessEntityID, JobTitle, SickLeaveHours,
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
AS "Cumulative Distribution",
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;
| BusinessEntityID | JobTitle | SickLeaveHours | Cumulative Distribution | Percentile Discreet | Percentile Continuous |
|---|---|---|---|---|---|
| 272 | Application Specialist | 55 | 0.25 | 56 | 56 |
| 268 | Application Specialist | 56 | 0.75 | 56 | 56 |
| 269 | Application Specialist | 56 | 0.75 | 56 | 56 |
| 267 | Application Specialist | 57 | 1 | 56 | 56 |
