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