=============
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
----------------------------------
Expand|Select|Wrap|Line Numbers
- 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;
----------------------------------
Expand|Select|Wrap|Line Numbers
- 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;
-----------------------------------
WHERE EMPLOYEE GETTING SALARY 3000 IS RANKED.
------------------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- 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;