SQL 分组查询如何实现每组排名统计？-SQL-PHP中文网

分组排名通过窗口函数实现，核心是使用ROW_NUMBER()、RANK()和DENSE_RANK()结合PARTITION BY与ORDER BY，在每组内进行排序。ROW_NUMBER()为每行分配唯一序号，即使并列也强制区分；RANK()在并列时给予相同排名，但后续排名跳跃；DENSE_RANK()则在并列时相同排名且后续不跳号。实际应用中可根据业务需求选择函数，并通过添加额外排序字段确保结果确定性，广泛用于电商Top N商品、金融最大交易、用户行为路径分析等场景。

sql 分组查询如何实现每组排名统计？

SQL分组查询实现每组排名统计，核心在于运用窗口函数（Window Functions）。这是一种极其强大且灵活的工具，它允许你在不改变原有分组聚合结果的前提下，对每个分组内的数据进行独立的排序和编号，从而轻松地得到你想要的组内排名。

解决方案

要实现每组排名统计，我们通常会用到SQL的窗口函数，特别是

ROW_NUMBER()

登录后复制

、

RANK()

登录后复制

和

DENSE_RANK()

登录后复制

。它们在

OVER()

登录后复制

子句中结合

PARTITION BY

登录后复制

来定义分组，再用

ORDER BY

登录后复制

来指定组内的排序规则。

想象一下，我们有一个

students_scores

登录后复制

表，记录了学生在不同课程中的成绩：

CREATE TABLE students_scores (
    student_id INT,
    student_name VARCHAR(50),
    course_id INT,
    course_name VARCHAR(50),
    score INT
);

INSERT INTO students_scores (student_id, student_name, course_id, course_name, score) VALUES
(1, '张三', 101, '数学', 95),
(2, '李四', 101, '数学', 90),
(3, '王五', 101, '数学', 95),
(4, '赵六', 101, '数学', 88),
(5, '钱七', 102, '语文', 80),
(6, '孙八', 102, '语文', 85),
(7, '周九', 102, '语文', 85),
(8, '吴十', 102, '语文', 78),
(9, '郑一', 103, '英语', 92),
(10, '王二', 103, '英语', 92),
(11, '李三', 103, '英语', 89);

登录后复制

现在，我们想知道每个课程里学生的成绩排名。这就是分组排名的典型场景。

SELECT
    student_name,
    course_name,
    score,
    ROW_NUMBER() OVER (PARTITION BY course_name ORDER BY score DESC) AS row_num_rank,
    RANK() OVER (PARTITION BY course_name ORDER BY score DESC) AS rank_rank,
    DENSE_RANK() OVER (PARTITION BY course_name ORDER BY score DESC) AS dense_rank_rank
FROM
    students_scores
ORDER BY
    course_name, score DESC;

登录后复制

这段SQL会按照

course_name

登录后复制

进行分组，然后在每个课程组内，根据

score

登录后复制

降序排列，并计算出三种不同的排名。

PARTITION BY course_name

登录后复制

就是定义了“组”的概念，

ORDER BY score DESC

登录后复制

则指定了组内排名的依据。这种方式非常直观，而且效率通常很高，比早期的子查询或变量赋值方法要优雅得多。

SQL中ROW_NUMBER、RANK和DENSE_RANK函数有何区别？

理解这三个窗口函数的细微差别，是掌握分组排名的关键。虽然它们都用于生成排名，但在处理并列（ties）情况时，行为却大相径庭。

```
ROW_NUMBER()
```
登录后复制
: 这个函数为分区（组）内的每一行分配一个唯一的、连续的整数。即使有并列的行，它们也会得到不同的排名。你可以把它想象成一个纯粹的行号计数器，遇到并列时，它的排名是“任意”分配的，通常取决于数据在物理存储或查询优化器处理时的顺序（当然，你也可以通过在
```
ORDER BY
```
登录后复制
子句中添加额外的列来强制一个确定的顺序）。如果你需要从每个组中精确地选出“第N个”元素，哪怕有并列，
```
ROW_NUMBER()
```
登录后复制
是首选。
```
RANK()
```
登录后复制
:
```
RANK()
```
登录后复制
函数处理并列的方式是，并列的行会获得相同的排名，但下一个非并列的行会跳过相应的排名。举个例子，如果两个人并列第一，他们都会得到排名1，但第三个人会得到排名3（排名2被跳过了）。这种排名方式在传统竞赛中很常见，比如“并列第一，然后是第三名”。
```
DENSE_RANK()
```
登录后复制
: 与
```
RANK()
```
登录后复制
类似，
```
DENSE_RANK()
```
登录后复制
也为并列的行分配相同的排名。但它与
```
RANK()
```
登录后复制
不同的是，它不会跳过排名。也就是说，如果两个人并列第一，他们都得到排名1，但第三个人会得到排名2。排名是“紧密”连续的，没有间隔。在某些业务场景下，比如我们想知道“有多少个不同的排名等级”，
```
DENSE_RANK()
```
登录后复制
会更符合预期。

用我们上面的学生成绩数据来直观感受一下：

NameGPT名称生成器

免费AI公司名称生成器，AI在线生成企业名称，注册公司名称起名大全。

