MySQL窗口函数的妙用
- 问题引入
有一张成绩表,里面有若干个学生,他们来自三个班级,每个学生学习了两门课程,现在要求查询出各个班级每门课程的前两名学生id。
- 分析
要各个班级每门课程的前两名,第一反应肯定要根据班级和课程去分组,但实际上你要是用
group by
会发现不好处理,因为我们要取每个班的前两名,如果你用group by
再用limit
,那返回的数据是在总数中的取 n 条记录,而不是每个班取 n 条记录。所以我们得用其他方式实现,比如窗口函数。
- 窗口函数
窗口函数是可以对数据库进行实时分析处理的函数,可以理解为它是对
where
或group by
处理后的结果再进行操作,基本语法如下: <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>) 窗口函数又分为两类:
- 专用窗口函数,比如
rank、dense_rank、row_number
等 - 聚合函数,比如
sum、avg、count、max、min
等
- 用法
回到刚才那个问题,用窗口函数怎么处理呢?
- 首先准备一张表,再插入一些数据
create table grade ( id int(10) primary key auto_increment comment '主键', stu_id int(10) comment '学生id', class_id int(10) comment '班级id', course_id int(3) comment '课程id', score int(3) comment '学生分数', unique key (stu_id, course_id) ) engine = innodb charset = utf8; insert into grade (stu_id, class_id, course_id, score) values ('1', 1, 1, 90), ('4', 1, 1, 90), ('7', 1, 1, 84), ('10', 1, 1, 84), ('13', 1, 1, 88), ('1', 1, 2, 67), ('4', 1, 2, 85), ('7', 1, 2, 90), ('10', 1, 2, 88), ('13', 1, 2, 86); insert into grade (stu_id, class_id, course_id, score) values ('2', 2, 1, 83), ('5', 2, 1, 94), ('8', 2, 1, 81), ('11', 2, 1, 91), ('14', 2, 1, 79), ('2', 2, 2, 99), ('5', 2, 2, 80), ('8', 2, 2, 82), ('11', 2, 2, 76), ('14', 2, 2, 66); insert into grade (stu_id, class_id, course_id, score) values ('3', 3, 1, 98), ('6', 3, 1, 92), ('9', 3, 1, 76), ('12', 3, 1, 73), ('15', 3, 1, 83), ('3', 3, 2, 95), ('6', 3, 2, 91), ('9', 3, 2, 86), ('12', 3, 2, 87), ('15', 3, 2, 68); 这里就是建立了一个成绩表,然后往表中插入了15个学生,他们来自三个班级,每个学生学习了两门课程。
- rank函数的用法
按照上面窗口函数的语法,写出如下SQL:
select * from (select *, rank() over (partition by class_id, course_id order by score desc ) as ranking from grade) t where t.ranking <= 2;
先看里面窗口函数那一层,首先是用了rank()
,partition by class_id, course_id
就表示根据class_id
和course_id
来分组,order by score desc
就表示按照分数降序,然后把分组且分数降序的结果作为ranking
字段,就是排名。外层ranking <= 2
就表示每个分组取两条数据。
这样查询出来的就满足要求了,为了让结果更加清晰,外层稍微改写一下,不要select *
,改成如下所示:
select stu_id, case when class_id = 1 then '六(1)班' when class_id = 2 then '六(2)班' else '六(3)班' end as class, IF(course_id = 1, '语文', '数学') as course, score, ranking from (select *, rank() over (partition by class_id, course_id order by score desc ) as ranking from grade) t where t.ranking <= 2;
执行结果如下:
结果
可以看到,六(1)班语文有两个90分的,他们并列第一,但是用rank
的时候,第二个90分的也占了一个名额,并不会把分数第二大的学生查询出来。
- dense_rank()的用法
直接将
rank
换成dense_rank
就可以看出区别了。
结果
dense_rank
两个人并列第一名只会占用一个名额,会把分数第二大的也查出来,所以总共查出了13个学生。
- row_number()的用法 换成row_number再看执行结果:
结果
- 我用的MySQL5.x,没有这些窗口函数怎么办?
上面说的窗口函数要MySQL8.0+才支持,5.x的话可以自己去实现。比如要实现一个row_number(),格式如下:
select <要查询的字段>,
ranking from (select @ranking := if(@<分组字段1> = <分组字段1> and @<分组字段2> = <分组字段2>, @ranking + >1, 1) as ranking,
@<分组字段1> := <分组字段1> as <分组字段1>, @<分组字段2> := <分组字段2> as <分组字段2>, <排序字段> <其他需要查询出来的字段> from (select * from <表名> order by <分组字段1>, <分组字段2>, <排序字段> desc) a, (select @ranking = 0, @<分组字段一> = 0) b ) c where ranking <= <要取的条数>;
根据题目要求,将分组字段和排序字段代入上面的公式,可得:
select stu_id,
case when class_id = 1 then '六(1)班' when class_id = 2 then '六(2)班' else '六(3)班' end as class,
IF(course_id = 1, '语文', '数学')
as course,
score,
ranking from (select @ranking := if(@class_id = class_id and @course_id = course_id, @ranking + 1, 1) as ranking,
@class_id := class_id
as class_id,
@course_id := course_id
as course_id,
score,
stu_id
from (select * from grade order by class_id, course_id, score desc) a,
(select @ranking = 0, @class_id = 0) b ) c where ranking <= 2;
相关文章
- MySQL中concat()、concat_ws()、group_concat()函数
- MySQL中数学函数的应用(mysql数学函数)
- MySQL时间函数:提高数据库效率(mysql数据库时间函数)
- MySQL中的字符串长度函数:功能与使用(mysql字符串长度函数)
- MySQL查询与建表:快速实现你的目标(mysql查询并建表)
- 选择如何使用MySQL创建函数选择(创建函数mysql)
- MySQL查询中的IF语句用法简介(mysql查询if)
- MySQL分布式存储:实现高性能存储(mysql分布式存储实现)
- MySQL 跨库数据复制技术实战(mysql跨库复制)
- 使用 MySQL 长度函数轻松获取数据字段长度(mysql长度函数)
- MySQL之减法函数的使用(mysql 减 函数)
- 使用MySQL中STR函数实现字符串操作(mysql 中 str)
- MySQL中使用IN函数实现范围查询(mysql中in的范围)
- MySQL中的file类型存储文件路径的最佳选择(mysql中file类型)
- MySQL中CEIL函数的使用方法(mysql中ceil用法)
- 中实现向上取整 MySQL中用CEIL函数实现向上取整(ceil 在mysql)
- MySQL函数测试攻略(mysql中函数测试)
- 对比MySQL三个比较函数,帮你更好地理解数据查询(mysql 三者比较函数)
- MySQL表清理函数简单高效地清除无用数据(mysql下清理表函数)