CUME_DIST Function.

[debasisdas]

CUME_DIST:-
=============
THIS FUNCTION CALCULATES THE RATIO OF THE NUMBER OF ROWS THAT HAVE A LESSER OR EQUAL RANKING TO THE TOTAL NO OF ROWS IN THE PARTITION.

PERCENT_RANK:-THIS FUNCTION CALCULATES THE RATIO OF THE ROW'S RANKING TO THE NUMBER OF ROWS IN THE PARTITION USING THE FORMULA
(RRP-1)/(NRP-1)
RRP-RANK OF ROW IN PARTITION.
NRP-NUMBER OF ROW IN PARTITION.
BOTH OF THESE FUNCTIONS UTILIZE DENSE_RANK FOR THEIR RANKINGS AND CAN BE SPECIFIED TO BE IN ASCENDING OR IN DESCNDING ORDER.

Sample Example #1
----------------------------------

SELECT DEPTNO,EMPNO,SUM(SAL) SALARY,
CUME_DIST() OVER(ORDER BY SUM(SAL)DESC)CUM_SAL,
PERCENT_RANK() OVER (ORDER BY SUM(SAL)DESC)PER_SAL
FROM EMP
GROUP BY DEPTNO,EMPNO
ORDER BY SALARY DESC;

Sample Example #2
----------------------------------

SELECT EMPNO,SUM(SAL) SALARY,
RANK() OVER(ORDER BY SUM(SAL) DESC) RANK,
DENSE_RANK() OVER(ORDER BY SUM(SAL) DESC)DENSE_RANK,
CUME_DIST() OVER(ORDER BY SUM(SAL)DESC)CUM_SAL,
PERCENT_RANK() OVER (ORDER BY SUM(SAL)DESC)PER_SAL
FROM EMP
GROUP BY EMPNO
ORDER BY RANK;

Sample Example #3
-----------------------------------
WHERE EMPLOYEE GETTING SALARY 3000 IS RANKED.
------------------------------------------------------------------------------------------

SELECT 
RANK(3000) WITHIN GROUP
(ORDER BY SUM(SAL) DESC) HYP_RANK,
DENSE_RANK(3000) WITHIN GROUP
(ORDER BY SUM(SAL) DESC) HYP_D_RANK,
CUME_DIST(3000) WITHIN GROUP
(ORDER BY SUM(SAL) DESC) HYP_CUM_RANK,
PERCENT_RANK(3000) WITHIN GROUP
(ORDER BY SUM(SAL) DESC ) HYP_PER_RANK
FROM EMP
GROUP BY EMPNO;

THE "WITHIN GROUP" CLAUSE HAS THE EFFECT OF INJECTING A FICTITIOUS ROW INTO THE RESULT SET BEFORE DETERMINING THE RANKING.