Mastering QUALIFY: Simplify Window Function Filtering in MaxCompute

Overview

MaxCompute, Alibaba Cloud's distributed big‑data processing platform, offers a new SQL engine that improves compiler usability and language expressiveness. The QUALIFY clause allows users to filter the results of window functions, similar to how HAVING filters aggregated results.

Syntax

QUALIFY [expression]

The clause is placed after the WINDOW clause in the query execution order.

Execution Order

FROM

WHERE

GROUP BY and aggregation functions

HAVING

WINDOW

QUALIFY

DISTINCT

ORDER BY

LIMIT

QUALIFY runs after window functions have produced their results, enabling direct filtering of those results.

Usage Scenarios

When a query needs to filter window function output, developers previously used sub‑queries with WHERE clauses. QUALIFY simplifies this by allowing direct filtering without extra nesting.

Examples

Simple filtering using a sub‑query:

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;

Equivalent query using QUALIFY:

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;

Filtering without aliases:

SELECT t.a, SUM(t.a) OVER (PARTITION BY t.b) 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;

Complex condition example:

SELECT * FROM values (1,2) t(a,b) QUALIFY SUM(t.a) OVER (PARTITION BY t.b) IN (SELECT a FROM t1);

Full query demonstrating execution order:

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;

Result illustration: