今年春招的时候，面试官给我出了一道题 如何列出成绩表 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 ;
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
该代码流程：
变量赋值，是先运行from 后面的内容，以及排序，排序的目的是把 lession 、grade 放到各自的组中（这一点和我们原来的先select 后order 是不一样的，等下会有说明）此时@rank等于0,@lession等于null
开始进行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的分组排序和变量赋值顺序
1084
loserwang
Java开发 @ 家里蹲
