The cumulative distribution function, CUME_DIST, is sometimes defined as the inverse of percentile. CUME_DIST computes the normalized position of a specific value relative to the set of values in the window. The range of the function is between 0 and 1.
As the window moves down the input rows, the cumulative distribution is calculated for the expression specified in the window's ORDER BY clause. If the ORDER BY clause includes multiple expressions, the second and subsequent expressions are used to break ties if the first expression has the same value in adjacent rows. NULL values are sorted before any other value (in ascending sequence).
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 DepartmentID ORDER BY Salary DESC ) "Rank" FROM Employees WHERE State IN ( 'CA' ); |
This query returns the following result:
DepartmentID | Surname | Salary | Rank |
---|---|---|---|
200 | Savarino | 72300.00 | 0.333333333333333 |
200 | Clark | 45000.00 | 0.666666666666667 |
200 | Overbey | 39300.00 | 1 |
For more information about the syntax for the CUME_DIST function, see CUME_DIST function [Ranking].
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |