【原】MySQL窗口函数的妙用

贪挽懒月 2022-11-12 发布于广东

展开全文

问题引入

有一张成绩表，里面有若干个学生，他们来自三个班级，每个学生学习了两门课程，现在要求查询出各个班级每门课程的前两名学生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分的也占了一个名额，并不会把分数第二大的学生查询出来。3. dense_rank()的用法直接将rank换成dense_rank就可以看出区别了。dense_rank两个人并列第一名只会占用一个名额，会把分数第二大的也查出来，所以总共查出了13个学生。4. 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;