CUME_DIST Function [Analytical]

The CUME_DIST function is a rank analytical function that calculates the relative position of one value among a group of rows. It returns a decimal value between 0 and 1.

Syntax

CUME_DIST () OVER (window-spec)

Returns

A DOUBLE value between 0 and 1

Example

The following example returns a result set that provides a cumulative distribution of the salaries of employees who live in California:

SELECT DepartmentID, Surname, Salary,CUME_DIST() OVER (PARTITION BY DepartmentIDORDER BY Salary DESC) "Rank"FROM Employees WHERE State IN ('CA');

The returned result set is:

CUME_DIST result set

DepartmentID

Surname

Salary

Rank

200

Savarino

72,300.000

0.333333

200

Clark

45,000.000

0.666667

200

Overbey

39,300.000

1.000000

Usage

Sybase IQ calculates the cumulative distribution of a value of x in a set S of size N using:CUME_DIST(x) = number of values in S coming before and including x in the specified order / N

Composite sort-keys are not currently allowed in the CUME_DIST function. You can use composite sort-keys with any of the other rank functions.

You can specify elements of window-spec either in the function syntax (inline), or with a WINDOW clause in the SELECT statement. The window-spec must contain the ORDER BY clause, and cannot contain a ROWS or RANGE clause.

Note: DISTINCT is not supported.

Standards and Compatibility

  • SQL—ISO/ANSI SQL compliant. SQL feature T612.

  • Sybase—Compatible with SQL Anywhere.