简介: MaxCompute反对QUALIFY语法过滤Window函数的后果,使得查问语句更简洁易了解。Window函数和QUALIFY语法之间的关系能够类比聚合函数+GROUP BY语法和HAVING语法。
MaxCompute(原ODPS)是阿里云自主研发的具备业界领先水平的分布式大数据处理平台, 尤其在团体外部失去广泛应用,撑持了多个 BU 的外围业务。MaxCompute 除了继续优化性能外,也致力于晋升 SQL 语言的用户体验和表达能力,进步宽广 MaxCompute 开发者的生产力。
MaxCompute 基于 MaxCompute2.0 新一代的 SQL 引擎,显著晋升了 SQL 语言编译过程的易用性与语言的表达能力。咱们在此推出深刻 MaxCompute 系列文章
第一弹 - 善用MaxCompute编译器的谬误和正告
第二弹 - 新的根本数据类型与内建函数
第三弹 - 简单类型
第四弹 - CTE,VALUES,SEMIJOIN
第五弹 - SELECT TRANSFORM
第六弹 - User Defined Type
第七弹 - Grouping Set, Cube and Rollup
第八弹 - 动静类型函数
第九弹 - 脚本模式与参数视图
第十弹 - IF ELSE分支语句
本文将介绍MaxCompute反对QUALIFY语法,QUALIFY语法反对指定过滤条件过滤窗口(Window)函数的后果,相似于HAVING语法解决通过聚合函数和GROUP BY后的数据。
QUALIFY性能简介
语法格局
QUALIFY [expression]
QUALIFY语法过滤Window函数的后果,Window函数和QUALIFY语法之间的关系能够类比聚合函数+GROUP BY语法和HAVING语法。
典型的查问语句的执行程序如下:
- FROM
- WHERE
- GROUP BY和Aggregation Function
- HAVING
- WINDOW
- QUALIFY
- DISTINCT
- ORDER BY
- LIMIT
通常在一个查问语句中QUALIFY语法的执行程序在WINDOW函数之后,用于对窗函数解决后的数据进行筛选。
应用场景
须要对Window函数的后果进行过滤,没有QUALIFY语法前,个别是在FROM语句中应用SubQuery,并通过WHERE条件来配合实现过滤。如下:
SELECT col1, col2FROM(SELECTt.a as col1,sum(t.a) over (partition by t.b) as col2FROM values (1, 2),(2,3),(2,2),(1,3),(4,2) t(a, b))WHERE col2 > 4;
改写后的查问语句:
SELECT t.a as col1, sum(t.a) over (partition by t.b) as col2 FROM values (1, 2),(2,3),(2,2),(1,3),(4,2) t(a, b) QUALIFY col2 > 4;
也能够不应用别名,间接对Window函数进行过滤。
SELECT t.a as col1,sum(t.a) over (partition by t.b) as col2FROM values (1, 2),(2,3),(2,2),(1,3),(4,2) t(a, b)QUALIFY sum(t.a) over (partition by t.b) > 4;
QUALIFY和WHERE/HAVING的应用办法雷同,只是执行程序不同,所以QUALIFY语法容许用户写一些简单的条件,比方:
SELECT *FROM values (1, 2) t(a, b)QUALIFY sum(t.a) over (partition by t.b) IN (SELECT a FROM t1)
QUALIFY执行于窗口函数失效后,上面一个较简单的例子能够直观的感触QUALIFY语法的执行程序:
SELECT a, b, max(c)FROM values (1, 2, 3),(1, 2, 4),(1, 3, 5),(2, 3, 6),(2, 4, 7),(3, 4, 8) t(a, b, c)WHERE a < 3GROUP BY a, bHAVING max(c) > 5QUALIFY sum(b) over (partition by a) > 3; --+------------+------------+------------+--| a | b | _c2 |--+------------+------------+------------+--| 2 | 3 | 6 |--| 2 | 4 | 7 |--+------------+------------+------------+
示例
row_number窗口函数示例,将所有职工依据部门(deptno)分组(作为开窗列),每个组内依据薪水(sal)做降序排序,取得职工在本人组内的序号,若须要查问每个部门薪水top 3的信息,则实现如下
数据筹备
create table if not exists emp (empno string, ename string, job string, mgr string, hiredate string, sal string, comm string, deptno string);
insert into table emp values('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');
在FROM语句中应用SubQuery,并通过WHERE条件来配合实现过滤,如下:
SELECT a.*FROM ( SELECT deptno ,ename ,sal ,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC ) AS nums FROM emp ) aWHERE a.nums<=3;
通过QUALIFY实现如下:
SELECT deptno ,ename ,sal ,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC ) AS numsFROM empQUALIFY nums <= 3;
后果均如下图,然而应用QUALIFY会使得查问语句更简洁易了解。
注意事项
QUALIFY语法须要查问语句外面至多一个Window函数,在没有Window函数的状况下应用QUALIFY语法会报错:
FAILED: ODPS-0130071:[3,1] Semantic analysis exception - use QUALIFY clause without window function
。谬误示例如下。SELECT * FROM values (1, 2) t(a, b) QUALIFY a > 1;
QUALIFY语法中容许用户应用SELECT中列的别名作为过滤条件的一部分,示例如下。
SELECT sum(t.a) over (partition by t.b) as c1 FROM values (1, 2) t(a, b) QUALIFY c1 > 1;