MySQL CUME_DIST Function

Summary: in this tutorial, you will learn how to use the MySQL CUME_DIST() function to calculate cumulative distribution value.

Overview of MySQL CUME_DIST() Function

The CUME_DIST() is a window function that returns the cumulative distribution of a value within a set of values. It represents the number of rows with values less than or equal to that row’s value divided by the total number of rows.

The returned value of the CUME_DIST() function is greater than zero and less than or equal one (0 < CUME_DIST() <= 1). The repeated column values receive the same CUME_DIST() value.

The following shows the syntax of the CUME_DIST() function:

CUME_DIST() OVER (
	PARTITION BY expr, ...
	ORDER BY expr [ASC | DESC], ...
)

In this syntax, the  PARTITION BY clause divides the result set returned by the FROM clause into partitions to which the CUME_DIST() function applies.

The ORDER BY the clause specifies the logical order of the rows in each partition or the whole result set in case the PARTITION BY is omitted. The CUME_DIST() the function calculates the cumulative distribution value of each row based on its order in the partition.

The approximate formula of the CUME_DIST() the function is as follows:

ROW_NUMBER() / total_rows

MySQL CUME_DIST() function example

Let’s create a table named scores with some sample data for the demonstration:

CREATE TABLE scores (
    name VARCHAR(20) PRIMARY KEY,
    score INT NOT NULL
);

INSERT INTO
	scores(name, score)
VALUES
	('Smith',81),
	('Jones',55),
	('Williams',55),
	('Taylor',62),
	('Brown',62),
	('Davies',84),
	('Evans',87),
	('Wilson',72),
	('Thomas',72),
	('Johnson',100);

The following statement finds the cumulative distribution on the score in the result set:

SELECT
	name,
    score,
    ROW_NUMBER() OVER (ORDER BY score) row_num,
    CUME_DIST() OVER (ORDER BY score) cume_dist_val
FROM
	scores;

Here is the output:

In this example, the score is sorted in ascending order from 55 to 100. Note that the ROW_NUMBER() the function was added for reference.

So how the CUME_DIST() the function performs calculation?

For the first row, the function finds the number of rows in the result set, which has a value less than or equal to 55. The result is 2. Then CUME_DIST() function divides 2 by the total number of rows which is 10: 2/10. the result is 0.2 or 20%. The same logic is applied to the second row.

For the third row, the function finds the number of rows with values less than or equal to 62. There are four rows. Then the result of the CUME_DIST() function is: 4/10 = 0.4 which is 40%.

The same calculation logic is applied to the remaining rows.

In this tutorial, you have learned how to use the MySQL CUME_DIST() function to calculate the cumulative distribution of a value in a set of values.