MaxCompute:NTH_VALUE

Limits

Before you use window functions, take note of the following limits:

• Window functions are supported only in SELECT statements.

• 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

nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

Description

Returns the value of expr that corresponds to the Nth row in a window.

Parameters

• expr: required. The expression that is used to calculate the returned result.

• number: required. A value of the BIGINT type. The value must be an integer greater than or equal to 1. If the input value is 1, this function is equivalent to FIRST_VALUE.

• ignore_nulls: optional. A value of the BOOLEAN type. This parameter specifies whether to ignore null values. Default value: false. If this parameter is set to true, a non-null value of expr that corresponds to the Nth row of a window is returned.

• partition_clause, orderby_clause, and frame_clause: For more information about these parameters, see windowing_definition.

Return value

A value of the same data type as expr is returned.

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;

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,,10

Examples

Group all employees by department and return the sixth row of data in each group. Sample statement:

• If order by is not specified, the rows from the first row to the last row belong to the current window. The value of the sixth row in the current window is returned.

  select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;

  The following result is returned:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | nth_value  |
  +------------+------------+------------+------------+
  | 10         | TEBAGE     | 1300       | 2450       |
  | 10         | CLARK      | 2450       | 2450       |
  | 10         | KING       | 5000       | 2450       |
  | 10         | MILLER     | 1300       | 2450       |
  | 10         | JACCKA     | 5000       | 2450       |
  | 10         | WELAN      | 2450       | 2450       |   -- This row is the sixth row of the current window. 
  | 20         | FORD       | 3000       | NULL       |
  | 20         | SCOTT      | 3000       | NULL       |
  | 20         | SMITH      | 800        | NULL       |
  | 20         | ADAMS      | 1100       | NULL       |
  | 20         | JONES      | 2975       | NULL       |   -- This current window has less than six rows, and null is returned. 
  | 30         | TURNER     | 1500       | 2850       |
  | 30         | JAMES      | 950        | 2850       |
  | 30         | ALLEN      | 1600       | 2850       |
  | 30         | WARD       | 1250       | 2850       |
  | 30         | MARTIN     | 1250       | 2850       |
  | 30         | BLAKE      | 2850       | 2850       |   -- This row is the sixth row of the current window. 
  +------------+------------+------------+------------+

• If order by is specified, the rows from the first row to the current row belong to the current window. The value of the sixth row in the current window is returned.

  select deptno, ename, sal, nth_value(sal,6) over (partition by deptno order by sal) as nth_value from emp;

  The following result is returned:

  +------------+------------+------------+------------+
  | deptno     | ename      | sal        | nth_value  |
  +------------+------------+------------+------------+
  | 10         | TEBAGE     | 1300       | NULL       |   
  | 10         | MILLER     | 1300       | NULL       |   -- This window has only two rows, and NULL is returned. 
  | 10         | CLARK      | 2450       | NULL       |
  | 10         | WELAN      | 2450       | NULL       |
  | 10         | KING       | 5000       | 5000       |  
  | 10         | JACCKA     | 5000       | 5000       |
  | 20         | SMITH      | 800        | NULL       |
  | 20         | ADAMS      | 1100       | NULL       |
  | 20         | JONES      | 2975       | NULL       |
  | 20         | SCOTT      | 3000       | NULL       |
  | 20         | FORD       | 3000       | NULL       |
  | 30         | JAMES      | 950        | NULL       |
  | 30         | MARTIN     | 1250       | NULL       |
  | 30         | WARD       | 1250       | NULL       |
  | 30         | TURNER     | 1500       | NULL       |
  | 30         | ALLEN      | 1600       | NULL       |
  | 30         | BLAKE      | 2850       | 2850       |
  +------------+------------+------------+------------+

Related functions

NTH_VALUE 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.