跳至主要內容

MySQL 分组之后 TopN 问题的处理方式

ruleeeer原创数据库数据库TopN问题大约 8 分钟约 2434 字

最近在工作中使用 MySQL 碰到了 TopN ,理论上来说这类问题不是 MySQL 的能力范围,不过在数据量较小的场景中仍然可以使用数据库的能力来完成 TopN 问题.

场景

为了避免场景难以理解,我们使用一个最简单的场景来实现该问题. 现在有一张学生成绩表,我们需要查寻出每门课程前 3 名的学生和成绩.

表结构

-- 学生分数
-- auto-generated definition
create table student_score
(
    id     int auto_increment
        primary key,
    name   varchar(50) default '' not null comment '姓名',
    course varchar(50) default '' not null comment '课程',
    score  int         default -1 not null comment '分数'
)
    comment '学生分数';

导入成绩

INSERT INTO student_score (id, name, course, score) VALUES (1, '张三', '数学', 44);
INSERT INTO student_score (id, name, course, score) VALUES (2, '张三', '语文', 55);
INSERT INTO student_score (id, name, course, score) VALUES (3, '张三', '英语', 58);
INSERT INTO student_score (id, name, course, score) VALUES (4, '李四', '数学', 45);
INSERT INTO student_score (id, name, course, score) VALUES (5, '李四', '语文', 87);
INSERT INTO student_score (id, name, course, score) VALUES (6, '李四', '英语', 45);
INSERT INTO student_score (id, name, course, score) VALUES (7, '王五', '数学', 76);
INSERT INTO student_score (id, name, course, score) VALUES (8, '王五', '语文', 34);
INSERT INTO student_score (id, name, course, score) VALUES (9, '王五', '英语', 89);
INSERT INTO student_score (id, name, course, score) VALUES (10, '赵六', '语文', 86);
INSERT INTO student_score (id, name, course, score) VALUES (11, '赵六', '数学', 95);
INSERT INTO student_score (id, name, course, score) VALUES (12, '赵六', '英语', 92);
INSERT INTO student_score (id, name, course, score) VALUES (13, '钱七', '数学', 56);
INSERT INTO student_score (id, name, course, score) VALUES (14, '钱七', '语文', 95);
INSERT INTO student_score (id, name, course, score) VALUES (15, '钱七', '英语', 96);
INSERT INTO student_score (id, name, course, score) VALUES (16, '周八', '数学', 93);
INSERT INTO student_score (id, name, course, score) VALUES (17, '周八', '语文', 99);
INSERT INTO student_score (id, name, course, score) VALUES (18, '周八', '英语', 94);
INSERT INTO student_score (id, name, course, score) VALUES (19, '孙九', '数学', 100);
INSERT INTO student_score (id, name, course, score) VALUES (20, '孙九', '语文', 89);
INSERT INTO student_score (id, name, course, score) VALUES (21, '孙九', '英语', 99);
INSERT INTO student_score (id, name, course, score) VALUES (22, '吴十', '数学', 96);
INSERT INTO student_score (id, name, course, score) VALUES (23, '吴十', '语文', 79);
INSERT INTO student_score (id, name, course, score) VALUES (24, '吴十', '英语', 80);

实现

方式一(union)

对于学生成绩这个场景来说,我们可以使用首先查询出每个科目前三名的成绩,然后使用 union 拼接数据,同理,也可以在外部程序中循环每个科目,然后查询前 N 名数据拼接到一起即可,但是该方式不具有通用性,尤其在科目较多的情况下.

在本场景中,只有三个科目,可以直接使用以下 SQL 查询.

select name, score, course
from (select name, score, course
      from student_score
      where course = '语文'
      order by score desc
      limit 3) temp1
union all
select name, score, course
from (select name, score, course
      from student_score
      where course = '数学'
      order by score desc
      limit 3) temp2
union all
select temp3.name, temp3.score, temp3.course
from (select name, score, course
      from student_score
      where course = '英语'
      order by score desc
      limit 3) temp3

当然选择在外部程序用循环科目查询也是可以的,这里不再演示

方式二(自关联)

注意

表连接查询,在 MySQL 5.7 及以下可以使用这种方式,如果是 MySQL 5.8 及以上,可以使用更方便的开窗函数处理,该方式不推荐在 MySQL 5.8 及以上使用

首先可以简单思考一下,第一名是不是代表没有人比他成绩还高?第二名是不是代表着有只有一个人成绩比他高? 根据这个思路,我们首先使用 SQL 查询出有几个人成绩比我高的数据

这是同表关联,关键点在与连接条件 s1.score < s2.score,这表示比自己成绩高的人作为连接条件 s1.course = s2.course and s1.name != s2.name 则表示需要同科目比较,并且自己不参与比较

select s1.name, s1.course, s1.score
from student_score s1
         left join student_score s2
                   on s1.score < s2.score
                       and s1.course = s2.course
                       and s1.name != s2.name
group by s1.name, s1.course, s1.score

这条 SQL 依然不完整,我们需要的是有几个人我成绩高的数据

提示

补充一个 count(s2.id) 表示有几个人成绩比我高,0 则表示没有人比我高,也就代表是第一名

