Splits rows of data in a partition into N groups of equal size and returns the number of the group to which the current row belongs. The group number ranges from 1 to N.
Limits
Before you use window functions, take note of the following limits:
Window functions are supported only in
SELECTstatements.A window function cannot contain nested window functions or nested aggregate functions.
You cannot use window functions together with aggregate functions of the same level.
Syntax
bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])Description
Splits rows of data in a partition into N groups of equal size and returns the number of the group to which the specified row belongs. If data in the partition cannot be split into N groups of equal size, one more row is preferentially allocated to the first M groups.
Parameters
N: required. This parameter specifies the number of splits. The input value is of the BIGINT type.
partition_clause and orderby_clause: For more information about these parameters, see windowing_definition.
Return value
A value of the BIGINT type is returned.
Sample data
This section provides sample source data and examples for you to understand how to use the functions. Create a table named emp and insert the sample data into the table. Sample statement:
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
tunnel upload emp.txt emp;The emp.txt file contains the following sample data:
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10Examples
Divide all employees into three groups based on the sal column in descending order and obtain the number of the group to which each employee belongs. Sample statement:
select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;The following result is returned:
+------------+------------+------------+------------+
| deptno | ename | sal | nt3 |
+------------+------------+------------+------------+
| 10 | JACCKA | 5000 | 1 |
| 10 | KING | 5000 | 1 |
| 10 | CLARK | 2450 | 2 |
| 10 | WELAN | 2450 | 2 |
| 10 | TEBAGE | 1300 | 3 |
| 10 | MILLER | 1300 | 3 |
| 20 | SCOTT | 3000 | 1 |
| 20 | FORD | 3000 | 1 |
| 20 | JONES | 2975 | 2 |
| 20 | ADAMS | 1100 | 2 |
| 20 | SMITH | 800 | 3 |
| 30 | BLAKE | 2850 | 1 |
| 30 | ALLEN | 1600 | 1 |
| 30 | TURNER | 1500 | 2 |
| 30 | MARTIN | 1250 | 2 |
| 30 | WARD | 1250 | 3 |
| 30 | JAMES | 950 | 3 |
+------------+------------+------------+------------+Related functions
NTILE is a window function. For more information about the functions that are used to calculate the sum of data of columns in a window and to sort data, see Window functions.