和风网标志

生成数据智能

大数据运用

分解 DENSE_RANK()：SQL 爱好者的分步指南 – KDnuggets

由柏拉图重新发布
由柏拉图重新发布

日期：

浏览次数： 88

分解 DENSE_RANK()：SQL 爱好者的分步指南
图片由编辑
 

在当今数据驱动的世界中，SQL（结构化查询语言）是管理和操作数据库系统的基石。 SQL 的强大功能和灵活性的核心组成部分在于它 窗口函数，一类函数，用于跨与当前行相关的行集执行计算。

想象一下，您正在通过滑动窗口查看数据，并根据该窗口的位置和大小，对数据执行计算或转换。这本质上就是 SQL 窗口函数的作用。它们处理诸如计算运行总计、平均值或排名之类的任务，这些任务使用标准 SQL 命令执行起来具有挑战性。

窗口函数工具箱中最强大的工具之一是排名函数，特别是 DENSE_RANK() 功能。这个功能对于数据分析师来说是天赐之物，让我们能够对不同行的数据进行没有任何间隙的排名。无论您是要深入了解销售数据、网站流量数据，甚至是简单的学生考试成绩列表， DENSE_RANK() 是必不可少的。

在这篇文章中，我们将深入探讨其内部工作原理 DENSE_RANK()，将它与它的近亲兄弟姐妹并列 RANK() 和 ROW_NUMBER()，并展示如何避免可能在 SQL 之旅中绊倒您的常见陷阱。准备好提升您的数据分析技能了吗？让我们深入了解一下。

SQL 中的排名函数是窗口函数的子集，它为结果集中的每一行分配唯一的排名。这些排名值对应于特定顺序，由函数内的 ORDER BY 子句确定。排名函数是 SQL 的支柱，广泛用于各种任务的数据分析，例如查找顶级销售人员、确定表现最佳的网页或确定特定年份最卖座的电影。

SQL中有3个主要的排名函数，分别是 RANK(), ROW_NUMBER()及 DENSE_RANK()。每个函数的操作方式都略有不同，但它们都服务于根据指定条件对数据进行排名的共同目的。 RANK() 和 DENSE_RANK() 函数具有类似的行为，因为它们为具有相同值的行分配相同的排名。关键的区别在于他们如何处理后续排名。 RANK() 跳过下一个排名，而 DENSE_RANK() 才不是。

在另一方面，在 ROW_NUMBER() 函数为每一行分配一个唯一的行号，无论列值的顺序是否相同。尽管 RANK(), DENSE_RANK()及 ROW_NUMBER() 乍一看似乎可以互换，但理解它们的细微差别对于使用 SQL 进行有效的数据分析至关重要。这些函数之间的选择可以显着影响您的结果以及从数据中得出的见解。

DENSE_RANK() 是 SQL 中的一个强大的排名函数，它在指定分区内分配唯一的排名值。症结所在， DENSE_RANK() 为您的数据提供无差距排名，这意味着每个唯一值都会被赋予不同的排名，相同的值将获得相同的排名。与它的对应物不同 RANK(), DENSE_RANK() 如果值之间存在平局，则不会跳过任何排名。

为了将其分解，让我们想象一个场景，其中您有一个学生分数数据集，并且三个学生获得了相同的分数，例如 85 分。使用 RANK()，所有三名学生都将获得排名 1，但下一个最好成绩将排名 4，跳过排名 2 和 3。但是， DENSE_RANK() 对此的处理方式有所不同。它将为所有三名学生分配 1 的排名，其次最好的成绩将获得 2 的排名，确保排名没有差距。

那么，什么时候应该使用 DENSE_RANK()？在需要连续排名且没有任何间隙的场景中，它特别方便。考虑一个用例，您需要奖励表现最好的三名员工。如果您的数据有联系，请使用 RANK() 可能会导致您错过授予值得的候选人的机会。就在那时 DENSE_RANK() 来救援，确保所有得分最高的球员都得到应有的认可，并且排名不会被跳过。

了解两者之间的差异 DENSE_RANK(), RANK()及 ROW_NUMBER() 对于 SQL 中的高效数据分析至关重要。所有三个功能都非常强大，但它们的细微差别可能会显着影响数据分析的结果。

我们先从 RANK()。此函数为数据集中的每个不同值分配唯一的排名，并将相同的排名分配给相同的值。然而，当 RANK() 遇到平局（相同的值），它会跳过序列中的下一个排名。例如，如果您有三种销售数字相同的产品， RANK() 将为这些产品中的每一个分配相同的排名，但随后将跳过下一个排名。这意味着，如果这三种产品是最畅销的产品，它们都将被分配排名 1，但下一个最畅销的产品将被分配排名 4，而不是排名 2。

