您可以使用WINDOW关键字自定义窗口,为窗口函数自定义名称。
命令格式
WINDOW <window_name> AS (<window_definition>)
[, <window_name> AS (<window_definition>)]
...
- window_name:必填。自定义某个窗口函数时,设置的自定义名称(named window)。
- window_definition:必填。被自定义名称的窗口函数,窗口函数的介绍详情请参见窗口函数。
使用限制
- 一个named window也可以用来定义另一个named window,经过这种定义,两个named window的意义相同。未定义named window不允许用来定义另一个named window。
- 在同一个作用域中,不允许一个named window被多次定义。
示例数据
--创建表:
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
--查询数据:
select * from emp;
--返回:
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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|
+------------+------------+------------+------------+------------+------------+------------+------------+
使用示例
- 示例1:定义一个named window。
--没有window关键字写法 SELECT deptno, ename, sal, row_number() OVER (partition by deptno order by sal desc) AS nums FROM emp; --返回: +------------+------------+------------+------------+ | 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 | +------------+------------+------------+------------+ --有window关键字写法 SELECT deptno, ename, sal, row_number() OVER w1 AS nums FROM emp WINDOW w1 AS (partition by deptno order by sal desc); --返回相同: +------------+------------+------------+------------+ | 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 | +------------+------------+------------+------------+
- 示例2:同时定义多个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 (partition by deptno);
- 示例3:使用window语句在全局定义named window。
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;
- 示例4:一个named window用来定义另一个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;
说明 经过这种定义,两个named window的意义相同。 - 示例5(错误示例,用于提示):未定义named window不允许用来定义另一个named window,以下为错误示例,其中W1和W2均未被定义。
SELECT deptno, ename, row_number() OVER w1 AS nums FROM emp WINDOW w1 AS w2;
- 示例6(错误示例,用于提示):在同一个作用域中,不允许一个named window被多次定义。以下为错误示例,其中W1被重复定义。
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);