Divides ordered rows in a partition into a specified number of ranked groups of as equal size as possible and returns the group number for the current row. Group numbers range from 1 to N.
Syntax
bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
N | Yes | BIGINT | The number of groups to divide rows into. |
partition_clause | No | — | The column or expression to partition rows by before grouping. For syntax details, see windowing_definition. |
orderby_clause | No | — | The column or expression that determines the order in which rows are assigned to groups. For syntax details, see windowing_definition. |
Return value
Returns a BIGINT value representing the group number (1 through N) for the current row.
Usage notes
Uneven row distribution
When the total number of rows in a partition is not evenly divisible by N, the first M groups each receive one extra row. For example, if a partition has 10 rows divided into 3 groups, the distribution is 4-3-3: group 1 gets 4 rows, and groups 2 and 3 each get 3 rows.
Window function constraints
Window functions are supported only in
SELECTstatements.A window function cannot contain nested window functions or nested aggregate functions.
Window functions and aggregate functions of the same level cannot be used together.
Sample data
The examples below use a table named emp. Run the following statements to create the table and load the sample data.
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 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,,10Example
Divide employees in each department into three salary-ranked groups in descending order.
select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;Result:
+------------+------------+------------+------------+
| 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 |
+------------+------------+------------+------------+NTILE partitions rows by deptno and divides each department independently into three groups. Department 20 has 5 employees, so the groups are distributed 2-2-1: the top 2 earners fall into group 1, the next 2 into group 2, and the lowest earner into group 3. This demonstrates the uneven distribution behavior described in Usage notes.
Related functions
NTILE is a window function. For a full list of window functions in MaxCompute, see Window functions.