MySQL Cume_Dist Function – A Complete Guide

In this tutorial, we will study the MySQL Cume_Dist() function which is used to calculate the cumulative distribution value.

Also read: MySQL Nth_Value Function – A Complete Reference

Introduction

Cume_Dist() function is a window function that is used to calculate the cumulative distribution value among a set of values. Here the Row number less than or equal to the row’s value is divided by the total number of rows. The formula is ROW_NUMBER () / total_rows.

The Cume_Dist() function calculates the cumulative distribution value of each row based on its order in the partition.

Syntax of MySQL CUME_DIST()

CUME_DIST() OVER (
	PARTITION BY expr, ...
	ORDER BY expr [ASC | DESC], ...
)Code language: SQL (Structured Query Language) (sql)

The value returned by Cume_Dist() function is greater than 0 and less than or equal to 1. Partition By clause is used to divide the result set and Order By is used to specify the order of the rows in the result set.

Example of MySQL Cume_Dist()

First, we will create a table named detail and insert some data into it. The code for this is-

Create Table detail (
    name Varchar(20) Primary Key,
    value Int Not Null
    );
Insert into
    detail (name, value)
    values
    ('Peter', 45),
    ('David', 98),
    ('John' , 67),
    ('Lily', 88),
    ('Lisa', 33),
    ('William', 100),
    ('Chuck', 93),
    ('Taylor', 55);Code language: SQL (Structured Query Language) (sql)

Now we will find the cumulative distribution of value in the result set. The code for this is-

Select
    name,
    value,
    ROW_NUMBER() OVER (ORDER BY score) row_num,
    CUME_DIST() OVER (ORDER BY score) cume_dist_val
FROM
detail;Code language: SQL (Structured Query Language) (sql)

Output-

Working of Cume_Dist() Function

In the first row, the function first finds the number of rows in the result set which has a value less than or equal to 33 and such rows is only 1. Then CUME_DIST() function divides 1 by the total number of rows which is 8. 1/8 is 0.125. The same logic is applied to the second row, third row, and so on.

In this way, Cume_Dist() function finds the cumulative distribution of each row in the result set.

Conclusion

In this tutorial, we studied MySQL Cume_Dist() window function which helps us to find the cumulative distribution of each row in the result set. For more reference, check the official documentation of MySQL.