Reuse Window Definitions with Named Windows in MaxCompute - MaxCompute

Use the WINDOW keyword to assign a name to a window definition and reuse it across multiple window functions in the same query. Without named windows, each window function requires its own inline OVER clause — repeating the same PARTITION BY and ORDER BY logic is verbose and error-prone. Named windows eliminate that repetition.

Syntax

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

Parameters

Parameter	Required	Description
`window_name`	Yes	The name assigned to the window definition. Use this name in `OVER` clauses to reference the window.
`window_definition`	Yes	The window specification, such as `PARTITION BY` and `ORDER BY` clauses. For the full syntax, see Window functions.

Limitations

A named window can be used to define another named window. The two windows are equivalent.
An undefined named window cannot be used to define another named window.
A named window cannot be defined more than once in the same scope.

Sample data

The examples in this topic use the emp table. Run the following commands 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,,10

To verify the data was loaded correctly:

SELECT * FROM emp;

Expected output:

+------------+------------+------------+------------+---------------------+------------+------------+------------+
| 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

The following two queries are equivalent. The second uses a named window to avoid repeating the window definition inline.

Without the WINDOW keyword:

SELECT deptno,
       ename,
       sal,
       row_number() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums
FROM emp;

With the WINDOW keyword:

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

Both queries return the same result:

+------------+------------+------------+------------+
| 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

Define multiple named windows in a single WINDOW clause and reference each in a different window function.

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: Define a named window globally

Define a named window before INSERT statements to reuse it across multiple queries:

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: Reference one named window from another

A named window can reference another named window defined in the same WINDOW clause. The two windows are equivalent:

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

When w2 references w1, both windows resolve to the same definition.

Example 5: Invalid — referencing an undefined named window

The following example is invalid. w1 references w2, but w2 is not defined anywhere in the WINDOW clause. Referencing an undefined named window causes an error.

-- Invalid: w2 is undefined
SELECT deptno, ename,
    row_number() OVER w1 AS nums
FROM emp
WINDOW w1 AS w2;

Example 6: Invalid — defining the same named window twice

The following example is invalid. w1 is defined twice in the same scope. Each named window must be unique within its scope.

-- Invalid: w1 is defined more than once in the same scope
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);