Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 23 of 121
For information on syntax and semantics, see "Analytic Functions".
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.
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
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|