SQL窗口排序函数 RANK/DENSE_RANK/ROW_NUMBER详解

row_number()、rank()、dense_rank()在sql窗口函数中分别用于唯一行号分配、并列跳过排名和并列不跳过排名。1. row_number()为每行分配唯一序号，不受值重复影响，适合去重或选择最新记录；2. rank()对相同值赋予相同排名，但后续排名跳过相应位数，适用于竞赛名次固定场景；3. dense_rank()同样对相同值赋予相同排名，但后续排名连续递增，适合需要紧凑排名的场景；三者的选择取决于具体业务需求，如是否允许排名空缺或是否强调相对位置。

SQL窗口函数中的RANK(), DENSE_RANK(), ROW_NUMBER()是处理数据排序和排名的利器。简单来说，ROW_NUMBER()为每行分配一个唯一的序列号，不考虑值是否相同；RANK()在遇到相同值时会给出相同的排名，并在下一个不同值时跳过相应的排名；而DENSE_RANK()则在遇到相同值时给出相同排名，但不会跳过后续的排名，而是紧密地递增。它们各自适用于不同的业务场景，理解其差异是高效数据分析的关键。

SQL中的窗口函数，尤其是RANK(), DENSE_RANK(), ROW_NUMBER()，是数据分析师和开发者日常工作中不可或缺的工具。它们允许我们在一个“窗口”内对数据进行分区和排序，并为每一行生成一个排名或序号。

ROW_NUMBER()：这个函数非常直接，它为结果集中的每一行分配一个唯一的、连续的整数。如果你的数据有重复值，ROW_NUMBER()仍然会为它们分配不同的序号，因为它的核心是行号，而不是值的排名。它非常适合需要对数据进行分页、或者在每个分组中只选择“第一条”或“最新一条”记录的场景。

RANK()：当你需要根据某个或某些列的值进行排名，并且希望相同的值拥有相同的排名时，RANK()就派上用场了。它的一个特点是，如果存在并列（tie）的情况，它会跳过接下来的排名。例如，如果有两个人并列第一，那么下一个人的排名会是第三，而不是第二。这在一些竞赛或排行榜场景中很常见，比如“前三名”的奖项，即使有并列，名额也是固定的。

DENSE_RANK()：与RANK()类似，DENSE_RANK()也会为相同的值分配相同的排名。但它与RANK()的主要区别在于，它不会跳过排名。也就是说，如果有多人并列第一，下一个人的排名会是第二，而不是第三。这在需要连续排名的场景中非常有用，例如，你想知道某个分数在所有分数中的“实际”排名，不希望因为并列而产生排名上的“空洞”。

让我们通过一个简单的例子来对比这三个函数：

假设我们有一个Students表，包含Name和Score：

SELECT
    Name,
    Score,
    ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum,
    RANK() OVER (ORDER BY Score DESC) AS RankNum,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRankNum
FROM
    Students;

结果会是这样：

从结果中可以清晰地看到它们处理并列的差异：

• ROW_NUMBER()：Alice和Charlie分数相同，但因为Alice在排序中可能排在前面，所以她得到1，Charlie得到2。每行都有一个独一无二的序号。
• RANK()：Alice和Charlie分数都是90，并列第一，所以都得到1。但因为占用了两个名次，Bob和Eve（85分）的排名就从3开始了。
• DENSE_RANK()：Alice和Charlie分数都是90，并列第一，都得到1。Bob和Eve（85分）的排名紧接着从2开始，没有跳过排名。

何时选择 ROW_NUMBER()？

我个人在实际工作中，ROW_NUMBER()的用处非常广泛，尤其是在需要确保唯一性和精确控制行顺序的场景。比如说，你可能需要从每个用户最近的几次操作中，只取出最新的那一条记录。这时候，ROW_NUMBER()结合PARTITION BY就显得无比强大。

一个典型的应用场景是“去重”或者“选择最新/最旧记录”。假设你有一个日志表，记录了用户每次登录的信息，但你只想知道每个用户最近一次的登录时间。

简篇AI排版

AI排版工具，上传图文素材，秒出专业效果！

554

查看详情

SELECT
    UserID,
    LoginTime,
    Action
FROM (
    SELECT
        UserID,
        LoginTime,
        Action,
        ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginTime DESC) AS rn
    FROM
        UserLog
) AS SubQuery
WHERE rn = 1;

