All Products
Document Center


Last Updated: Jun 18, 2021

The NTILE function divides an ordered dataset into a number of buckets that are indicated by expr and assigns an appropriate bucket number to each row. The bucket number ranges from 1 to expr. For each partition, the value of expr must be resolved to a positive constant. If expr is a non-integer constant, ApsaraDB for OceanBase truncates this value to an integer. The return value is NUMBER.

The number of rows in the buckets can differ by at most 1. Each bucket is assigned with a remainder value (the remainder of the number of rows divided by the number of buckets). This starts from bucket 1. If expr is greater than the number of rows, a number of buckets equal to the number of rows are filled and the remaining buckets are empty.

You cannot use NTILE or other analytic functions to nest analytic functions. However, you can use other built-in function expressions in expr.


NTILE(expr) OVER ([query_partition_clause] order_by_clause)





The value can be only a positive constant.


Uses the OVER clause to define a window for calculation.

Return type

Data of the NUMERIC type is returned.


Categorize students to four levels based on scores to determine awards for students. To create the course table and insert data into the table, execute the following statements:

  name VARCHAR(8),
  grade NUMBER
INSERT INTO course VALUES('Linda',50);
INSERT INTO course VALUES('Tan',85);
INSERT INTO course VALUES('Tom',90);
INSERT INTO course VALUES('John',95);
INSERT INTO course VALUES('Mery',55);
INSERT INTO course VALUES('Peter',60);
INSERT INTO course VALUES('Jack',65);
INSERT INTO course VALUES('Rose',70);
INSERT INTO course VALUES('Tonny',75);
INSERT INTO course VALUES('Apple',80);

Execute the following statement:

SELECT name, grade, ntile(4) OVER (ORDER BY grade DESC) til FROM course;

The following query result is returned:

| NAME  | GRADE | TIL  |
| John  |    95 |    1 |
| Tom   |    90 |    1 |
| Tan   |    85 |    1 |
| Apple |    80 |    2 |
| Tonny |    75 |    2 |
| Rose  |    70 |    2 |
| Jack  |    65 |    3 |
| Peter |    60 |    3 |
| Mery  |    55 |    4 |
| Linda |    50 |    4 |