NTILE
On this page
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 |
+------+------+---------------------------+Last modified: February 16, 2023