Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 68 of 121
For information on syntax and semantics, see "Analytic Functions".
PERCENT_RANK
is an analytic function, and is similar to the CUME_DIST
(cumulative distribution) function. For a row R, PERCENT_RANK
calculates the rank of R minus 1, divided by 1 less than the number of rows being evaluated (the entire query result set or a partition). The range of values returned by PERCENT_RANK
is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK
of 0.
The following example calculates, for each employee, the percent rank of the employee's salary within the department:
SELECT deptno, ename, sal, PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS pr FROM emp; DEPTNO ENAME SAL PR ---------- ---------- ---------- ---------- 10 KING 5000 0 10 CLARK 2450 .5 10 MILLER 1300 1 20 SCOTT 3000 0 20 FORD 3000 0 20 JONES 2975 .5 20 ADAMS 1100 .75 20 SMITH 800 1 30 BLAKE 2850 0 30 ALLEN 1600 .2 30 TURNER 1500 .4 30 WARD 1250 .6 30 MARTIN 1250 .6 30 JAMES 950 1
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|