All Products
Search
Document Center

CUME_DIST

Last Updated: Jun 18, 2021

The CUME_DIST function calculates the cumulative distribution of a value in a group of values. The range of the return value is 0 < CUME_DIST <= 1 Tie values always evaluate to the same cumulative distribution value. This function uses the numeric or non-numeric data types that can be implicitly converted to numeric data types as parameters. ApsaraDB for OceanBase determines the parameter that has the highest numeric precedence, implicitly converts the remaining parameters to that data type, performs calculations, and returns NUMBER.

CUME_DIST that serves as an analytic function calculates the relative position of a specified value in a group of values. Assume that the ascending order is used for row r. The cume_dist of r is obtained by dividing the number of rows whose values are less than or equal to the value of r by the number of the calculated rows (the entire query result set or a partition).

Syntax

CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)

Parameters

Parameter

Description

expr

The expression of the NUMBER type or the types that can be implicitly converted to the NUMBER data type.

{RESPECT | IGNORE} NULLS

Specifies whether to ignore NULL values. The default value is RESPECT NULLS and indicates that NULL values are taken into consideration.

Return type

The return value is of the NUMBER data type.

Examples

To create the emp_msg table and insert data into the table, execute the following statements:

CREATE TABLE emp_msg(deptno INT, ename varchar(30), sal INT, MGR varchar(30));
INSERT INTO emp_msg VALUES(10,'CLARK', 2750, 7839);       
INSERT INTO emp_msg VALUES(10,'KING', 5300, NULL);       
INSERT INTO emp_msg VALUES(10,'MILLER', 1600, 7782);        
INSERT INTO emp_msg VALUES(20,'ADAMS', 1400, 7788);       
INSERT INTO emp_msg VALUES(20,'FORD', 3300, 7566);      
INSERT INTO emp_msg VALUES(20,'JONES', 3275, 7839);      
INSERT INTO emp_msg VALUES(20,'SCOTT', 3300, 7566);    
INSERT INTO emp_msg VALUES(20,'SMITH', 1100, 7902);   
INSERT INTO emp_msg VALUES(30,'ALLEN', 1900, 7698); 
INSERT INTO emp_msg VALUES(30,'BLAKE', 3150, 7839); 
INSERT INTO emp_msg VALUES(30,'JAMES', 1250, 7698); 
INSERT INTO emp_msg VALUES(30,'MARTIN', 1550, 7698);
INSERT INTO emp_msg VALUES(30,'TURNER', 1800, 7698);
INSERT INTO emp_msg VALUES(30,'WARD', 1550, 7698);

To return the CUME_DIST value of the specified column sal in the same deptno, execute the following statement:

SELECT deptno , ename , sal, cume_dist ( ) over ( partition BY deptno ORDER BY sal DESC ) "RANK"
FROM emp_msg WHERE sal>2000;

The following query result is returned:

+--------+-------+------+-------------------------------------------+
| DEPTNO | ENAME | SAL  | RANK                                      |
+--------+-------+------+-------------------------------------------+
|     10 | KING  | 5300 |                                        .5 |
|     10 | CLARK | 2750 |                                         1 |
|     20 | FORD  | 3300 | .6666666666666666666666666666666666666667 |
|     20 | SCOTT | 3300 | .6666666666666666666666666666666666666667 |
|     20 | JONES | 3275 |                                         1 |
|     30 | BLAKE | 3150 |                                         1 |
+--------+-------+------+-------------------------------------------+