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

Remarks

SAP 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.

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