在本文中,我们将介绍 SQL 语言中名为 QUALIFY 的特定子句。读完本文,您将了解:
- QUALIFY 的基本概念和语法
- 何时使用 QUALIFY 子句
- 它与其他过滤方法有何不同。
SQL QUALIFY 子句的基本概念和语法
QUALIFY 是用于过滤窗口函数结果的子句。因此,要成功使用 QUALIFY 子句,SELECT 列表或 QUALIFY 子句中必须至少有一个 WINDOW 函数 - 仅返回布尔表达式计算结果为 TRUE 的行。
提示:查看SQL 窗口函数备忘单。
如果没有 QUALIFY 子句,则对 WINDOW 函数结果的过滤将需要嵌套。SQL 开发人员通常建议尽可能不要使用嵌套查询,因为它们会降低代码的可读性,同时增加调试和协作的复杂性。
换句话说,QUALIFY 子句背后的主要思想是简化需要对窗口函数结果进行过滤的查询;这意味着 QUALIFY 在计算窗口函数后进行评估。
以下是带有 QUALIFY 语句子句的查询的典型执行顺序:
- FROM
- WHERE
- GROUP BY and Aggregation
- HAVING
- WINDOW
- QUALIFY
- DISTINCT
- ORDER BY
- LIMIT
QUALIFY 语句的一般语法如下:
QUALIFY <predicate>
就其一般形式而言,它看起来像这样:
SELECT <column_list>
FROM <data_source>
[GROUP BY ...]
[HAVING ...]
QUALIFY <predicate>
[ ... ]
注意:<predicate>是一个表达式,用于过滤聚合和窗口函数计算后的结果。
何时使用 SQL QUALIFY
QUALIFY 语句子句与 HAVING 子句非常相似,因为它避免了使用子查询来执行过滤的需要。例如,您可以使用 QUALIFY 来过滤分析函数的结果 - 该函数用于根据一组行计算聚合值。
为了更清楚地说明何时使用 QUALIFY,这里有一个简短的场景:
假设您是一名数据科学家,利益相关者向您询问每个客户的上次登录信息。您的初始查询可能如下所示:
SELECT
user_id,
ip,
country_code,
os,
RANK() over (
PARTITION BY user_id ORDER BY log_datetime DESC
) as previous_logins
FROM login_logs
WHERE TRUE
这是一个好的开始,但它不能满足涉众的请求,因为它将返回所有登录信息,而不仅仅是最近的登录信息。要返回最后一次登录,我们需要添加一个过滤器。
SQL 新手可能会陷入尝试使用 WHERE 语句子句过滤此内容的陷阱,如下所示:
SELECT
user_id,
ip,
country_code,
os,
RANK() over (
PARTITION BY user_id ORDER BY log_datetime DESC
) AS previous_logins
FROM login_logs
WHERE TRUE
AND last_login = 1
不幸的是,这段代码不会执行;如果您尝试 GROUP BY 和 HAVING,情况也是如此。
发生这种情况的原因完全取决于我们在上一节中讨论的操作顺序。
WHERE、GROUP BY 和 HAVING 均在 WINDOW 函数之前求值;这意味着它们无法过滤 WINDOW 函数,因为它们不知道它们存在 - 在运行这三个子句时尚未评估 WINDOW 函数。
使过滤器出现在 WINDOW 函数之后的一种方法是使用公共表表达式 (CTE)。
我们的代码如下所示:
WITH
logins AS (
SELECT
user_id,
ip,
country_code,
os,
RANK() OVER (
PARTITION BY user_id ORDER BY log_datetime DESC
) AS previous_logins
FROM login_logs
WHERE TRUE
)
SELECT user_id, ip, country_code, os
FROM logins
WHERE previous_logins = 1
从技术上讲,这段代码是有效的——它将完美执行并返回利益相关者想要的结果。
但是,我们必须引入两个查询,并且有一些额外的代码行,如果您定期重复此过程,这些代码行可能会变得多余。
解决此问题的最佳方法是使用 QUALIFY。
代码如下所示:
-- Starter code from @Jiho Choi on StackOverflow
SELECT
user_id,
ip,
country_code,
os,
RANK() over (
PARTITION BY user_id ORDER BY log_datetime DESC
) as previous_logins
FROM login_logs
WHERE TRUE
QUALIFY previous_logins = 1
这个有效的解决方案之所以有效,是因为 QUALIFY 子句是在 WINDOW 函数之后按照 SQL 操作顺序进行评估的,这意味着它们知道它们的存在,因此可以在同一查询中过滤它们。
您可能想知道 QUALIFY 除了编写更少的代码行之外还有什么好处,答案是实际上没有任何性能好处。
我们使用的 CTE 解决方案和 QUALIFY 解决方案都在相似的时间范围内执行,因此我们不能说QUALIFY 解决方案有很大的性能增强。
主要好处是生活质量的改善;代码更少,更容易阅读。
SQL QUALIFY 与其他过滤方法的比较
到现在为止,您已经了解了 QUALIFY 语句子句是 SQL 中另一种可用的过滤方法。您还知道使用 QUALIFY 子句的最佳时机。
但是 QUALIFY 和其他过滤方法有什么区别呢?
让我们回顾一下每种过滤方法。
WHERE 子句
WHERE用于过滤表中的记录;这意味着它实现了行操作。当我们想要提取满足特定条件的记录时,我们会使用它。例如,我们可能想按某人的年龄过滤数据集。它在代码中的样子如下:
SELECT name, gender, height, weight, age
FROM gym_members_info
WHERE age < 23;
以下是关于 WHERE 子句的主要注意事项。
- 它用于根据特定条件过滤记录。
- 操作是在行上执行的。
- 您可以使用不带 GROUP BY 子句的 WHERE 子句
- WHERE 可以与 SELECT、UPDATE、DELETE 语句一起使用。
让我们将其与 HAVING 子句进行比较。
HAVING 子句
HAVING 用于根据特定条件从 GROUP BY 子句定义的组中过滤记录。因此,WHERE 和 HAVING 之间最显着的区别是 HAVING 需要存在 GROUP BY 子句才能成功求值。
这样做的主要好处是您可以将 HAVING 子句应用于聚合组的子集,这在 WHERE 块中是不可能的。
例如:
SELECT gender, AVG(height)
FROM gym_members_info
GROUP BY gender
HAVING AVG(height) > 170
上面的代码将返回平均身高大于170厘米的性别组;换句话说,HAVING 实现了列操作。
另一件需要注意的事情是,与 WHERE 不同,HAVING 只能与 SELECT 语句一起使用。
QUALIFY 子句
QUALIFY 与 HAVING 等效,只不过它对 WINDOW 函数的结果执行过滤器,而 HAVING 对聚合函数和 GROUP BY 子句执行过滤器 – 了解有关如何使用 GROUP BY 的更多信息。
如果我们想了解每个年龄段中最高的人的信息,我们可以使用 WINDOW 函数来执行计算,并使用 QUALIFY 子句来过滤结果,这样我们就只发现每个年龄段中最高的人。
代码如下:
SELECT name, gender, height, weight, age
RANK() over (
PARTITION BY age ORDER BY height DESC
) as ranked_ages
FROM gym_members_info
WHERE TRUE
QUALIFY ranked_ages = 1
本质上,QUALIFY 子句指定一个条件表达式,用于过滤已根据用户指定的条件计算的有序分析函数的输出。
结论与进一步研究
SQL 在典型的数据科学工作流程中发挥着重要作用;了解如何在 SQL 中实现有用的操作(例如 QUALIFY 子句)非常有帮助,因为它使您能够编写更清晰的代码,从而使其他人更愉快地与您协作。