All Products
Search
Document Center

# MaxCompute:QUALIFY

Last Updated:Sep 28, 2023

MaxCompute allows you to use the QUALIFY clause to filter the result returned by window functions based on specified filters. You can use the QUALIFY clause to filter results returned by window functions in the same way as you use the HAVING clause to filter results returned by aggregate functions and GROUP BY clauses. This topic describes the syntax of the QUALIFY clause and provides examples on how to use the QUALIFY clause.

## Overview

In most cases, the QUALIFY clause in a SELECT statement is executed after window functions. This can filter out the results that are returned by window functions. In a typical SELECT statement, clauses are executed in the following sequence:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. WINDOW

6. QUALIFY

7. DISTINCT

8. ORDER BY

9. LIMIT

## Precautions

• The QUALIFY clause in the SELECT statement must contain at least one window function. If no window function is specified in the QUALIFY clause, the following error is returned: `FAILED: ODPS-0130071:[3,1] Semantic analysis exception - use QUALIFY clause without window function`. Invalid sample code:

``````SELECT *
FROM values (1, 2) t(a, b)
QUALIFY a > 1;``````
• In a QUALIFY clause, you can use the alias of a column that is specified in the SELECT statement as a filter. Sample code:

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

## Syntax

``QUALIFY <expression>``

Replace `expression` with a filter expression.

## Examples

• Use the QUALIFY clause to simplify the original statement.

• SQL statement that does not use the QUALIFY clause and the returned result:

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

-- The following result is returned:
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+``````
• SQL statement that uses the QUALIFY clause and the returned result:

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

-- The following result is returned:
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+``````

In a QUALIFY clause, you can also use a window function instead of the column alias as a filter. Sample statement:

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

-- The following result is returned:
+------+------------+
| col1 | col2       |
+------+------------+
| 2    | 2          |
+------+------------+``````
• The QUALIFY, WHERE, and HAVING clauses are all used to filter data. They differ in the execution sequence in a SELECT statement. You can specify complex filters in the QUALIFY clause.

``````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>);``````
• The QUALIFY clause is executed after window functions. The following complex statement can help you know the execution sequence of the QUALIFY clause.

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

-- The following result is returned:
+------+------+------+
| a    | b    | _c2  |
+------+------+------+
| 2    | 3    | 6    |
| 2    | 4    | 7    |
+------+------+------+``````