All Products
Search
Document Center

NTILE

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.

Syntax

NTILE(expr) OVER ([query_partition_clause] order_by_clause)

Parameters

Parameter

Description

expr

The value can be only a positive constant.

OVER

Uses the OVER clause to define a window for calculation.

Return type

Data of the NUMERIC type is returned.

Examples

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:

CREATE TABLE course
(
  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);
COMMIT;

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