关于大数据:深入MaxCompute-第十一弹-QUALIFY

64次阅读

共计 3889 个字符，预计需要花费 10 分钟才能阅读完成。

简介： 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 [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, 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 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;
```

正文完