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.1
NTILE(number_of_groups) OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- number_of_groupsINT
- Number of groups, a positive integer
- PARTITION BYoptional
- Columns that split rows into partitions, groups are formed in each partition separately
- ORDER BYoptional
- Order in which rows are distributed into groups; without it the distribution is arbitrary
Return value
An integer (BIGINT UNSIGNED) from 1 to number_of_groups
Examples
Three groups by birth date
MySQL 8.1
SELECT member_name,
birthday,
NTILE(3) OVER (
ORDER BY birthday
) AS age_group
FROM FamilyMembers;Quartile bounds by order amount
MySQL 8.1
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.