The Oracle PL/SQL function PERCENTILE_DISC is a built in inverse distribution analytic function which works on discrete distribution model to get the median of a column value. It takes a numeric input which is assumed as percentile rank and grouping specification as input. It returns the least value whose percentile is greater than or equal to the given percentile.
PERCENTILE_DISC() WITHIN GROUP (ORDER BY NULLS )
The SQL statement below lists the median salary which is greater than or equal to percentile of 0.5 within each department:
SQL> SELECT EMPNO, DEPTNO, SALARY , PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER_BY SALARY DESC)
OVER (PARTITION BY DEPTNO) "Percentile_DISC"
Related Code Snippets:
- PERCENTILE_DISC - An inverse distribution function that assumes a discrete distribution model. It ...