发布于 由admin

如何利用`ROW_NUMBER()`与`RANK()`函数实现数据排名？

数据排名：ROW_NUMBER() 与 RANK() 的深度解析

大家好！今天我们来深入探讨 SQL 中两个非常重要的排名函数：ROW_NUMBER() 和 RANK()。它们在数据分析、报表生成以及各种需要对数据进行排序和排名的场景中都扮演着关键角色。我们将通过具体的例子，详细讲解这两个函数的用法、区别以及适用场景。

1. ROW_NUMBER()：生成唯一序列号

ROW_NUMBER() 函数的作用非常直接：它为结果集中的每一行分配一个唯一的序列号，从 1 开始，依次递增。这个序列号的生成完全依赖于 ORDER BY 子句指定的排序规则。

语法:

ROW_NUMBER() OVER ( [PARTITION BY column1, column2, ...] ORDER BY columnA [ASC | DESC], columnB [ASC | DESC], ...)
  • PARTITION BY (可选): 将结果集划分为多个分区，ROW_NUMBER() 函数会在每个分区内独立生成序列号。
  • ORDER BY: 定义在每个分区内对行进行排序的规则。ROW_NUMBER() 函数根据这个排序规则来分配序列号。

示例：按销售额排名客户

假设我们有一个 Orders 表，包含以下字段：

  • CustomerID: 客户ID
  • OrderDate: 订单日期
  • OrderAmount: 订单金额

我们希望按照每个客户的总订单金额进行排名。

WITH CustomerOrderTotals AS (
    SELECT
        CustomerID,
        SUM(OrderAmount) AS TotalOrderAmount
    FROM
        Orders
    GROUP BY
        CustomerID
)
SELECT
    CustomerID,
    TotalOrderAmount,
    ROW_NUMBER() OVER (ORDER BY TotalOrderAmount DESC) AS CustomerRank
FROM
    CustomerOrderTotals;

解释:

  1. WITH CustomerOrderTotals AS (...): 我们使用 CTE (Common Table Expression) 来计算每个客户的总订单金额。
  2. SELECT CustomerID, SUM(OrderAmount) AS TotalOrderAmount FROM Orders GROUP BY CustomerID: 这个子查询计算每个客户的总订单金额，并将结果存储在名为 CustomerOrderTotals 的 CTE 中。
  3. ROW_NUMBER() OVER (ORDER BY TotalOrderAmount DESC) AS CustomerRank: 这是 ROW_NUMBER() 函数的核心部分。
    • OVER (ORDER BY TotalOrderAmount DESC): OVER() 子句指定了排名的窗口。在这里，我们按照 TotalOrderAmount 降序排序，这意味着订单金额最高的客户将获得排名 1。
    • AS CustomerRank: 我们将生成的序列号命名为 CustomerRank。

结果:

CustomerID TotalOrderAmount CustomerRank
1 1500 1
2 1200 2
3 1000 3
4 800 4
5 700 5

在这个例子中，ROW_NUMBER() 函数为每个客户分配了一个唯一的排名，即使他们的订单金额相同。

示例：按地区和销售额排名

现在，假设我们还想按照地区（Region）对客户进行排名。

WITH CustomerOrderTotals AS (
    SELECT
        CustomerID,
        Region,
        SUM(OrderAmount) AS TotalOrderAmount
    FROM
        Orders
    GROUP BY
        CustomerID,
        Region
)
SELECT
    CustomerID,
    Region,
    TotalOrderAmount,
    ROW_NUMBER() OVER (PARTITION BY Region ORDER BY TotalOrderAmount DESC) AS RegionalCustomerRank
FROM
    CustomerOrderTotals;

解释:

  1. PARTITION BY Region: PARTITION BY 子句将结果集按照 Region 划分为多个分区。
  2. ORDER BY TotalOrderAmount DESC: 在每个 Region 分区内，我们按照 TotalOrderAmount 降序排序。

结果（假设有两个地区：North 和 South）:

North Region

CustomerID Region TotalOrderAmount RegionalCustomerRank
1 North 800 1
2 North 700 2
3 North 600 3

South Region

CustomerID Region TotalOrderAmount RegionalCustomerRank
4 South 700 1
5 South 600 2
6 South 500 3

在这个例子中，ROW_NUMBER() 函数在每个 Region 分区内独立生成序列号。因此，每个地区都有自己的排名。

2. RANK()：处理并列排名

RANK() 函数与 ROW_NUMBER() 函数类似，也用于对结果集中的行进行排名。但 RANK() 函数的关键区别在于它会处理并列排名。如果有多行具有相同的排序值，它们将获得相同的排名，并且下一个排名会被跳过。