接下来，我们考虑一下 DENSE_RANK()。 相近 RANK(), DENSE_RANK() 将相同的排名分配给相同的值，但不会跳过任何排名。使用前面的示例，与 DENSE_RANK()，三个最畅销的产品仍将被指定为排名 1，但下一个最畅销的产品将被指定为排名 2，而不是排名 4。

最后， ROW_NUMBER() 采取不同的方法。它为每一行分配一个唯一的排名，无论值是否相同。这意味着即使三种产品的销售额相同， ROW_NUMBER() 将为每行分配一个唯一的编号，非常适合需要为每一行分配不同标识符的情况。

的语法 DENSE_RANK() 很简单。它与 OVER() 子句，在分配排名之前对数据进行分区。语法如下： DENSE_RANK() OVER (ORDER BY column)。 这里， column 指的是您想要对数据进行排名的列。让我们考虑一个例子，其中我们有一个名为 Sales 带列 SalesPerson 和 SalesFigures。为了根据销售数据对销售人员进行排名，我们将使用 DENSE_RANK() 功能如下： DENSE_RANK() OVER (ORDER BY SalesFigures DESC)。此 SQL 查询将根据销售人员的销售数据从最高到最低对销售人员进行排名。

运用 DENSE_RANK() 和这个结合 PARTITION BY 可以特别有洞察力。例如，如果您想对每个区域内的销售人员进行排名，您可以按以下方式对数据进行分区： Region 然后在每个分区内排名。其语法是 DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesFigures DESC)。这样，您不仅可以获得全面的排名，还可以对每个地区的表现有细致入微的了解。

Apple SQL 问题：查找每个销售日期的最佳销售业绩

表：sales_data

+------------+-----------+------------+
|employee_id | sales_date| total_sales|
+------------+-----------+------------+
|101         |2024-01-01 |500         |
|102         |2024-01-01 |700         |
|103         |2024-01-01 |600         |
|101         |2024-01-02 |800         |
|102         |2024-01-02 |750         |
|103         |2024-01-02 |900         |
|101         |2024-01-03 |600         |
|102         |2024-01-03 |850         |
|103         |2024-01-03 |700         |
+------------+-----------+------------+

 

输出

+------------+-----------+------------+
|employee_id | sales_date| total_sales|
+------------+-----------+------------+
|101         |2024-01-01 |800         |
|103         |2024-01-02 |900         |
|102         |2024-01-03 |850         |
+------------+-----------+------------+

Apple 顶级销售业绩解决方案

第 1 步：了解数据

首先，我们来了解一下 sales_data 表中的数据。它具有三列：employee_id、sales_date 和total_sales。此表表示销售数据，其中包含有关员工、销售日期和总销售额的信息。

步骤 2：分析 DENSE_RANK() 函数

该查询使用 DENSE_RANK() 窗口函数根据每个销售日期分区内的总销售额对员工进行排名。 DENSE_RANK() 用于为 sales_date 分区内的每一行分配排名，排序基于total_sales 降序排列。

第 3 步：分解查询结构

现在，让我们分解查询的结构：

SELECT 
  employee_id, 
  sales_date, 
  total_sales 
FROM 
  (
    SELECT 
      employee_id, 
      sales_date, 
      total_sales, 
      DENSE_RANK() OVER (
        PARTITION BY sales_date 
        ORDER BY 
          total_sales DESC
      ) AS sales_rank 
    FROM 
      sales_data
  ) ranked_sales 
WHERE 
  sales_rank = 1;

 

  • SELECT 子句：指定最终结果中将包含的列。在本例中，它是employee_id、sales_date 和total_sales。
  • FROM 子句：这是实际数据的来源。它包括一个子查询（括在括号中），该子查询从 sales_data 表中选择列并使用 DENSE_RANK() 添加计算列。
  • DENSE_RANK() 函数：该函数在子查询中使用，根据total_sales 列为每行分配排名，并按sales_date 进行分区。这意味着排名是针对每个销售日期单独进行的。
  • WHERE 子句：此子句会过滤结果，仅包含 sales_rank 等于 1 的行。这可确保最终结果中仅包含每个销售日期的最佳销售业绩者。

第 4 步：执行查询

