QUALIFY：您从来不知道自己需要的 SQL 过滤语句

最新推荐文章于 2023-10-08 18:47:52 发布

初九爱编程

最新推荐文章于 2023-10-08 18:47:52 发布

阅读量548

点赞数 1

分类专栏：数据分析与数据科学文章标签： sql 数据库

本文链接：https://blog.csdn.net/m0_75176907/article/details/131866054

版权

数据分析与数据科学专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在本文中，我们将介绍 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 子句指定一个条件表达式，用于过滤已根据用户指定的条件计算的有序分析函数的输出。