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.
