Purpose
CUME_DIST() calculates the cumulative distribution of a value in a group of values. The result is in the range of (0, 1]. You can use it as an aggregate or analytic function.
Note
- As an analytic function, it calculates the relative position of a specified value in a group of values. For
row, assume that the ascending order is used.cume_distofrowdivides the number of rows with a value less than or equal torowby the number of rows computed (in the entire query result set or partition). - As an aggregate function, the arguments of the function identify a single hypothetical row in each aggregate group. Therefore, they must all be evaluated as constant expressions within each aggregate group. The constant argument expressions match the expressions in the
ORDER BYaggregate clause by position. Therefore, the number of arguments must be the same and their types must be compatible. If the specified value is duplicate with a value in the ordered group, the two duplicate values are treated as one value.
Syntax
/*Aggregate syntax*/
CUME_DIST( expr[,expr]...)
WITHIN GROUP (ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]
[,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...
)
/*Analytic syntax*/
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | The value to query. |
| expr_col | The column name of the value to query. |
| DESC | ASC | Optional. The sorting method of the list.
|
| NULLS { FIRST | LAST } | Optional. The position of NULL in expr_col after sorting.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
The return type is NUMBER.
Examples
The table emp_msg has been created.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
| DEPTNO | ENAME | SAL | MGR |
+--------+--------+------+------+
| 10 | CLARK | 2750 | 7839 |
| 10 | KING | 5300 | NULL |
| 10 | MILLER | 1600 | 7782 |
| 20 | ADAMS | 1400 | 7788 |
| 20 | FORD | 3300 | 7566 |
| 20 | JONES | 3275 | 7839 |
| 20 | SCOTT | 3300 | 7566 |
| 20 | SMITH | 1100 | 7902 |
| 30 | ALLEN | 1900 | 7698 |
| 30 | BLAKE | 3150 | 7839 |
| 30 | JAMES | 1250 | 7698 |
| 30 | MARTIN | 1550 | 7698 |
| 30 | TURNER | 1800 | 7698 |
| 30 | WARD | 1550 | 7698 |
| 30 | SCLARK | 1750 | 7839 |
+--------+--------+------+------+
15 rows in set
Example of an aggregate function
Return the position of 3300 in the sal column.
obclient> SELECT CUME_DIST(3300) WITHIN GROUP (ORDER BY sal) FROM emp_msg;
+----------------------------------------+
| CUME_DIST(3300)WITHINGROUP(ORDERBYSAL) |
+----------------------------------------+
| .9375 |
+----------------------------------------+
1 row in set
Example of an analytic function
Group the data records in the table by the deptno column and sort the data records by the sal column in descending order. Return the position of each value in the sal column.
obclient> SELECT deptno,ename,sal,
CUME_DIST ( ) over (PARTITION BY deptno ORDER BY sal DESC ) "RANK"
FROM emp_msg;
+--------+--------+------+-------------------------------------------+
| DEPTNO | ENAME | SAL | RANK |
+--------+--------+------+-------------------------------------------+
| 10 | KING | 5300 | .3333333333333333333333333333333333333333 |
| 10 | CLARK | 2750 | .6666666666666666666666666666666666666667 |
| 10 | MILLER | 1600 | 1 |
| 20 | FORD | 3300 | .4 |
| 20 | SCOTT | 3300 | .4 |
| 20 | JONES | 3275 | .6 |
| 20 | ADAMS | 1400 | .8 |
| 20 | SMITH | 1100 | 1 |
| 30 | BLAKE | 3150 | .1428571428571428571428571428571428571429 |
| 30 | ALLEN | 1900 | .2857142857142857142857142857142857142857 |
| 30 | TURNER | 1800 | .4285714285714285714285714285714285714286 |
| 30 | SCLARK | 1750 | .5714285714285714285714285714285714285714 |
| 30 | MARTIN | 1550 | .8571428571428571428571428571428571428571 |
| 30 | WARD | 1550 | .8571428571428571428571428571428571428571 |
| 30 | JAMES | 1250 | 1 |
+--------+--------+------+-------------------------------------------+
15 rows in set