成都商报官方网站,旅游网站建设技术有哪些方面,网站建设的系统流程图,wordpress建站用什么简介#xff1a; MaxCompute支持QUALIFY语法过滤Window函数的结果#xff0c;使得查询语句更简洁易理解。Window函数和QUALIFY语法之间的关系可以类比聚合函数GROUP BY语法和HAVING语法。
MaxCompute#xff08;原ODPS#xff09;是阿里云自主研发的具有业界领先水平的分…简介 MaxCompute支持QUALIFY语法过滤Window函数的结果使得查询语句更简洁易理解。Window函数和QUALIFY语法之间的关系可以类比聚合函数GROUP BY语法和HAVING语法。
MaxCompute原ODPS是阿里云自主研发的具有业界领先水平的分布式大数据处理平台, 尤其在集团内部得到广泛应用支撑了多个 BU 的核心业务。MaxCompute 除了持续优化性能外也致力于提升 SQL 语言的用户体验和表达能力提高广大 MaxCompute 开发者的生产力。
MaxCompute 基于 MaxCompute2.0 新一代的 SQL 引擎显著提升了 SQL 语言编译过程的易用性与语言的表达能力。我们在此推出深入 MaxCompute 系列文章
第一弹 - 善用MaxCompute编译器的错误和警告 第二弹 - 新的基本数据类型与内建函数 第三弹 - 复杂类型 第四弹 - CTEVALUESSEMIJOIN 第五弹 - 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语法。 典型的查询语句的执行顺序如下
FROMWHEREGROUP BY和Aggregation FunctionHAVINGWINDOWQUALIFYDISTINCTORDER BYLIMIT
通常在一个查询语句中QUALIFY语法的执行顺序在WINDOW函数之后用于对窗函数处理后的数据进行筛选。
使用场景
需要对Window函数的结果进行过滤没有QUALIFY语法前一般是在FROM语句中使用SubQuery并通过WHERE条件来配合实现过滤。如下
SELECT col1, col2
FROM
(
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)
)
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 col2
FROM 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 3
GROUP BY a, b
HAVING max(c) 5
QUALIFY 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 numsFROM emp) a
WHERE a.nums3
;通过QUALIFY实现如下 SELECT deptno,ename,sal,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC ) AS nums
FROM emp
QUALIFY 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;
