Use QUALIFY to filter window function results - MaxCompute

QUALIFY does for window functions what HAVING does for aggregate functions — it filters rows based on window function results without requiring a subquery.

Execution order

In a SELECT statement, QUALIFY runs after window functions are evaluated:

FROM
WHERE
GROUP BY
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

Syntax

QUALIFY <expression>

Replace <expression> with any filter expression that references a window function.

Usage notes

The QUALIFY clause must contain at least one window function. You can reference the window function directly in the QUALIFY predicate, or reference it via a column alias defined in the SELECT list.

Using QUALIFY without a window function returns the following error:

FAILED: ODPS-0130071:[3,1] Semantic analysis exception - use QUALIFY clause without window function

Invalid example:

SELECT *
FROM values (1, 2) t(a, b)
QUALIFY a > 1;

Examples

Simplify window function filtering

The following examples produce the same result using a SUM window function, showing the subquery and QUALIFY approaches side by side.

Without QUALIFY — subquery approach:

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;

With QUALIFY — using a column alias:

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;

-- Result:
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+

With QUALIFY — using the window function directly in the predicate:

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;

-- Result:
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+

Use a subquery filter in QUALIFY

QUALIFY supports complex filter expressions, including subqueries:

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

Combine WHERE, GROUP BY, HAVING, and QUALIFY

The following example shows how QUALIFY interacts with the other filtering clauses. Each clause filters at a different stage of execution:

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:
+------+------+------+
| a    | b    | _c2  |
+------+------+------+
| 2    | 3    | 6    |
| 2    | 4    | 7    |
+------+------+------+

WHERE a < 3 — excludes rows where a = 3 before aggregation
HAVING max(c) > 5 — excludes aggregated groups where the max is 5 or below
QUALIFY sum(b) OVER (PARTITION BY a) > 3 — filters the window function result after all aggregation is complete