CUME_DIST

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

CUME_DIST (cumulative distribution) is an analytic function. It computes the relative position of a specified value in a group of values. For a row R, assuming ascending ordering, the CUME_DIST of R is the number of rows with values lower than or equal to the value of R, divided by the number of rows being evaluated (the entire query result set or a partition). The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.

Example

The following example calculates the salary percentile for each employee within each job category excluding job categories PRESIDENT and MANAGER. For example, 50% of clerks have salaries less than or equal to James.

SELECT job, ename, sal, CUME_DIST() 
   OVER (PARTITION BY job ORDER BY sal) AS cume_dist
   FROM emp
   WHERE job NOT IN ('MANAGER', 'PRESIDENT');

JOB       ENAME             SAL  CUME_DIST
--------- ---------- ---------- ----------
ANALYST   SCOTT            3000          1
ANALYST   FORD             3000          1
CLERK     SMITH             800        .25
CLERK     JAMES             950         .5
CLERK     ADAMS            1100        .75
CLERK     MILLER           1300          1
SALESMAN  WARD             1250         .5
SALESMAN  MARTIN           1250         .5
SALESMAN  TURNER           1500        .75
SALESMAN  ALLEN            1600          1