Outdated Version

You are viewing an older version of this section. View current production version.

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 |
+------+------+---------------------------+