NTILE
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 ntile_example (a int, b int); INSERT INTO ntile_example VALUES(1,2),(2,2),(3,3),(4,3); INSERT INTO ntile_example VALUES(3,2),(1,1),(3,1);
SELECT a,b, NTILE(3) OVER(ORDER BY a) FROM ntile_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 | +------+------+---------------------------+