The Oracle PL/SQL function PERCENTILE_CONT is a built in inverse distribution analytic function which works on a continuous distribution model to get the median of a column value. It takes a numeric input which is assumed as a percentile rank and a grouping specification as input. The method of linear interpolation is as below.
- Calculate Row Number (R) of each row within the group using percentile value
- Get CEIL(C) and FLOOR(F) value for the row number
- If (C=F=R) then value at R
- Else, (C-R) * (Value at F) + (R-F) * (Value at C)
PERCENTILE_CONT() WITHIN GROUP (ORDER BY NULLS )
The SQL statement below lists the median salary which has a percentile of 0.5 within each department:
SQL> SELECT EMPNO, DEPTNO, SALARY , PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER_BY SALARY DESC)
OVER (PARTITION BY DEPTNO) "Percentile_Cont"
Related Code Snippets:
- PERCENTILE_CONT - Inverse distribution function that assumes a continuous distribution model. It t...