More on Ranking Functions

More on Ranking:

We have seen RANK(),DENSE_RANK(),ROW_NUMBER in our previous post, lets see about CUME_DIST,PERCENT_RANK() and NTILE

PERCENT_RANK:- Given with the details of sales made in a year for n number of items and list as per sales percentage.

(rank of row-1)/(total number of rows -1)=Percent_rank

select name, gross_sales,

       100*percent_rank() over ( order by gross_sales ) as pctrank

from movies

order by 2,name

Name   Gross Sales         PCTRank

Abc        35                           1

Efg          33                           2.344

Igh          30                           3.344

CUME_DIST: For cumulative ranking often used for graphical purpose.

select name, gross_sales,

100*percent_rank() over ( order by gross_sales ) as pctrank

       100*cume_dist() over ( order by gross_sales ) as cumedist

from movies

order by 2,name

Name   Gross Sales         PCTRank        Cumedist

Abc        35                           1                              1

Efg          33                           2.344                  2

Igh          30                           2.346                  2

Yhi          29                           2.12                        3

NTILE(n):-Segregate the data as per the set of records given to n-tile value

select name, gross_sales,

       ntile(3) over ( order by gross_sales desc ) as quartile

from movies

order by 2,nam Name   Gross Sales         PCTRank        Cumedist

Abc        35                           1                              1                              1

Efg          33                           2.344                  2                              1

Igh          30                           2.346                  2                              2

Yhi          29                           2.12                        3                              2

Abc1      29                           1                              1                              3

Efg2       28                           2.344                  2                                3