CUME_DIST

Database: MySQL

68747470733a2f2f7374796c65732e7265646469746d656469612e636f6d2f74355f32716d366b2f7374796c65

Topic

CUME_DIST() over_clause

Returns the cumulative distribution of a value within a group of
values; that is, the percentage of partition values less than or equal
to the value in the current row. This represents the number of rows
preceding or peer with the current row in the window ordering of the
window partition divided by the total number of rows in the window
partition. Return values range from 0 to 1.

This function should be used with ORDER BY to sort partition rows into
the desired order. Without ORDER BY, all rows are peers and have value
N/N = 1, where N is the partition size.

over_clause is as described in
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

Example

mysql> SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
CUME_DIST() OVER w AS 'cume_dist',
PERCENT_RANK() OVER w AS 'percent_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
+------+------------+--------------------+--------------+
| val | row_number | cume_dist | percent_rank |
+------+------------+--------------------+--------------+
| 1 | 1 | 0.2222222222222222 | 0 |
| 1 | 2 | 0.2222222222222222 | 0 |
| 2 | 3 | 0.3333333333333333 | 0.25 |
| 3 | 4 | 0.6666666666666666 | 0.375 |
| 3 | 5 | 0.6666666666666666 | 0.375 |
| 3 | 6 | 0.6666666666666666 | 0.375 |
| 4 | 7 | 0.8888888888888888 | 0.75 |
| 4 | 8 | 0.8888888888888888 | 0.75 |
| 5 | 9 | 1 | 1 |
+------+------------+--------------------+--------------+