WINDOW keyword - MaxCompute - Alibaba Cloud Documentation Center

You can use the WINDOW keyword to specify a custom window and specify a name for a window function.

Syntax

WINDOW <window_name> AS (<window_definition>)
    [, <window_name> AS (<window_definition>)]
    ...

window_name: required. This parameter indicates the name that you specify for a custom window function when you define the function. The custom name is represented by a named window.
window_definition: required. This parameter indicates the window function for which a custom name is specified. For more information about window functions, see Window functions.

Limits

A named window can be used to define another named window. If you use a named window to define another named window, the two named windows have the same meaning. An undefined named window cannot be used to define a named window.
A named window cannot be defined multiple times in the same scope.

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 commands:

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

Query data:

SELECT * FROM emp;
-- The following result is returned:
+------------+------------+------------+------------+------------+------------+------------+------------+
| empno      | ename      | job        | mgr        | hiredate   | sal        | comm       | deptno     |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 7369       | SMITH      | CLERK      | 7902       | 1980-12-17 00:00:00 | 800        | NULL       | 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       | NULL       | 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       | NULL       | 30|
| 7782       | CLARK      | MANAGER    | 7839       | 1981-06-09 00:00:00 | 2450       | NULL       | 10|
| 7788       | SCOTT      | ANALYST    | 7566       | 1987-04-19 00:00:00 | 3000       | NULL       | 20|
| 7839       | KING       | PRESIDENT  | NULL       | 1981-11-17 00:00:00 | 5000       | NULL       | 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       | NULL       | 20|
| 7900       | JAMES      | CLERK      | 7698       | 1981-12-03 00:00:00 | 950        | NULL       | 30|
| 7902       | FORD       | ANALYST    | 7566       | 1981-12-03 00:00:00 | 3000       | NULL       | 20|
| 7934       | MILLER     | CLERK      | 7782       | 1982-01-23 00:00:00 | 1300       | NULL       | 10|
| 7948       | JACCKA     | CLERK      | 7782       | 1981-04-12 00:00:00 | 5000       | NULL       | 10|
| 7956       | WELAN      | CLERK      | 7649       | 1982-07-20 00:00:00 | 2450       | NULL       | 10|
| 7956       | TEBAGE     | CLERK      | 7748       | 1982-12-30 00:00:00 | 1300       | NULL       | 10|
+------------+------------+------------+------------+------------+------------+------------+------------+

Examples:

Example 1: Define a named window.

-- In the following sample code, no WINDOW keyword is used.
SELECT deptno,
       ename,
       sal,
       row_number() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums
FROM emp;
-- The following result is returned:
+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 2          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 4          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 6          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 2          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 5          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

-- In the following sample code, the WINDOW keyword is used.
SELECT deptno,
       ename,
       sal,
       row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC);
-- The returned result is the same as the returned result in the sample code in which no WINDOW keyword is used.
+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 2          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 4          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 6          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 2          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 5          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

Example 2: Define multiple named windows. Sample code:

SELECT deptno, ename,
    max(sal) OVER w2 AS max_sal,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC),
       w2 AS (PARTITION BY deptno);

Example 3: Use the WINDOW keyword to globally define a named window. Sample code:

WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC);
INSERT OVERWRITE TABLE table1 SELECT row_number() OVER w1 AS nums FROM table2;
INSERT OVERWRITE TABLE table3 SELECT max(column1) OVER w1 AS nums FROM table4;

Example 4: Use one named window to define another named window. Sample code:

SELECT deptno, ename,
    max(sal) OVER w2 AS max_sal,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC),
       w2 AS w1;

Note

After another named window is defined, the two named windows have the same meaning.

Example 5: An undefined named window cannot be used to define a named window. The following sample code provides an incorrect example. The W1 and W2 windows are not defined.
```
SELECT deptno, ename,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS w2;
```
Example 6: A named window cannot be defined multiple times in the same scope. The following sample code provides an incorrect example. The W1 window is defined multiple times.
```
SELECT deptno, ename,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC),
       w1 AS (PARTITION BY ename ORDER BY sal DESC);
```