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
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
NTILE(expr) OVER ([query_partition_clause] order_by_clause)
The value can be only a positive constant.
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:
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 | +-------+-------+------+