语法:

RANK() OVER ( [PARTITION BY column1, column2, ...] ORDER BY columnA [ASC | DESC], columnB [ASC | DESC], ...)

与 ROW_NUMBER() 函数的语法相同，PARTITION BY 和 ORDER BY 子句的含义也相同。

示例：按销售额排名客户，处理并列

我们继续使用之前的 Orders 表和 CustomerOrderTotals CTE。

WITH CustomerOrderTotals AS (
    SELECT
        CustomerID,
        SUM(OrderAmount) AS TotalOrderAmount
    FROM
        Orders
    GROUP BY
        CustomerID
)
SELECT
    CustomerID,
    TotalOrderAmount,
    RANK() OVER (ORDER BY TotalOrderAmount DESC) AS CustomerRank
FROM
    CustomerOrderTotals;

结果（假设有两个客户订单金额相同）:

CustomerID TotalOrderAmount CustomerRank
1 1500 1
2 1200 2
3 1000 3
4 1000 3
5 800 5
6 700 6

解释:

在这个例子中，CustomerID 3 和 CustomerID 4 的 TotalOrderAmount 都是 1000。RANK() 函数为它们分配了相同的排名 3，并将下一个排名跳过，CustomerID 5 的排名是 5。

重要区别：排名跳跃

RANK() 函数的排名跳跃行为是其与 ROW_NUMBER() 函数的主要区别。ROW_NUMBER() 函数始终生成连续的序列号，而 RANK() 函数会根据并列情况跳过排名。

示例：按地区和销售额排名，处理并列

WITH CustomerOrderTotals AS (
    SELECT
        CustomerID,
        Region,
        SUM(OrderAmount) AS TotalOrderAmount
    FROM
        Orders
    GROUP BY
        CustomerID,
        Region
)
SELECT
    CustomerID,
    Region,
    TotalOrderAmount,
    RANK() OVER (PARTITION BY Region ORDER BY TotalOrderAmount DESC) AS RegionalCustomerRank
FROM
    CustomerOrderTotals;

结果（假设有两个地区：North 和 South，且在 North 地区有两个客户订单金额相同）:

North Region

CustomerID Region TotalOrderAmount RegionalCustomerRank
1 North 800 1
2 North 700 2
3 North 700 2
4 North 600 4

South Region

CustomerID Region TotalOrderAmount RegionalCustomerRank
5 South 700 1
6 South 600 2
7 South 500 3

在这个例子中，RANK() 函数在 North 地区的排名中处理了并列情况。CustomerID 2 和 CustomerID 3 具有相同的 TotalOrderAmount，因此它们都获得了排名 2，并且下一个排名被跳过。

3. DENSE_RANK()：另一种处理并列的方式

除了 RANK() 函数，SQL 还提供了 DENSE_RANK() 函数来处理并列排名。DENSE_RANK() 函数与 RANK() 函数类似，也会为具有相同排序值的行分配相同的排名。但是，DENSE_RANK() 函数不会跳过排名。

语法:

DENSE_RANK() OVER ( [PARTITION BY column1, column2, ...] ORDER BY columnA [ASC | DESC], columnB [ASC | DESC], ...)

示例：按销售额排名客户，使用 DENSE_RANK()

WITH CustomerOrderTotals AS (
    SELECT
        CustomerID,
        SUM(OrderAmount) AS TotalOrderAmount
    FROM
        Orders
    GROUP BY
        CustomerID
)
SELECT
    CustomerID,
    TotalOrderAmount,
    DENSE_RANK() OVER (ORDER BY TotalOrderAmount DESC) AS CustomerRank
FROM
    CustomerOrderTotals;

结果（假设有两个客户订单金额相同）:

CustomerID TotalOrderAmount CustomerRank
1 1500 1
2 1200 2
3 1000 3
4 1000 3
5 800 4
6 700 5

解释:

在这个例子中，CustomerID 3 和 CustomerID 4 的 TotalOrderAmount 都是 1000。DENSE_RANK() 函数为它们分配了相同的排名 3，但没有跳过下一个排名，CustomerID 5 的排名是 4。

RANK() vs DENSE_RANK()：关键区别

  • RANK(): 处理并列排名，并跳过排名。
  • DENSE_RANK(): 处理并列排名，但不跳过排名。

选择哪个函数取决于你的具体需求。如果你需要知道每个值实际的排名位置（即使有并列），并且希望排名是连续的，那么应该使用 DENSE_RANK()。如果你需要知道每个值在排序后的位置，并且允许排名跳跃，那么应该使用 RANK()。