查看详情

student_name	course_name	score	row_num_rank	rank_rank	dense_rank_rank
张三	数学	95	1	1	1
王五	数学	95	2	1	1
李四	数学	90	3	3	2
赵六	数学	88	4	4	3
孙八	语文	85	1	1	1
周九	语文	85	2	1	1
钱七	语文	80	3	3	2
吴十	语文	78	4	4	3
郑一	英语	92	1	1	1
王二	英语	92	2	1	1
李三	英语	89	3	3	2

从表中可以清晰地看到，在“数学”和“英语”课程中，张三/王五（数学95分）和郑一/王二（英语92分）都是并列的，三种函数的行为差异一目了然。选择哪个函数，完全取决于你的业务逻辑对“并列”的定义和处理需求。

如何处理分组排名中的并列情况？

处理分组排名中的并列，实际上是根据业务需求选择合适的窗口函数，并在必要时加入额外的排序条件来“打破”并列。这不仅仅是技术实现问题，更是对业务逻辑的深入理解。

当我们遇到并列数据时，首先要明确业务上希望如何对待这些并列项：

所有并列项共享同一排名，且后续排名连续不跳跃：这正是
```
DENSE_RANK()
```
登录后复制
的适用场景。例如，我们想知道有多少个不同的分数等级，或者在不关心具体位次跳跃的情况下，给予并列者相同的“地位”。
所有并列项共享同一排名，但后续排名跳跃：
```
RANK()
```
登录后复制
函数就是为此设计的。它模拟了许多传统排名系统，比如体育赛事中并列金牌后，银牌会直接从第三名开始。
即使并列也需要区分出唯一的排名：这时
```
ROW_NUMBER()
```
登录后复制
就派上用场了。但仅仅使用
```
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY score DESC)
```
登录后复制
，并列项的排名顺序是不确定的。为了让这个排名在并列时也具有确定性，我们需要在
```
ORDER BY
```
登录后复制
子句中添加一个额外的、能唯一区分行的列。

例如，如果“数学”课中有两个学生都考了95分，我们希望分数高的排前面，分数相同的情况下，

student_id

登录后复制

小的排前面。那么SQL可以这样写：

SELECT
    student_name,
    course_name,
    score,
    ROW_NUMBER() OVER (PARTITION BY course_name ORDER BY score DESC, student_id ASC) AS unique_rank_with_tie_breaker
FROM
    students_scores
ORDER BY
    course_name, score DESC, student_id ASC;

登录后复制

这里，

student_id ASC

登录后复制

作为第二个排序条件，在

score

登录后复制

相同的情况下，会根据

student_id

登录后复制

的大小来决定谁排在前面，从而确保

ROW_NUMBER()

登录后复制

给出的是一个完全确定的、唯一的排名。

选择哪种处理方式，取决于具体的业务规则。比如，在生成销售排行榜时，并列第一的销售额，可能更倾向于用

RANK()

登录后复制

来体现传统意义上的排名；但在做用户行为分析，需要找出每个用户在特定操作序列中的“第一个”或“最后一个”行为时，

ROW_NUMBER()

登录后复制

结合合适的

ORDER BY

登录后复制

会更精准。

分组排名在实际业务场景中有哪些应用？

分组排名远不止于学生成绩排名，它在各种业务场景中都扮演着关键角色，帮助我们从海量数据中提炼出有价值的洞察。它的应用场景非常广泛，几乎涵盖了所有需要“组内Top N”或“组内排序”的需求。

电商平台：找出每个商品类别中最畅销的Top 5商品。 这对于库存管理、商品推荐和市场营销策略至关重要。例如，

SELECT * FROM (SELECT product_name, category, sales, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn FROM products) AS ranked_products WHERE rn <= 5;

登录后复制

金融风控：识别每个客户在过去一段时间内交易金额最大的Top 3交易。 这有助于分析客户行为模式，发现异常交易，或者评估客户价值。
网站分析：分析每个用户会话（session）中，用户访问的第一个页面和最后一个页面。 通过
```
PARTITION BY session_id ORDER BY timestamp ASC
```
登录后复制
，可以轻松找出每个会话的起始和结束点，从而分析用户路径。
社交媒体：在每个话题标签下，找出最受欢迎的Top 10帖子。 这能帮助平台推荐热门内容，提升用户活跃度。
游戏行业：统计每个服务器中，玩家的等级排名。 或者找出每个公会中贡献值最高的成员，用于奖励和激励。
供应链管理：在每个仓库中，找出库存周转率最低的Top N商品。 这有助于优化库存结构，避免积压。
人力资源：统计每个部门员工的绩效排名。 辅助绩效评估和晋升决策。

这些例子都体现了分组排名的核心价值：将复杂的全局排序问题，分解为更易于管理和分析的局部排序问题。通过这种方式，我们能够更精细地理解数据，并根据特定分组的上下文做出决策，而不是泛泛地看待所有数据。可以说，只要你的数据存在“组”的概念，并且你需要在这个组内进行某种形式的排序或筛选，分组排名就是你不可或缺的工具。

以上就是SQL 分组查询如何实现每组排名统计？的详细内容，更多请关注php中文网其它相关文章！