Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL PERCENTILE_CONT Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: PERCENTILE_CONT

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.

  1. Calculate Row Number (R) of each row within the group using percentile value
  2. Get CEIL(C) and FLOOR(F) value for the row number
  3. If (C=F=R) then value at R
  4. 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...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 308 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?