Purpose
NTILE() is an analytic function. It divides an ordered dataset into several groups and assigns an appropriate group number to each row. The group number is in the range from 1 to expr.
Syntax
NTILE (expr) OVER ([ query_partition_clause ] order_by_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | The desired number of groups.
NTILE() or any other analytic function. However, you can use other built-in function expressions in expr. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
The return type is NUMERIC.
Examples
The table emp_msg has been created. Query the emp_msg table. Divide the employees into 5 levels based on the values in the sal column.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
| DEPTNO | ENAME | SAL | MGR |
+--------+--------+------+------+
| 10 | CLARK | 2750 | 7839 |
| 10 | KING | 5300 | NULL |
| 10 | MILLER | 1600 | 7782 |
| 20 | ADAMS | 1400 | 7788 |
| 20 | FORD | 3300 | 7566 |
| 20 | JONES | 3275 | 7839 |
| 20 | SCOTT | 3300 | 7566 |
| 20 | SMITH | 1100 | 7902 |
| 30 | ALLEN | 1900 | 7698 |
| 30 | BLAKE | 3150 | 7839 |
| 30 | JAMES | 1250 | 7698 |
| 30 | MARTIN | 1550 | 7698 |
| 30 | TURNER | 1800 | 7698 |
| 30 | WARD | 1550 | 7698 |
| 30 | SCLARK | 1750 | 7839 |
+--------+--------+------+------+
15 rows in set
obclient> SELECT ename,sal,NTILE(5) OVER (ORDER BY sal DESC) sal_ntile FROM emp_msg ORDER BY sal DESC;
+--------+------+-----------+
| ENAME | SAL | SAL_NTILE |
+--------+------+-----------+
| KING | 5300 | 1 |
| FORD | 3300 | 1 |
| SCOTT | 3300 | 1 |
| JONES | 3275 | 2 |
| BLAKE | 3150 | 2 |
| CLARK | 2750 | 2 |
| ALLEN | 1900 | 3 |
| TURNER | 1800 | 3 |
| SCLARK | 1750 | 3 |
| MILLER | 1600 | 4 |
| MARTIN | 1550 | 4 |
| WARD | 1550 | 4 |
| ADAMS | 1400 | 5 |
| JAMES | 1250 | 5 |
| SMITH | 1100 | 5 |
+--------+------+-----------+
15 rows in set