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

Example:
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;

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.