4. NTILE(): 分桶排名

NTILE() 函数可以将结果集划分为指定数量的桶（buckets），并为每个桶分配一个桶号。这在需要将数据分组到不同的类别中时非常有用。

语法:

NTILE(number_of_buckets) OVER ( [PARTITION BY column1, column2, ...] ORDER BY columnA [ASC | DESC], columnB [ASC | DESC], ...)
  • number_of_buckets: 指定要将结果集划分成的桶的数量。
  • PARTITION BY (可选): 将结果集划分为多个分区，NTILE() 函数会在每个分区内独立分配桶号。
  • ORDER BY: 定义在每个分区内对行进行排序的规则。NTILE() 函数根据这个排序规则来分配桶号。

示例：将客户按销售额划分为 4 个等级

WITH CustomerOrderTotals AS (
    SELECT
        CustomerID,
        SUM(OrderAmount) AS TotalOrderAmount
    FROM
        Orders
    GROUP BY
        CustomerID
)
SELECT
    CustomerID,
    TotalOrderAmount,
    NTILE(4) OVER (ORDER BY TotalOrderAmount DESC) AS CustomerTier
FROM
    CustomerOrderTotals;

结果（假设有 10 个客户）:

CustomerID TotalOrderAmount CustomerTier
1 1500 1
2 1200 1
3 1000 1
4 900 2
5 800 2
6 700 2
7 600 3
8 500 3
9 400 4
10 300 4

解释:

在这个例子中，NTILE(4) 将客户按照 TotalOrderAmount 降序排序后，划分为了 4 个等级（桶）。每个桶的客户数量尽可能相等。

示例：按地区将客户划分为 3 个等级

WITH CustomerOrderTotals AS (
    SELECT
        CustomerID,
        Region,
        SUM(OrderAmount) AS TotalOrderAmount
    FROM
        Orders
    GROUP BY
        CustomerID,
        Region
)
SELECT
    CustomerID,
    Region,
    TotalOrderAmount,
    NTILE(3) OVER (PARTITION BY Region ORDER BY TotalOrderAmount DESC) AS RegionalCustomerTier
FROM
    CustomerOrderTotals;

解释:

这个例子与之前的例子类似，但是我们使用了 PARTITION BY Region 子句。这意味着 NTILE(3) 函数将在每个 Region 分区内独立分配桶号。

5. 应用场景总结

函数 描述 适用场景
ROW_NUMBER() 为结果集中的每一行分配一个唯一的序列号。 需要为每一行分配唯一标识符，例如分页，或者需要确定每个值在排序后的准确位置。
RANK() 为结果集中的行进行排名，处理并列排名，并跳过排名。 需要知道每个值在排序后的位置，并且允许排名跳跃，例如比赛排名（如果有多个人并列第一，则下一个排名是第三名）。
DENSE_RANK() 为结果集中的行进行排名，处理并列排名，但不跳过排名。 需要知道每个值实际的排名位置（即使有并列），并且希望排名是连续的，例如奖牌榜（如果有多个人并列第一，则下一个排名是第二名）。
NTILE() 将结果集划分为指定数量的桶，并为每个桶分配一个桶号。 需要将数据分组到不同的类别中，例如客户分层（将客户划分为不同的等级，如VIP客户、普通客户等），或者需要将数据平均分配到多个处理单元中。

6. 注意事项

  • OVER() 子句是所有排名函数的核心。 必须正确指定 ORDER BY 子句才能获得预期的排名结果。
  • PARTITION BY 子句可以用来将结果集划分为多个分区，并在每个分区内独立进行排名。
  • 排名函数通常与 CTE (Common Table Expression) 结合使用，以简化查询逻辑。
  • 不同的数据库系统可能对排名函数的支持程度有所不同。 请查阅相关数据库文档。
  • 性能方面，使用排名函数可能会对大型数据集的查询性能产生影响。 在实际应用中，需要根据具体情况进行优化。
  • 在处理大量数据时，理解数据分布情况至关重要。 排名函数的结果会受到数据分布的影响。

7. 理解并灵活运用这些函数，提升数据分析能力

今天我们深入探讨了 ROW_NUMBER(), RANK(), DENSE_RANK() 和 NTILE() 这四个强大的 SQL 排名函数。 它们可以帮助你更好地理解和分析数据，从而做出更明智的决策。 掌握这些函数将显著提升你在数据处理方面的技能。记住根据实际需求选择合适的函数，并结合 PARTITION BY 和 ORDER BY 子句来实现更精细的排名控制。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注