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.
A DOUBLE value between 0 and 1
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:
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 |
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.