MySQL CUME_DIST Function

The CUME_DIST() function in MySQL is a window function introduced in MySQL 8.0. It calculates the cumulative distribution of a value within a result set. This function determines the relative rank of a row compared to other rows, expressed as a percentage of the total rows.

The result is a value between 0 (exclusive) and 1 (inclusive).

Syntax

CUME_DIST() OVER (
    [PARTITION BY column_name]
    ORDER BY column_name [ASC | DESC]
)

PARTITION BY: Divides the result set into partitions. The function calculates the cumulative distribution within each partition.
ORDER BY: Specifies the order of rows within the partition. This order determines the rank of the rows.

How It Works

CUME_DIST() calculates the proportion of rows with values less than or equal to the current row's value in the ordered partition.
Formula: $\text{CUME\_DIST}(\text{row}) = \frac{\text{Number of rows with values <= current row value}}{\text{Total rows in the partition}}$

Examples

Example 1: Basic Usage

Suppose you have a table scores:

student_id	subject	score
1	Math	90
2	Math	80
3	Math	80
4	Math	70

To calculate the cumulative distribution of scores:

SELECT student_id, score,
       CUME_DIST() OVER (ORDER BY score DESC) AS cumulative_distribution
FROM scores;

Output:

student_id	score	cumulative_distribution
1	90	0.25
2	80	0.75
3	80	0.75
4	70	1.00

For score = 90, the cumulative distribution is $\frac{1}{4} = 0.25$ .
For score = 80, it appears twice, and the cumulative distribution includes both rows: $\frac{3}{4} = 0.75$ .
For score = 70, it includes all rows: $\frac{4}{4} = 1.00$ .

Example 2: Using `PARTITION BY`

If the the scores table includes multiple subjects:

student_id	subject	score
1	Math	90
2	Math	80
3	Math	80
4	Math	70
5	Science	85
6	Science	80

To calculate the cumulative distribution of scores for each subject:

SELECT student_id, subject, score,
       CUME_DIST() OVER (PARTITION BY subject ORDER BY score DESC) AS cumulative_distribution
FROM scores;

Output:

student_id	subject	score	cumulative_distribution
1	Math	90	0.25
2	Math	80	0.75
3	Math	80	0.75
4	Math	70	1.00
5	Science	85	0.50
6	Science	80	1.00

The cumulative distribution is calculated separately for each subject.

Example 3: Identifying Top Performers

To list students who are in the top 50% of scores:

SELECT student_id, score
FROM (
    SELECT student_id, score,
           CUME_DIST() OVER (ORDER BY score DESC) AS cumulative_distribution
    FROM scores
) ranked_scores
WHERE cumulative_distribution <= 0.5;

Output:

student_id	score
1	90

Only the top 50% of scores are included based on the cumulative distribution.

Use Cases

Percentile Analysis:
- Analyze the rank of a row compared to the total rows in a dataset.
Top/Bottom Analysis:
- Identify rows within specific percentiles (e.g., top 10%, bottom 25%).
Cumulative Rankings:
- Evaluate the cumulative distribution for reporting or analytical purposes.

Conclusion

The CUME_DIST() function is a powerful tool for analyzing data distributions in MySQL. It is especially useful in percentile rankings and comparative analyses, helping users understand the relative position of rows within partitions or the entire result set. By using PARTITION BY and ORDER BY, you can customize its behavior for various scenarios.

Souy Soeng

MySQL CUME_DIST Function