Snowflake QUALIFY 入门：它是什么以及如何使用

作为一名使用 Snowflake 的数据从业者，您可能需要基于窗口函数来筛选查询结果。传统做法往往需要子查询或复杂的筛选逻辑。不过，Snowflake 提供了一个强大的替代方案：QUALIFY 子句。

在本指南中，我将通过分步骤示例和最佳实践，讲解如何使用 QUALIFY 来简化筛选。

Snowflake 中的 QUALIFY 子句是什么？

Snowflake 的 QUALIFY 子句用于配合窗口函数筛选结果。您既可以在 SELECT 列表中定义窗口函数，也可以在 QUALIFY 子句中直接引用它们。

通常，WHERE 会在聚合前筛选原始行，而 HAVING 在 GROUP BY 聚合之后筛选。

相较之下，QUALIFY 允许基于 ROW_NUMBER()、RANK()、DENSE_RANK() 等 SQL 语句的结果进行筛选。这意味着我们可以在窗口函数中生成输出并立刻对其进行过滤。

您可以将 QUALIFY 理解为：它对窗口函数的作用类似于 HAVING 之于聚合——在结果计算完成后进行筛选。

以下是 QUALIFY 子句特别有用的几种场景：

• 需要根据窗口函数的输出筛选查询结果。
• 希望通过减少对子查询的需求来简化 SQL 查询。
• 需要在分区内高效地排序、去重或细分数据。
• 要为报表或仪表板挑选每个类别下的最佳记录。

> 如果您是 Snowflake 新手，我们的Introduction to Snowflake 课程能为您提供上手练习，然后再深入学习诸如 QUALIFY 之类的子句。您也可以通过这篇面向初学者的教程了解 Snowflake 的更多功能。

Snowflake 中 QUALIFY 的语法

让我们回顾一下 QUALIFY 的基础语法，以便理解其构成。以下是一个使用 QUALIFY 的简单查询：

SELECT
 column1,
 column2, 
 window_function() OVER (PARTITION BY column3 ORDER BY column4) AS rank
FROM table_name
QUALIFY window_function_condition;技术支持

这是常规的 SELECT 子句和若干列。随后以常见格式编写 window_function()，得到名为 rank 的列。

接着是常见的 FROM 语句指明表名，最后是 QUALIFY。该 QUALIFY 后接“window_function_condition”，通常是等式判断，例如 QUALIFY rank = 1。

注意：您既可以在 QUALIFY 子句中引用窗口函数的别名，也可以重写整个窗口函数表达式。两种方式都可行。

> 如果您需要复习窗口函数，请查看这份窗口函数速查表。

在 Snowflake 中使用 QUALIFY 的示例

了解了 QUALIFY 的整体结构后，我们来看一些具体示例。

示例 1：按组筛选前 N 行

假设我们想找出每个部门薪资最高的 3 名员工。先看一个不使用 QUALIFY 的例子，然后再看使用后更简洁的版本。

/* First we have to rank all the employees */
WITH ranked_employees AS (
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees)

/* Then we query to subquery and filter using WHERE */
SELECT *
FROM ranked_employees
WHERE rank <= 3;技术支持

如您所见，上述示例需要一个 CTE，而使用 QUALIFY 后，查询就更直观了：

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank <= 3;技术支持

上述查询会在每个部门内为员工赋予名次，并返回各部门排名前 3 的员工。它通过 QUALIFY 直接过滤，避免了子查询或 CTE。这在构建仪表板或 API 时尤为有用，因为缩短查询长度并缩短响应时间很重要。

示例 2：筛除重复记录

如果一张表存在重复记录，而我们希望基于某个排序函数仅保留首次出现的记录，可以将 QUALIFY 与 ROW_NUMBER() 搭配使用：

SELECT 
order_id, 
customer_id, 
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM orders
QUALIFY row_num = 1;技术支持

通过按每位客户分区并按下单日期排序生成 row_num 列，从最早的订单开始为每条记录赋予序号。使用 QUALIFY row_num = 1 进行过滤，即可确保仅保留每位客户的最早一笔订单。

