NTILE
Splits the rows of a window partition into the given number of groups of roughly equal size and returns the group number of the row.
MySQL 8.4
NTILE(number_of_groups)- number_of_groups
- Number of groups, a positive integerINT
Return value
An integer (BIGINT UNSIGNED) from 1 to number_of_groups
Examples
Three groups by birth date
MySQL 8.4
SELECT member_name,
birthday,
NTILE(3) OVER (
ORDER BY birthday
) AS age_group
FROM FamilyMembers;Quartile bounds by order amount
MySQL 8.4
SELECT bucket,
MIN(total_amount) AS min_amount,
MAX(total_amount) AS max_amount,
COUNT(*) AS orders
FROM (
SELECT total_amount,
NTILE(4) OVER (
ORDER BY total_amount
) AS bucket
FROM orders
) AS t
GROUP BY bucket
ORDER BY bucket;Details
If the rows do not divide evenly, the first groups get one extra row: 8 rows split into 3 groups as 3, 3 and 2. If there are more groups than rows, each row gets its own group. Rows with the same ORDER BY value can end up in different groups, and without ORDER BY rows are distributed into groups arbitrarily.