当您执行此查询时，它将生成一个结果集，其中包括每个销售日期的最佳销售人员的employee_id、sales_date 和total_sales。

第 5 步：检查输出

最终输出表名为 top_performers，将包含所需信息：基于 DENSE_RANK() 计算的每个销售日期的最佳销售业绩者

Google SQL 问题：为每个产品查找提供最高评论分数的客户

表：产品评论

+------------+-----------+-------------+-------------------------------+
|customer_id | product_id| review_date | review_score | helpful_votes  |
+------------+-----------+-------------+--------------+----------------+
|301         |101        |2024-04-01   |4.5           | 12             |
|302         |102        |2024-04-01   |3.8           | 8              |
|303         |103        |2024-04-01   |4.2           | 10             |
|301         |101        |2024-04-02   |4.8           | 15             |
|302         |102        |2024-04-02   |3.5           | 7              |
|303         |103        |2024-04-02   |4.0           | 11             |
|301         |101        |2024-04-03   |4.2           | 13             |
|302         |102        |2024-04-03   |4.0           | 10             |
|303         |103        |2024-04-03   |4.5           | 14             |
+------------+-----------+-------------+--------------+----------------+

 

输出

+------------+-----------+-------------+--------------+----------------+
|customer_id | product_id| review_date | review_score | helpful_votes  |
+------------+-----------+-------------+--------------+----------------+
|301         |101        |2024-04-01   |4.5           | 12             |
|301         |101        |2024-04-02   |4.8           | 15             |
|303         |103        |2024-04-03   |4.5           | 14             |
+------------+-----------+-------------+--------------+----------------+

Google 最高评论分数解决方案

第 1 步：了解数据

Product_reviews 表包含有关各种产品的客户评论的信息。它包括 customer_id、product_id、review_date、review_score 和 Helpful_votes 等列。此表表示与客户评论相关的数据，其中包含有关客户、正在评论的产品、评论日期、评论分数以及收到的有用投票数的详细信息。

步骤 2：分析 DENSE_RANK() 函数

在此查询中，DENSE_RANK() 窗口函数用于对由product_id 和review_date 定义的每个分区内的行进行排名。排名根据两个标准确定：review_score（按降序排列）和helpful_votes（按降序排列）。这意味着具有较高评论分数和较多有用投票的行将被分配较低的排名。

第 3 步：分解查询结构

现在，让我们分解查询的结构：

SELECT 
  customer_id, 
  product_id, 
  review_date, 
  review_score, 
  helpful_votes 
FROM 
  (
    SELECT 
      customer_id, 
      product_id, 
      review_date, 
      review_score, 
      helpful_votes, 
      DENSE_RANK() OVER (
        PARTITION BY product_id, 
        review_date 
        ORDER BY 
          review_score DESC, 
          helpful_votes DESC
      ) AS rank_within_product 
    FROM 
      product_reviews
  ) ranked_reviews 
WHERE 
  rank_within_product = 1;

 

  • SELECT 子句：指定将包含在最终结果中的列。它包括 customer_id、product_id、review_date、review_score 和 Helpful_votes。
  • FROM 子句：这部分包括一个子查询（括在括号中），该子查询从 Product_reviews 表中选择列，并使用 DENSE_RANK() 添加计算列。计算是在由product_id和review_date定义的分区上执行的，并且排名基于review_score和helpful_votes按降序排列。
  • DENSE_RANK() 函数：此函数在子查询中应用，根据指定的条件为每行分配排名。排名是针对product_id 和review_date 的每个组合单独完成的。
  • WHERE 子句：过滤结果以仅包含rank_within_product 等于1 的行。这可确保最终结果中仅包含每个产品在每个审核日期排名靠前的行。

第 4 步：执行查询

执行此查询将生成一个结果集，其中包含所需信息：customer_id、product_id、review_date、review_score 以及基于每个产品和评论日期组合中的评论分数和有用投票的排名靠前的评论。

第 5 步：检查输出

最终的输出表名为 top_reviewers，将显示每个评论日期每个产品的排名最高的评论，同时考虑评论分数和有用投票数。

而 DENSE_RANK() 是 SQL 中非常有用的函数，对于分析人员，尤其是刚接触 SQL 的分析人员来说，在使用它时犯错误的情况并不少见。让我们仔细看看其中一些常见错误以及如何避免它们。