select s1.name, s1.course, s1.score,
count(s2.id) as higher_than_me
from student_score s1
         left join student_score s2
                   on s1.score < s2.score
                       and s1.course = s2.course
                       and s1.name != s2.name
group by s1.name, s1.course, s1.score
order by s1.course, s1.score desc;

 







执行查询会出现以下结果:

namecoursescorehigher_than_me
孙九数学1000
吴十数学961
赵六数学952
周八数学933
王五数学764
钱七数学565
李四数学456
张三数学447
孙九英语990
钱七英语961
周八英语942
赵六英语923
............

这其实就已经得出排名数据了,有 0 人比我高则代表我是第一名,有 1 人比我高则代表我是第二名. 我们继续为查询添加 having count(s2.id) < 3 条件,表示取前三名

select s1.name, s1.course, s1.score, count(s2.id) as higher_than_me,
count(s2.id)+1 as rank_num
from student_score s1
         left join student_score s2
                   on s1.score < s2.score
                       and s1.course = s2.course
                       and s1.name != s2.name
group by s1.name, s1.course, s1.score
having count(s2.id) < 3
order by s1.course, s1.score desc;

 






 

此时就能得到想要的数据了

namecoursescorehigher_than_merank_num
孙九数学10001
吴十数学9612
赵六数学9523
孙九英语9901
钱七英语9612
周八英语9423
周八语文9901
钱七语文9512
孙九语文8923

方式三(用户变量)

什么是用户变量

MySQL 的用户变量是一种用户定义的变量，以  @  符号开头，可以在查询中使用。它们可以存储任何类型的值，仅在当前会话中有效，会话结束时会被销毁。用户变量可以用于存储中间结果或在查询中传递值。 我们可以使用用户变量来排名成绩,然后直接筛选前三名

注意

注意,切记每次使用前先清空@row_number的数据,因为用户变量是会话级的数据,当前大多数应用都会使用连接池计数,如果不执行 set @row_number:= 0 会导致累加现象.

set @row_number := 0;
select name,
       course,
       score,
       @orw_number := @row_number + 1 as rank_num
from student_score
order by course, score desc;
 



 


会得到如下结果

namecoursescorerank_num
孙九数学1001
吴十数学962
赵六数学953
周八数学934
王五数学765
钱七数学566
李四数学457
张三数学448
孙九英语999
钱七英语9610
............

可以看到,我们已经获取了排名数据 rank_num,但是当前是连续的排名数据,我们需要让排名按照科目分组

如何让 rank_number 可以根据科目分组?

使用另一个用户变量 @course 判断当前这条数据的科目与上一条数据的科目是否不同,如果不同,从 1 开始重新累计排名

set @orw_number := 0;
set @course = '';
select name,
       course,
       score,
       @row_number := if(@course = course, @row_number, 0) + 1 as rank_num,
       @course := course                               as not_use
from student_score
order by course, score desc;

 



 
 


执行后可以得到如下结果,可以看到排名已经按照科目分组了 最后一列 not_use 对于查询结果来说无意义,这是赋值语句 @course:=course 的结果

namecoursescorerank_numnot_use
孙九数学1001数学
吴十数学962数学
赵六数学953数学
周八数学934数学
王五数学765数学
钱七数学566数学
李四数学457数学
张三数学448数学
孙九英语991英语
钱七英语962英语
...............

继续加上前三名的筛选条件就达到目的了

set @row_number := 0;
set @course = '';
select name, course, score, rank_num
from (select name,
             course,
             score,
             @row_number := if(@course = course, @row_number, 0) + 1 as rank_num,
             @course := course                             as not_use
      from student_score
      order by course, score desc) temp
where temp.rank_num <= 3;










 

方式四(MySQL 8 开窗函数 row_number())

以上几种场景都是在 MySQL 5.7 没有开窗函数的条件下实用的,如果当前环境是 MySQL 8,可以使用最简单的开窗函数完成统计

row_number()函数是什么?

row_number() 函数是 MySQL 8 开始提供的开窗函数,它的结果是返回当前的行数,与方案三(用户变量)@row_number 的方式类似,我们可以给每一行一个数字作为排名,然后筛选排名即可

我们首先直接给每一行一个编号作为排名

select course,
       name,
       score,
       row_number() over () as rank_num
from student_score



 

可以得到尚未按照科目分组排名的数据

coursenamescorerank_num
数学张三441
语文张三552
英语张三583
数学李四454
语文李四875
英语李四456
数学王五767
语文王五348
英语王五899
............

row_number() 实际上在使用时可以在 over() 子句中指定分组规则和排序规则 例如 row_number() over (partition by course order by score desc) 表示按照 course 分组然后按照分数逆序返回行号 可以将 SQL 改写为如下形式,就可以达到预期效果了

select course, name, score, rank_num
from (select course,
             name,
             score,
             row_number() over (partition by course order by score desc) as rank_num
      from student_score) t
where t.rank_num <= 3




 

 

总结

综上所述,在 MySQL 5.7 的环境下,有限边界的情况下使用方式一(union)或者在外部程序中处理中较为方便,如果边界太大建议使用方式二(自关联)或者方式三(用户变量)的方式,如果是 MySQL 5.8 及以上环境下直接使用 row_number() 开窗函数即可

上次编辑于:
贡献者: ruleeeer