SQL DENSE_RANK 函数

SQL Server 的 DENSE_RANK 函数是一个排名函数，它为分区中的每个记录分配连续的排名编号，而不跳过排名编号。如果函数在同一分区中遇到两个相等的值，它将为这两个值分配相同的排名编号。

SQL DENSE_RANK 函数在我们想要显示连续的排名而不跳过任何数字（无间隙）时非常有用。例如，按部门对员工进行薪资排名，按个人分数对学生进行排名，或按销售额对产品进行排名。

在本文中，我们将通过一些实际示例、常见错误及修复方法以及最佳实践来向您展示如何编写 SQL Server DENSE_RANK 函数。在实际示例之前，让我先介绍一下它的工作原理。

想象一个包含姓名、年龄、电子邮件和分数列的学生表。任务是根据分数对学生进行排名。如您所知，如果有多个学生获得相同的最高分数，他们都获得相同的排名，而下一个学生获得第二名（不跳过）。

SELECT Name, Age, Email, Marks,
	DENSE_RANK() OVER(ORDER BY Marks DESC) AS Ranks
FROM students

SQL DENSE_RANK 函数语法

DENSE_RANK() 函数的语法，它返回 BIGINT 作为结果集：

SELECT DENSE_RANK() OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]

PARTITION BY 子句：这是一个可选参数，它将结果集中的记录或行划分为分区。

  • 如果指定了 PARTITION BY 子句，SQL DENSE_RANK 函数将为每个分区分配排名编号。接下来，它会为下一组重置编号。
  • 如果未指定，它将考虑所有记录为一个分区。

ORDER BY：使用此子句指定如何对列数据进行排序（升序或降序）。DENSE_RANK() 排名因子基于顺序工作。

对于此 DENSE_RANK 查询演示，我们使用具有十条记录的 Customers 表数据。

SQL DENSE_RANK 函数示例

在此示例中，我们将展示如何使用 DENSE_RANK 和 PARTITION BY 子句来分区表中的记录并对其进行排名。以下排名函数查询将按职业对客户数据进行分区，并使用年收入分配密集排名编号。

SELECT [ID], [FirstName], [LastName], [Education],
[Occupation], [Sales], [YearlyIncome]      
,DENSE_RANK() OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
             ) AS DRWP
  FROM [Customer]

SQL Server 语句使用其职业将选定的数据划分为分区。从上一张图可以看出，我们有四个分区。

PARTITION BY [Occupation]

在下面的语句中，我们使用了带 PARTITION BY 子句的 SQL DENSE_RANK 函数。ORDER BY 子句将按收入降序对客户进行排序。因此，该函数根据客户的年收入为每个分区分配一个密集排名编号。当新分区开始时，DENSE_RANK 编号将重置为 1。

DENSE_RANK() OVER (
              PARTITION BY [Occupation] 
              ORDER BY [YearlyIncome] DESC
             ) AS DRWP

由于第 3 条和第 4 条记录的年收入相同，因此它们获得了相同的排名。接下来，它为下一条记录分配了第 2 名（它不会跳过任何数字）。

提示：如果将 ORDER BY [YearlyIncome] DESC 改为 ASC，DENSE_RANK() 函数将分配排名编号 1 作为最低收入，n = 最高收入。

在 DENSE_RANK() OVER 子句中，您还可以在 ORDER BY 中使用多个列，以根据这两列分配不同的排名。这在您有一列中的相同值时非常有用，您可以使用另一列进一步划分它们以赋予不同的排名。

DENSE_RANK() OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC,[Sales] DESC
             ) AS DRWP2

注意：我们可以使用 WHERE 子句与上述语句结合使用，以根据 DENSE_RANK 值过滤记录。例如，如果我们将 WHERE DRWithPart < 2 作为最后一行。它会显示排名为 1 的客户。

SQL DENSE_RANK 无 PARTITION BY 子句

在此示例中，我们将展示如果在 DENSE_RANK 函数中遗漏 PARTITION BY 子句会发生什么。为了演示相同，我们将上面提到的SELECT 语句在没有 PARTITION BY 子句的情况下使用。这意味着 DENSE_RANK 函数将整个表视为一个分区或组。

SELECT *,DENSE_RANK() 
    OVER (ORDER BY [YearlyIncome] DESC
           ) AS DRWithoutP
FROM [Customer]

在此示例中，下面的语句按降序对年收入数据进行排序。请参考最后一张图查看输出。

ORDER BY [YearlyIncome] DESC

在下面的语句中，我们使用了不带 PARTITION BY 子句的 SQL Server DENSE_RANK 函数。因此，它会将整个表视为一个部分，并从头到尾分配排名编号。

DENSE_RANK() OVER (
              ORDER BY [YearlyIncome] DESC
            ) AS DRWithoutP

由于第 2、3 和 4 条记录的年收入相同（80000），因此它们获得了相同的排名（2）。接下来，由于第 5 条和第 6 条记录的年收入相同，因此它们也获得了相同的编号，依此类推。

字符串列上的 DENSE_RANK

SQL Server DENSE_RANK 函数还允许您对字符串列进行排名。在此示例中，我们将使用此方法为 [First name] 分配排名编号。

SELECT *,
      ,DENSE_RANK() OVER (
                         PARTITION BY [Occupation] 
                         ORDER BY [FirstName] DESC
                         ) AS  DRStr
  FROM [Customer]

如果您观察下面的屏幕截图，此 DENSE_RANK 函数已为第 6 条和第 7 条记录分配了相同的排名，因为它们的名字是相同的。接下来，它为下一条记录分配了第 2 名。

SQL DENSE_RANK Function Example

DENSE_RANK 常见错误与最佳实践

在使用 SQL DENSE_RANK() 函数时，您必须注意一些事项以避免错误。以下最佳实践列表可帮助您完全避免这些错误或提供解决方案。

  1. 当您使用 DENSE_RANK() 函数时，必须放置 ORDER BY 子句。如果省略它，排名编号将不可预测，并且可能会产生错误的结果。
  2. ORDER BY 子句决定了排名因子。例如，产品销售表，ASC 表示排名从最低销量开始。如果是 DESC，则第一个排名属于最高销量。
  3. 如果您的目标是按组排名，则必须包含 PARTITION BY 子句。
  4. 在使用 PARTITION BY 时，请使用适当的列名进行分组。例如，Category、Country、Region 等。
  5. 在处理大型数据集时，可以使用 WHERE 子句过滤数据，或使用 CTE 获取表的所需部分。
  6. 尽可能在 ORDER BY 和 PARTITION 子句中使用索引列。
  7. 您可以将 SQL Server DENSE_RANK() 函数与其他排名函数结合使用，例如 RANK()、ROW_NUMBER 和 NTILE。
  8. 我们可以将 DENSE_RANK() 函数与聚合函数（如 SUM()、AVG()、MIN() 等）结合使用，以在分区内查找总计、平均值、最小值等。
  9. DENSE_RANK() 函数将所有 NULL 值视为相同，并将相同的排名编号分配给所有 NULL 值。

评论已关闭。