Let Us Learn Oracle - Part 41 of N [ Cume_Dist function of Oracle ]

It acts both as Aggregate and analytic function.

Analytic Example

Purpose: Within a group of rows, it calculates the cumulative distribution of a value in a group of values.

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

Formula:

Cumulative_Distribution = (R)/(N)

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 cumulative distribution of the 7th row. Henceforth, applying the values to the above formula, we get R = 7 , N = 10

So, Cumulative_Distribution = (7)/(10) = 0.7

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  ,Cume_Dist() Over(Partition By BelongsTo Order By BelongsTo,FeePerMatch) As  CumilativeDistribution
  7  From tblPlayers;

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

11 rows selected.

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

Take the case of the row where the Player ID 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

Cumulative_Distribution = 3/6 = 0.5

Aggregate Example

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

PERCENT_RANK
------------
          .6

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

Hope you have enjoyed it.Thanks for reading