MaxCompute:QUALIFY

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