ntile

Divides an ordered partition into a requested number of buckets and returns the bucket containing each row. Bucket numbers start at 1.

Bucket sizes differ by at most one row. When the rows cannot be divided evenly, the first buckets receive an extra row. Tied ordering values can fall in different buckets.

Syntax

text

ntile(buckets) OVER ([PARTITION BY expression, ...] ORDER BY expression, ...)

Arguments

buckets — The requested bucket count. It is evaluated once and converted to an integer. A converted count less than 1 raises a SQL error.

Returned value An INTEGER bucket number. Each partition is divided independently. If the bucket count exceeds the row count, each occupied bucket contains one row and the remaining buckets are empty. A frame clause does not change the allocation.

Example Splitting accounts into two groups

Five accounts are split into buckets of three and two rows:

sql

CREATE TABLE accounts (name TEXT, revenue INTEGER);
INSERT INTO accounts VALUES ('Ada', 100), ('Grace', 90), ('Linus', 80), ('Sam', 70), ('Terry', 60);

sql

SELECT name, revenue,
       ntile(2) OVER (ORDER BY revenue DESC, name) AS bucket
FROM accounts
ORDER BY revenue DESC, name
FORMAT csv;

text

"Ada",100,1
"Grace",90,1
"Linus",80,1
"Sam",70,2
"Terry",60,2

See percent_rank for relative ranks and cume_dist for cumulative fractions that keep peers together.

ntile ​

ntile