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.
PostgreSQL 17.5
NTILE(number_of_groups) OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- number_of_groupsinteger
- 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
Return value
An integer from 1 to number_of_groups
Examples
Three groups by birth date
PostgreSQL 17.5
SELECT member_name,
birthday,
NTILE(3) OVER (
ORDER BY birthday
) AS age_group
FROM FamilyMembers;Price quartiles in payments
PostgreSQL 17.5
SELECT payment_id,
unit_price,
NTILE(4) OVER (
ORDER BY unit_price
) AS price_quartile
FROM Payments;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.