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
-- Create a table.
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
-- 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);