QUALIFY语法和使用示例_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

MaxCompute支持使用QUALIFY语法根据您指定的过滤条件过滤窗（Window）函数的结果，QUALIFY语法过滤Window函数数据类似于HAVING语法处理经过聚合函数和GROUP BY后的数据。本文为您介绍QUALIFY语法和使用示例。

功能简介

一个典型的查询语句的执行顺序如下：

FROM
WHERE
GROUP BY和Aggregation Function
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

通常在一个查询语句中QUALIFY语法的执行顺序在WINDOW函数之后，用于对窗函数处理后的数据进行筛选。

注意事项

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;
```

命令格式

QUALIFY <expression>

其中expression为过滤条件表达式。

使用示例

通过使用QUALIFY语法可以将原有的语法简化。

改造前SQL命令及结果：

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) t(a, b)
)
WHERE col2 > 1;

--返回结果
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+

改写后SQL命令及结果：

SELECT 
t.a as col1, 
sum(t.a) over (partition by t.b) as col2 
FROM values (1, 2),(2,3) t(a, b) 
QUALIFY col2 > 1;

--返回结果
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+

也可以不使用别名，直接对Window函数进行过滤，改写如下。

SELECT t.a as col1,
sum(t.a) over (partition by t.b) as col2
FROM values (1, 2),(2,3) t(a, b)
QUALIFY sum(t.a) over (partition by t.b)  > 1;

--返回结果
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+

QUALIFY和WHERE、HAVING的使用方法相同，只是执行顺序不同，所以QUALIFY语法允许您写一些复杂的条件，示例如下。
```
SELECT * 
FROM values (1, 2),(2,3) t(a, b) 
QUALIFY sum(t.a) over (partition by t.b)  IN (SELECT a FROM <table_name>);
```

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    |
+------+------+------+