The WINDOW keyword lets you define a reusable named window for window functions.
Syntax
WINDOW <window_name> AS (<window_definition>)
[, <window_name> AS (<window_definition>)]
...window_name: Required. The custom name for the window.
window_definition: Required. The window's specification. For more information, see Window functions.
Restrictions
You can define a new named window from an existing one, making them equivalent. However, you cannot reference a named window that has not yet been defined.
You cannot define a named window more than once within the same scope.
Sample data
To help you understand how to use each function, this topic provides source data and function examples based on that data. The following example commands create a table named emp and add data to it:
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,,10Query the data as follows:
SELECT * FROM emp;
-- The command returns the following result:
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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.
-- Query without the WINDOW keyword SELECT deptno, ename, sal, row_number() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums FROM emp; -- The command returns the following 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 | +------------+------------+------------+------------+ -- Query 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); -- The 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 in a single statement.
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 clause in an INSERT statement.
INSERT OVERWRITE TABLE table1 SELECT row_number() OVER w1 AS nums FROM table2 WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC); INSERT OVERWRITE TABLE table3 SELECT max(column1) OVER w1 AS nums FROM table4 WINDOW w1 AS (PARTITION BY deptno ORDER BY sal DESC);Example 4: Define a named window based on another named window.
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;NoteThis definition makes the two named windows equivalent.
Example 5 (Error example): You cannot define a named window by referencing an undefined one. In the following incorrect example, w1 is defined based on w2, but w2 is not defined.
SELECT deptno, ename, row_number() OVER w1 AS nums FROM emp WINDOW w1 AS w2;Example 6 (Error example): You cannot define the same named window more than once in the same scope. In the following incorrect example, w1 is defined twice.
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);
该文章对您有帮助吗？