深入 MaxCompute - 第十一弹 -QUALIFY

2023-08-31
浙江
本文字数：2734 字
阅读完需：约 9 分钟

简介： 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          ) a    WHERE a.nums<=3    ;

复制代码

通过 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;

复制代码

发布于: 刚刚阅读数: 3

原文链接:【http://xie.infoq.cn/article/e1fc071230ff65ad899dd2be9】。文章转载请联系作者。

阿里云大数据AI技术

关注

还未添加个人签名 2020-10-15 加入

分享阿里云计算平台的大数据和AI方向的技术创新和趋势、实战案例、经验总结。

发布

暂无评论

创作场景

深入 MaxCompute - 第十一弹 -QUALIFY

QUALIFY 功能简介

示例

注意事项

阿里云大数据AI技术

评论