NTILE

​

Syntax

​

Window

ntile(buckets) OVER ([PARTITION BY key] ORDER BY ordering_key [ROWS frame])

• NTILE assigns buckets using all ordered rows in each group. A ROWS frame clause does not change the buckets. RANGE frames with numeric or time offsets are not supported.

​

Arguments

​

Returns

• Type: BIGINT
• Possible values: 1 through min(buckets, number of rows in the group).
• NULL behavior: Returns a non-NULL bucket number for each row. Invalid or NULL bucket counts raise an error.
• Empty input: An empty input produces no rows.

​

Properties

​

Usage notes

• When rows do not divide evenly, the earliest buckets get one extra row.
• When there are more buckets than rows, only one bucket number per row is emitted.
• An explicit frame does not change NTILE’s bucket assignment. All rows in the PARTITION BY group are used.

​

Examples

​

Uneven bucket sizes

WITH t("value") AS (VALUES (100), (90), (80), (70), (60), (50), (40), (30), (20), (10)) SELECT "value", ntile(4) OVER (ORDER BY "value" DESC) AS result FROM t ORDER BY "value" DESC;

​

More buckets than rows

WITH sample(id) AS (VALUES (1), (2), (3))
SELECT id, ntile(5) OVER (ORDER BY id) AS bucket FROM sample ORDER BY id;