文档

QUALIFY

更新时间:

MaxCompute支持使用QUALIFY语法根据您指定的过滤条件过滤窗(Window)函数的结果,QUALIFY语法过滤Window函数数据类似于HAVING语法处理经过聚合函数和GROUP BY后的数据。本文为您介绍QUALIFY语法和使用示例。

功能简介

一个典型的查询语句的执行顺序如下:

  1. FROM

  2. WHERE

  3. GROUP BY和Aggregation Function

  4. HAVING

  5. WINDOW

  6. QUALIFY

  7. DISTINCT

  8. ORDER BY

  9. LIMIT

通常在一个查询语句中QUALIFY语法的执行顺序在WINDOW函数之后,用于对窗函数处理后的数据进行筛选。

注意事项

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