MaxCompute支持使用QUALIFY语法根据您指定的过滤条件过滤窗(Window)函数的结果,QUALIFY语法过滤Window函数数据类似于HAVING语法处理经过聚合函数和GROUP BY后的数据。本文为您介绍QUALIFY语法和使用示例。
功能简介
在一个查询语句中QUALIFY语法的执行顺序在WINDOW函数之后,用于对窗口函数处理后的数据进行筛选。通常,SELECT语句的子句按如下所示的顺序执行:
FROM
WHERE
GROUP BY
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT
注意事项
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 | +------+------+------+
文档内容是否对您有帮助?