该技巧常用于数据清洗中的去重任务。

示例 3：基于聚合窗口函数进行筛选

我们再进阶一点。将 QUALIFY 与聚合窗口函数结合，可以实现更灵活、动态的筛选。

例如，如果我们想找出薪资高于所在部门平均水平的员工，可以将 QUALIFY 与窗口上的 AVG() 一起使用：

SELECT 
employee_id, 
department, 
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees
QUALIFY salary > avg_salary;技术支持

这类逻辑非常适合在同侪群体中识别异常值或顶尖表现者。

> 如果您希望提升 SQL 能力以应对这类更复杂的查询，请查看我们的Intermediate SQL 课程。

在 Snowflake 中使用 QUALIFY 的最佳实践

以下是关于 QUALIFY 子句的一些最佳实践与用法。

简化复杂查询

QUALIFY 的强大之处在于它能通过消除额外的子查询和 CTE 来简化查询。充分利用这一特性。当您编写窗口函数并需要基于其输出进行筛选时，尽量使用它，而无需创建子查询。

更少的 CTE 意味着更易调试、更清晰的逻辑以及更好的可维护性。

将 QUALIFY 与其他窗口函数组合

您可以组合多个窗口函数以实现更精细的筛选。例如，将 ROW_NUMBER() 与 RANK() 一起使用，在限制结果数量的同时解决并列问题：

SELECT 
employee_id, 
department, 
salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, employee_id) AS row_num
FROM employees
QUALIFY rank <= 3 AND row_num <= 3;技术支持

该查询返回各部门薪资排名前 3 的员工。但如果前 3 名存在超过三人的并列，则会仅按员工 ID 顺序显示前三位。

性能考量

虽然 QUALIFY 能在代码层面简化查询，但也需要注意一些性能方面的考量。

窗口函数可能计算成本较高，结构不佳的查询会消耗大量资源。请注意以下要点：

• 在大数据集上，窗口函数计算代价高，应谨慎选择在流程的哪个阶段使用。
• 确保合理的索引与分区以优化性能。
• 在测试阶段将 LIMIT 与 QUALIFY 搭配使用可进一步提升效率。
• 使用 Snowflake 的 Query Profile 工具来识别窗口查询中的性能瓶颈。

排查 QUALIFY 查询

鉴于窗口函数的复杂性，QUALIFY 语句很容易踩坑。以下是一些应对常见问题的提示。

1. 在 WHERE 子句中使用窗口函数

试图在 WHERE 子句中直接使用窗口函数会报错，因为 WHERE 的求值先于窗口函数处理。

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
WHERE rank = 1;技术支持

解决方案： 使用 QUALIFY 子句，它在窗口函数之后进行求值。

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;技术支持

2. 省略 QUALIFY 中的谓词

QUALIFY 子句需要一个谓词来筛选窗口函数的结果。省略谓词会导致错误。

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC);技术支持

解决方案： 确保 QUALIFY 子句包含谓词，例如使用 = 1 来筛选排名第一的行。

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;技术支持

3. 含糊不清的别名

为列与窗口函数使用相同的别名会引发混淆并导致意外结果。

-- Potentially ambiguous
SELECT salary AS rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;技术支持

解决方案： 使用不同的别名以避免歧义。

SELECT salary AS salary_amount,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 1;技术支持

4. 分区或排序不当

在窗口函数中错误指定 PARTITION BY 或 ORDER BY 会导致意外结果。

解决方案： 仔细定义分区与排序以匹配所需逻辑。例如，要获取各部门的最高薪资：

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;技术支持

结论

Snowflake 中的 QUALIFY 子句是一种强大的工具，可基于窗口函数筛选结果，减少对子查询的需求，让查询更易读。通过掌握其有效用法，数据科学家可以在处理排序、分组或聚合数据时，简化查询并提升效率。

准备进一步提升您的 Snowflake 和 SQL 技能了吗？先从我们的Introduction to Snowflake 课程开始，在PostgreSQL 窗口函数课程中打磨分析型查询，或者通过Associate Data Engineer in SQL 学习路径推进您的职业发展！