类别：

查询语法

QUALIFY

在 SELECT 语句中， QUALIFY 子句筛选窗口函数的结果。

QUALIFY 对窗口函数的作用就像 HAVING 对聚合函数和 GROUP BY 子句的作用一样。

因此，在查询的执行顺序中， QUALIFY 在计算窗口函数之后进行计算。通常，SELECT 语句的子句按如下所示的顺序进行计算：

  1. From

  2. Where

  3. Group by

  4. Having

  5. Window

  6. QUALIFY

  7. Distinct

  8. Order by

  9. Limit

语法

QUALIFY <predicate>
Copy

包含 QUALIFY 的语句的一般形式类似于以下内容（允许有一些顺序变化，但未显示）：

SELECT <column_list>
  FROM <data_source>
  [GROUP BY ...]
  [HAVING ...]
  QUALIFY <predicate>
  [ ... ]
Copy
column_list

这通常遵循 SELECT 语句的投影子句的规则。

data_source

数据源通常是表，但也可以是其他类似表的数据源，如视图、UDTF （用户定义的表函数）等。

predicate

谓词是一种表达式，用于在计算聚合函数和窗口函数后筛选结果。谓词应类似于 HAVING 子句，但没有关键字 HAVING。此外，谓词还可以包含窗口函数。

有关谓词示例，请参阅 示例 部分（本主题内容）。

使用说明

  • QUALIFY 子句要求在 SELECT 语句的以下至少一个子句中指定至少一个窗口函数：

    • SELECT 列的列表。

    • QUALIFY 子句的筛选谓词。

    下面的“示例”一节中显示了其中每个子句的示例。

  • SELECT 列表中的表达式（包括窗口函数）可以通过 SELECT 列表中定义的列别名来引用。

  • QUALIFY 支持谓词中的聚合和子查询。对于聚合，适用与 HAVING 子句相同的规则。

  • QUALIFY 一词是一个保留的词。

  • QUALIFY 的 Snowflake 语法不是 ANSI 标准的一部分。

示例

QUALIFY 子句简化了需要对窗口函数结果进行筛选的查询。如果没有 QUALIFY，筛选需要嵌套。下面的示例使用 ROW_NUMBER() 函数仅返回每个分区中的第一行。

创建并加载表：

CREATE TABLE qt (i INTEGER, p CHAR(1), o INTEGER);
INSERT INTO qt (i, p, o) VALUES
    (1, 'A', 1),
    (2, 'A', 2),
    (3, 'B', 1),
    (4, 'B', 2);
Copy

此查询使用的是嵌套，而不是 QUALIFY：

SELECT * 
    FROM (
         SELECT i, p, o, 
                ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
            FROM qt
        )
    WHERE row_num = 1
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+
Copy

此查询使用 QUALIFY：

SELECT i, p, o
    FROM qt
    QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1
    ;
+---+---+---+
| I | P | O |
|---+---+---|
| 1 | A | 1 |
| 3 | B | 1 |
+---+---+---+
Copy

您还可以使用 QUALIFY 来引用 SELECT 列的列表中的窗口函数：

SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
    FROM qt
    QUALIFY row_num = 1
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+
Copy

您可以通过从上一个查询中移除 QUALIFY 并比较输出来了解 QUALIFY 如何充当筛选器：

SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
    FROM qt
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 2 | A | 2 |       2 |
| 3 | B | 1 |       1 |
| 4 | B | 2 |       2 |
+---+---+---+---------+
Copy

QUALIFY 子句还可以与聚合组合，并且可以在谓词中包含子查询。例如：

SELECT c2, SUM(c3) OVER (PARTITION BY c2) as r
  FROM t1
  WHERE c3 < 4
  GROUP BY c2, c3
  HAVING SUM(c1) > 3
  QUALIFY r IN (
    SELECT MIN(c1)
      FROM test
      GROUP BY c2
      HAVING MIN(c1) > 3);
Copy
语言: 中文