Definition:

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)

**Example Syntax:**

PERCENTILE_CONT() WITHIN GROUP (ORDER BY NULLS )

OVER ();

**Example Usage:**

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"

FROM EMPLOYEE

Related Links:

Related Code Snippets:

- PERCENTILE_CONT - Inverse distribution function that assumes a continuous distribution model. It t...