CUME_DIST

Calculates the cumulative distribution of data in a partition.

Limits

Window functions have the following limits:

Window functions are supported only in SELECT statements.
A window function cannot contain nested window functions or nested aggregate functions.
Window functions cannot be used together with aggregate functions of the same level.

Syntax

double cume_dist() over([partition_clause] [orderby_clause])

Description

Returns the cumulative distribution of a value within a partition — the ratio of rows whose values are greater than or equal to the values of the current row to all rows in the partition. The ratio is determined based on orderby_clause.

The result equals row_number_of_last_peer / partition_row_count, where:

row_number_of_last_peer: the ROW_NUMBER value of the last row in the peer group to which the current row belongs.
partition_row_count: the total number of rows in the partition.

Rows with identical ORDER BY values are treated as peers and always receive the same cumulative distribution value.

Parameters

partition_clause and orderby_clause: For parameter syntax, see windowing_definition.

Sample data

The following examples use a table named emp. Run the statements below to create the table and load the sample data.

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

The emp.txt file contains the following data:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Example

Group employees by department and calculate the cumulative salary distribution within each department, ranked from highest to lowest salary.

select deptno, ename, sal,
    concat(round(cume_dist() over (partition by deptno order by sal desc)*100,2),'%') as cume_dist
from emp;

Result:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | cume_dist  |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 33.33%     |
| 10         | KING       | 5000       | 33.33%     |
| 10         | CLARK      | 2450       | 66.67%     |
| 10         | WELAN      | 2450       | 66.67%     |
| 10         | TEBAGE     | 1300       | 100.0%     |
| 10         | MILLER     | 1300       | 100.0%     |
| 20         | SCOTT      | 3000       | 40.0%      |
| 20         | FORD       | 3000       | 40.0%      |
| 20         | JONES      | 2975       | 60.0%      |
| 20         | ADAMS      | 1100       | 80.0%      |
| 20         | SMITH      | 800        | 100.0%     |
| 30         | BLAKE      | 2850       | 16.67%     |
| 30         | ALLEN      | 1600       | 33.33%     |
| 30         | TURNER     | 1500       | 50.0%      |
| 30         | MARTIN     | 1250       | 83.33%     |
| 30         | WARD       | 1250       | 83.33%     |
| 30         | JAMES      | 950        | 100.0%     |
+------------+------------+------------+------------+

In department 10, JACCKA and KING both earn 5,000. Because they are peers, both receive the same cumulative distribution value of 33.33% (2 out of 6 rows). CLARK and WELAN, both earning 2,450, similarly share 66.67% (4 out of 6 rows).

Related functions

CUME_DIST is a window function. For other ranking and aggregation window functions, see Window functions.