CUME_DIST
Returns the cumulative share: what part of the partition rows comes no later than the current one.
MySQL 8.1
CUME_DIST() OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- PARTITION BYoptional
- Columns that split rows into partitions, the share is computed in each one separately
- ORDER BYoptional
- Order of rows that defines the share; without it all rows get 1
Return value
A floating-point number (DOUBLE) greater than 0 and not greater than 1
Examples
CUME_DIST and PERCENT_RANK by status
MySQL 8.1
SELECT member_name,
status,
CUME_DIST() OVER (
ORDER BY status
) AS status_cume_dist,
PERCENT_RANK() OVER (
ORDER BY status
) AS status_percent_rank
FROM FamilyMembers;Cheapest quarter of products
MySQL 8.1
SELECT name,
price
FROM (
SELECT name,
price,
CUME_DIST() OVER (
ORDER BY price
) AS share_cheaper_or_equal
FROM products
) AS t
WHERE share_cheaper_or_equal <= 0.25;Details
The value is the number of rows whose ORDER BY value is not greater than the current one, divided by the number of rows in the partition. Unlike PERCENT_RANK, the current row and rows equal to it are included in the share, so the result is never 0, and for the last row it is 1. The condition CUME_DIST() <= 0.25 in the outer query selects the first quarter of the rows.