Tag Archives: CUME_DIST() with partition

Comulative Distribution ( CUME_DIST ) Analytic Functions in Oracle SQL

Comulative Distribution CUME_DIST Analytic Functions in Oracle SQL

Cumulative distribution function (CDF) is method to describe the distribution of random variables.
The CDF can be defined for any kind of random variable (discrete, continuous, and mixed).
CUME_DIST calculates the cumulative distribution of a value in a group of values.
range of values returned by CUME_DIST is >0 to <=1

Calculates the salary percentile for each employee in the Employee table using CUME_DIST function.

SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees
-- WHERE job_id LIKE 'PU%'
ORDER BY job_id, last_name, salary, cume_dist;

---------- --------------- ---------- ----------
AC_ACCOUNT Gietz                 8300          1
AC_MGR     Higgins              12008          1
AD_ASST    Whalen                4400          1
AD_PRES    King                 24000          1
AD_VP      De Haan              17000          1
AD_VP      Kochhar              17000          1
FI_ACCOUNT Chen                  8200         .8
FI_ACCOUNT Faviet                9000          1
FI_ACCOUNT Popp                  6900         .2
FI_ACCOUNT Sciarra               7700         .4
FI_ACCOUNT Urman                 7800         .6