mysql如何统计各科前十名的成绩

loserwang
1,072 阅读5分钟

本文正在参加「技术专题19期 漫谈数据库技术」活动, 我是新人loserwang，希望大家多多支持和交流。

what

今年春招的时候，面试官给我出了一道题 如何列出成绩表 grade_list(student, lession, score)中各科前10名？

当时我一看，这有什么难的，直接 按照学科分组排序 + limit 呗。但是当我一写的时候发现，limit的结果是全局的，并不能把每个学科的前10名列出来。我这是平时业务思维习惯了，没能及时转到数据库Sql思维上。最后果然面试寄了。

痛定思痛，我决定恶补Sql知识，看看如何解决这道题。

窗口函数

窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。窗口函数为：在满足某些条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同，窗口大小都是固定的，称为静态窗口；有的函数则相反，不同的记录对应着不同的窗口，称为滑动窗口。

窗口函数和聚合函数的区别如下：

  • 聚合函数通过会将所有记录进行分类聚合；而窗口函数是对所有数据记录按照指定窗口进行计算，不会进行聚合。例如同样是avg,聚合函数是总共的平均值，而窗口函数则输出n个值，第i个值为[1,i]的平均值。
  • 窗口函数更多的是业务中需要对数据做排序/分组排序，环比计算，百分比计算等需求。
  • 聚合函数可用于窗口函数。

语法规则：

window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )
  • over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行
  • PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
  • ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；
  • FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

MySQL中的窗口函数：

名称描述
CUME_DIST计算一组值中值的累积分布。
DENSE_RANK根据ORDER BY子句为其分区中的每一行分配一个排名。 它为具有相同值的行分配相同的排名。 如果两行或更多行具有相同的等级，则排序值序列中将没有间隙。
FIRST_VALUE返回指定表达式相对于窗口框架中第一行的值。
LAG返回分区中当前行之前的第N行的值。 如果不存在前一行，则返回NULL。
LAST_VALUE返回指定表达式相对于窗口框架中最后一行的值。
LEAD返回分区中当前行之后的第N行的值。 如果不存在后续行，则返回NULL。
NTH_VALUE返回窗口框架第N行的参数值
NTILE将每个窗口分区的行分配到指定数量的已排名组中。
PERCENT_RANK计算分区或结果集中行的百分位数
RANK与DENSE_RANK（）函数类似，只是当两行或更多行具有相同的排名时，排序值序列中存在间隙。
ROW_NUMBER为其分区中的每一行分配一个连续整数

所以通过窗口函数就很简单：

# 计算并列排名
SELECT * FROM
(
  SELECT lession, student，grade, RANK() OVER 
     (PARTITION BY lession ORDER BY grade) as rank_number 
  FROM grade_list
) AS new_grade_list
where rank_number  <= 10;

# 没有并列排名
SELECT * FROM
(
  SELECT lession, student，grade, ROW_NUMBER() OVER 
     (PARTITION BY lession ORDER BY grade) as rank_number 
  FROM grade_list
) AS new_grade_list
where rank_number  <= 10;

but, MySQL从8.0开始支持开窗函数。如果低版本的时候，我们只能采用其他方法。

Join获取排名

我们可以采用上面的相同想法，所谓的某学科第i名，就是该学科有i-1个值比当前值大。则我们只需要join两张表，就可以计算比当前值大的个数(即学科排名)。

# 计算每个学科的排名
select *, count(a.score) rank from grade_list as a 
left join 
grade_list as b
on a.lession = b.lession and a.score < b.score
group by a.lession, a.student


# 获取每个学科前10名
select * from 
(    select *, count(a.score) rank from grade_list as a 
        left join 
        grade_list as b
        on a.lession = b.lession and a.score < b.score
        group by a.lession, a.student
) AS new_grade_list
where rank_number  <= 10;

该方法的时间和空间复杂度比较高，均为O（N^2)

MySQL变量

有时候，您希望将值从SQL语句传递给另一个SQL语句。为此，您可将该值存储在第一个语句中的MySQL用户定义的变量中，并在随后的语句中引用它。 要创建用户定义的变量，请使用格式@variable_name，其中variable_name由字母数字字符组成。由一个客户端定义的用户定义的变量不被其他客户端看到。 换句话说，用户定义的变量是特定于会话的。

有两种方法可以将值分配给用户定义的变量。

SET @variable_name := value;

or

SELECT @variable_name := value;

那么我们可以采用如下代码进行排序：

# 无重复的排名，如果需要重复相等的排名，再加变量即可
select  
        lession,
        student,
        grade,
        @rank:=if(@lession=lession,@rank+1,1) rank,
        @lession:=lession
    from person,(select @rank:=0,@lession:=null) temp
    order by lession, grade asc

该代码流程：

  1. 变量赋值，是先运行from 后面的内容，以及排序，排序的目的是把 lession、grade放到各自的组中（这一点和我们原来的先select 后order 是不一样的，等下会有说明）此时@rank等于0,@lession等于null

  2. 开始进行select中的内容

    • 运行 @rank:=if(@gen=gender,@rank+1,1) rank，此时@gen是等于null的，而gender 是第一行的值，所以IF函数将会返回1，第一行的rank就会返回1，接着运行@gen:=gender ，此时的@gen会被赋值第一行的
    • 还是先运行@rank:=if(@gen=gender,@rank+1,1) rank，此时的@gen是等于gender，根据IF会返回@rank+1 然后赋值到@rank，直到遇到下一个不一样的gender，@rank 才会重新变成1

时间复杂度为排序O(NLogN)，即排序的复杂度，遍历只需要O(N）

参考文章：

MySQL 窗口函数

mysql变量

数据分析面试之mysql的分组排序和变量赋值顺序

avatar
文章
阅读
粉丝