Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query optimization, and usability that you won’t want to miss.

NTILE

Ranking function. Divides the input into the specified number of buckets and returns the bucket number of the current row.

Syntax

NTILE ( bucket_count ) OVER (
    [PARTITION BY (col | expr), ...] 
    [ORDER BY (col | expr), ...]
)

Arguments

  • bucket_count: an integer.

Return Type

An integer

Examples

create table example (a int, b int);

insert into example values(1,2),(2,2),(3,3),(4,3);

insert into example values(3,2),(1,1),(3,1);

select a,b, ntile(3) over(order by a) from example;
+------+------+---------------------------+
| a    | b    | ntile(3) over(order by a) |
+------+------+---------------------------+
|    1 |    2 |                         1 |
|    1 |    1 |                         1 |
|    2 |    2 |                         1 |
|    3 |    3 |                         2 |
|    3 |    2 |                         2 |
|    3 |    1 |                         3 |
|    4 |    3 |                         3 |
+------+------+---------------------------+