Name

CUME_DIST

Calculates the cumulative distribution, or relative rank, of the current row with regard to other rows in the same partition. The calculation for a given row is as follows:

number of peer or preceding rows / number of rows in partition

Because the result for a given row depends on the number of rows preceding that row in the same partition, it’s important to always specify an ORDER BY clause when invoking this function.

ANSI SQL Standard Syntax

CUME_DIST(  ) OVER {window_name|(window_specification)}

Oracle

Oracle does not allow the framing portion of the windowing syntax. It requires the ordering clause:

CUME_DIST(  ) OVER ([partitioning] ordering)

SQL Server

SQL Server does not support CUME_DIST.

Example

The following Oracle-based example uses CUME_DIST to generate a relative rank for each row, ordering by NUM, after partitioning the data by ODD:

SELECT NUM, ODD, CUME_DIST(  ) OVER
 (PARTITION BY ODD ORDER BY NUM) cumedist
FROM test4;
       NUM        ODD   CUMEDIST
---------- ---------- ----------
         0          0 .333333333
         2          0 .666666667
         4          0          1
         1          1        .25
         3          1        .75
         3          1        .75
         5          1          1

Following is an explanation of the calculation behind the rank for the row in which NUM=0:

  1. Because of the ORDER BY clause, the rows in the partition are ordered as follows:

    NUM=0
    NUM=2
    NUM=4
  2. There are no rows preceding NUM=0.

  3. There is one row that is a peer of NUM=0, and that is the NUM=0 row itself. Thus, the divisor is 1.

  4. There are three rows in the partition as a whole, making the dividend 3.

  5. The result of 1/3 is .33 repeating, as shown in ...

Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.