这里，PARTITION BY UserID将数据按用户ID分组，ORDER BY LoginTime DESC确保在每个用户组内，最新的登录时间排在最前面。ROW_NUMBER()为每个用户组内的记录分配序号，我们通过WHERE rn = 1就能轻松筛选出每个用户的最新一条登录记录。这种模式在处理历史数据、版本控制或者需要获取每个实体最新状态时，简直是标准操作。

RANK() 与 DENSE_RANK()：处理并列排名的策略差异

这两种函数在处理并列排名时表现出的“跳跃”与“连续”差异，往往决定了它们在不同业务场景下的适用性。

RANK()的跳跃性在某些情况下是业务逻辑的精确体现。举个例子，如果你在做一个竞赛系统，要评选“前三名”，那么即使有两个人并列第一，他们都算作第一名，但“第二名”这个位置实际上是空缺的，下一个不同成绩的人就是第三名。这种情况下，使用RANK()能更真实地反映这种“名次”的消耗。我曾遇到过一个需求，需要找出每个产品类别中销量最高的三个产品，即便有并列，也希望实际“占据”了三个名额。这时，RANK()就非常合适。

而DENSE_RANK()的连续性则在需要一个“紧凑”排名序列时更为理想。比如，你可能在分析学生成绩，想知道某个分数段的学生在所有学生中的“实际”位置，而不是考虑是否有空缺。或者，在一个游戏排行榜中，如果多名玩家分数相同，他们都显示为同一排名，并且下一个分数的玩家紧接着排在下一个数字上，这样用户体验会更自然，因为排名看起来是连续的。我倾向于在制作用户可见的“排行榜”功能时使用DENSE_RANK()，因为它给人的感觉是排名是连续的，没有因为并列而产生的“断层”。

选择哪个，真的取决于你对“排名”这个概念在特定业务场景下的定义。是名额有限，名次会被消耗？还是仅仅想知道相对位置，不希望有空缺？这是思考的关键。

窗口函数中的 PARTITION BY 与 ORDER BY：它们如何塑造排名？

PARTITION BY和ORDER BY是窗口函数中两个至关重要的子句，它们共同定义了“窗口”的范围和内部的排序规则，从而直接影响了RANK(), DENSE_RANK(), ROW_NUMBER()的计算结果。

PARTITION BY子句的作用是将你的数据集划分为独立的、不重叠的逻辑分区。可以把它想象成在计算排名之前，数据被分成了很多个小块，每个小块内部独立进行排名。如果你省略了PARTITION BY，那么整个查询结果集将被视为一个单一的、巨大的分区。这意味着排名将针对所有行进行计算。

举个例子，如果你想对每个班级的学生进行排名，而不是对整个学校的学生进行排名，那么你就需要PARTITION BY ClassID。这样，每个班级内部的排名都是从1开始独立计算的，互不影响。我记得有一次在分析销售数据时，需要找出每个区域销售额最高的产品，如果不用PARTITION BY Region，那得到的就是全国销售额最高的产品，这显然不符合业务需求。

而ORDER BY子句则决定了在每个分区（或整个数据集，如果没分区）内部，行的排序顺序。这个顺序直接影响了RANK(), DENSE_RANK(), ROW_NUMBER()如何分配它们的序号或排名。例如，ORDER BY Score DESC会使分数高的排在前面，得到更小的排名数字。如果ORDER BY的列有重复值，那么对于ROW_NUMBER()来说，行的物理顺序（或者说数据库内部的某种确定性顺序，比如主键）可能会影响最终的分配，但在逻辑上，它依然会给每个重复值分配一个唯一的序号。

理解这两个子句的协同作用是掌握窗口函数的关键。PARTITION BY定义了“谁与谁一起排名”，而ORDER BY定义了“按什么顺序排名”。它们共同构建了窗口函数的强大灵活性，使得我们能够处理各种复杂的排名和分析需求。在编写SQL时，我常常会先在脑海中勾勒出数据的分区逻辑，再确定排序的优先级，这样就能避免很多不必要的返工。

以上就是SQL窗口排序函数 RANK/DENSE_RANK/ROW_NUMBER详解的详细内容，更多请关注php中文网其它相关文章！