一个常见的错误是误解如何 DENSE_RANK() 处理空值。与某些 SQL 函数不同， DENSE_RANK() 将所有 NULL 视为相同。这意味着如果您对某些值为 NULL 的数据进行排名， DENSE_RANK() 将为所有 NULL 值分配相同的排名。在处理包含 NULL 值的数据集时请注意这一点，并考虑将 NULL 替换为代表其在上下文中含义的值，或者根据您的具体要求排除它们。

另一个常见错误是在使用时忽视分区的重要性 DENSE_RANK()。 “PARTITION BY”子句允许您将数据划分为不同的段并在这些分区内执行排名。忽略使用“PARTITION BY”可能会导致错误的结果，特别是当您希望为不同的类别或组重新启动排名时。

与此相关的是不当使用 ORDER BY 条款与 DENSE_RANK(). DENSE_RANK() 默认按升序分配排名，这意味着最小值的排名为 1。如果您需要按降序排名，则必须在您的代码中包含“DESC”关键字 ORDER BY 条款。如果不这样做，将会产生可能与您的期望不符的排名。

最后，一些分析师错误地使用 DENSE_RANK() 哪里 ROW_NUMBER() or RANK() 可能更合适，反之亦然。正如我们所讨论的，所有这三个函数都有独特的行为。了解这些细微差别并为您的特定用例选择正确的函数对于进行准确有效的数据分析至关重要。

掌握 DENSE_RANK() 如何增强 SQL 中的高效数据分析

掌握使用 DENSE_RANK() 可以显着提高SQL数据分析的效率，特别是涉及排名和比较的数据分析。此函数提供了一种细致入微的排名方法，通过将相同的排名分配给相同的值而不跳过任何排名数字来保持排名规模的连续性。

这对于分析大型数据集特别有用，其中数据点通常可以共享相同的值。例如，在销售数据集中，多个销售人员可能实现了相同的销售数字。 DENSE_RANK() 实现公平排名，其中每个销售人员都被分配相同的排名。此外，使用 DENSE_RANK() 与“PARTITION BY”结合使用可以进行集中的、特定于类别的分析。

在处理空值时，该函数的应用变得更加有效。与其将这些排除在排名过程之外， DENSE_RANK() 将所有空值视为相同，并为它们分配相同的排名。这确保了即使可能丢失确切值，数据点也不会被忽略，从而提供更全面的分析。

为了增强您的 SQL 技能，我们建议您在 BigTechInterviews、Leetcode 或类似网站等平台上进行在线练习。

DENSE_RANK() 在 SQL 中做什么？

DENSE_RANK() 是一个 SQL 窗口函数，它根据指定列为数据行分配排名。它通过给予它们相同的排名来处理平局，而不在排名顺序中留下任何间隙。

SQL 中的 RANK()、ROW_NUMBER() 和 DENSE_RANK() 有什么区别？

RANK() 和 ROW_NUMBER() 为数据分配排名，但它们处理关系的方式不同。 RANK() 在关联数据的排名中留下间隙，而 ROW_NUMBER() 为每行分配一个唯一的数字，而不考虑关联。另一方面，DENSE_RANK() 为绑定数据点分配相同的排名，没有任何间隙。

如何在 SQL 的 WHERE 子句中使用 DENSE_RANK()？

DENSE_RANK() 是一个窗口函数，不能直接在 WHERE 子句中使用。相反，它可以与 ROW_NUMBER() 或 RANK() 等其他函数结合使用，然后可以在 WHERE 子句中使用它们来根据排名过滤数据。

可以在没有 PARTITION BY 的情况下使用 DENSE_RANK() 吗？

不，指定 PARTITION BY 对于 DENSE_RANK() 的正常运行至关重要。如果没有它，所有数据都将被视为一组，从而导致排名不准确且毫无意义。掌握 SQL 中 DENSE_RANK() 的使用可以显着提高您的数据分析技能。

RANK() 和 DENSE_RANK() 有什么区别？

RANK() 和 DENSE_RANK() 之间的主要区别在于它们处理关系的方式。虽然 RANK() 在绑定数据的排名中留下间隙，但 DENSE_RANK() 为绑定数据点分配相同的排名，没有任何间隙。此外，RANK() 始终为每个新行将排名编号增加 1，而 DENSE_RANK() 则保持连续排名。
 
 

约翰·休斯 曾是 Uber 的数据分析师，后来成为 SQL 学习平台 BigTechInterviews (BTI) 的创始人。他热衷于学习新的编程语言并帮助候选人获得通过技术面试的信心和技能。他以科罗拉多州丹佛为家。

有关此主题的更多信息

现货图片

最新情报

现货图片

版权所有@ 2024 Plato Technologies Inc.