NTILE

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

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

Arguments

bucket_count: an integer.

Return Type

An integer

Examples

SQL

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);

SQL

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