Purpose
You can call this function to divide an ordered dataset into several groups and assign an appropriate group number to each row. It is an analytic function. 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 | Specifies the desired number of groups.
Notice Do not use NTILE or any other analytic function to nest this 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 about analytic functions, see Analytic functions. |
Return types
This function returns data of theNUMBER data type.
Examples
Assume that you have created the table emp_msg. 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