Let Us Learn Oracle - Part 39 of N [ Percent_Rank function of Oracle ]

It acts both as Aggregate and analytic function.

Analytic Example

Purpose: Within a group of rows, it calculates the relative rank of a row.The range of values return by this function is between 0 to 1.The return value from this function is always NUMBER.

Syntax: Percent_Rank () Over ( [partition_by_clause] order_by_clause)

Formula:

Percent_Rank = (R -1)/(N-1)

Where ,

R => The row whose rank needs to be found

N => Total number of rows or records.

e.g. We have a set with 10 elements. We need to find the relative rank of the 7th row. Henceforth, applying the values to the above formula, we get R = 7 , N = 10

So, Percent_Rank = (7-1)/(10-1) = 0.6666666666666667

The range of values return by this function is between 0 to 1 inclusive. The return data type is always a positive number.

SQL> Select 
  2   PlayerID
  3  ,PLAYERFIRSTNAME || ' ' || PLAYERLASTNAME As PlayerName
  4    ,BelongsTo
  5    ,FeePerMatch
  6    ,Percent_Rank() Over(Partition By BelongsTo Order By BelongsTo,FeePerMatch) As PercentRank
  7   From tblPlayers;

  PLAYERID PLAYERNAME            BELONGSTO  FEEPERMATCH PERCENTRANK
---------- --------------------- ---------- ----------- -----------
         4 D Das                 India              500           0
         2 B Kadir               India             1000          .2
         1 A Raman               India             2000          .4
         3 C Nadir               India             3000          .6
         5 E Punchkar            India            12000          .8
        11 P Chopra              India                            1
         7 G Gajani              USA                999           0
         9 I Ikat                USA               5999         .25
         8 H Hariharan           USA               7000          .5
        10 J Jwar                USA               7900         .75
         6 F Fateman             USA              22000           1

11 rows selected.

Now let us understand how the relative rank for the [FeePerMatch] column has been generated.

Take the case of the row where the PlayerID is 1 i.e. 3rd from the row.

There are altogether 6 rows for the [BelongsTo] field "India".

So , we have R = 3 and N = 6.

Placing the values in our formula yields

Percent_Rank = (3 -1)/(6-1) = 2/5 = 0.4

Aggregate Example

It calculates for a hypothetical row r identified by the arguments of the function and a corresponding sort specification, the rank of row minus 1 divided by number of rows in the aggregate group

Suppose we want to calculate the percent rank of a hypothetical employee in the Emp table found under the ownership of Scott with a Salary of $2000 and a commission of .09

SQL> Select Percent_Rank(2000,.09)
  2  Within Group
  3  (Order By SAL,COMM) Percent_Rank
  4  From Emp;

PERCENT_RANK
------------
  .571428571
 

Prior to Denali CTP3, there was no inbuilt Percent_Rank function.But we have this now in the form of Alanytical function.You can read about Percent_Rank of Sql Server from here

Hope you have enjoyed it.Thanks for reading