MySQL 8.0 窗口函数 排名、topN问题

之前自己一直在用mysql5.7，没想到在8.0中加入了一个非常好用的功能---窗口函数，在这里将窗口函数、及其用法、可以解决的问题总结如下

what

窗口函数 --- 又名OLAP函数 --- Online Anallytical Processing，联机分析处理

窗口函数功能是从Oracle搬过来的，看样子Oracle收购mysql也有好处

主要解决业务中的排名与topN问题

how

基本语法：

窗口函数名() over (partition by 分组列名 order by 排序列名) as ranking

（as ranking是常用的命名方式，当然，你可以用别的名字）

分类：

• MySQL原有的一些聚合函数也可以用在窗口函数的语法中，当做窗口函数使用，比如sum(),avg(),max(),min(),count()等

  这里主要结合问题探讨序号函数

• 问："能否对窗口函数返回的结果直接进行过滤"

  答："因为窗口函数的发生位置（顺序）在where和group by之后，所以不能"（请注意，这里说的是“直接”，你当然可以将窗口函数的结果作为一个临时表然后再select...where...）

  

与group by的区别（重要）

行数

比如说表math有如下字段：班级(class)、学生id(stu_id)、每个学生某科的成绩(score)，我们先不排名，先统计每个班的人数

如果使用

select class, count(stu_id)
from math
group by class
order by class

# 那么展现的结果为
class	count(stu_id)
class1	3
class2	1
class3	2

也就是说，使用group by可能会改名表的行数 --- 一行展示一个group

如果使用窗口函数 --- 原表多少行就是多少行

select class, count(stu_id) over (partition by class order by class) as stu_num
from class

# 结果为
class	stu_num
class1	3
class1	3
class1	3
class2	1
class3	2
class3	2

说明，partiton by分组不会减少原表中的行数

分层的聚合函数

窗口函数中也可以使用sum、avg、count等，都是对每行自己以及自己以上的数据进行聚合的，比如：

# 使用sum作为聚合函数的结果 --- 对每个班级的学生总分进行分层sum
class	stu_id	Score
1		001		100
1		002		200
1		003		300
2		004		100
2		005		200

# 如果使用传统的group by那么就只有class和sum(score)了

序号函数

对于成绩和名次排序，生活中也会有不同的排序方式，比如说同分的如何排名等。于是也就有了不同的序号函数：假如有如下成绩

table marks:
stu_id	marks
1		5
2		5
3		4
4		2
5		1

dense_rank() --- 同分同名次，不占后面的名次，日常中常使用这种

select stu_id, dense_rank() over (order by marks) as dense_ranking
from marks

# 结果就是
stu_id	marks	ranking
1		5		1
2		5		1
3		4		2
4		2		3
5		1		4

rank() --- 同分同名次，但会“占用名次”，使用如上数据：

select stu_id, rank() over (order by marks) as ranking
from marks

# 结果就是
stu_id	marks	ranking
1		5		1
2		5		1
3		4		3
4		2		4
5		1		5

row_number() --- 同分不同名次，也排名，顾名思义，就和行号一样

select stu_id, row_number() over (order by marks) as row_number
from marks

# 结果就是
stu_id	marks	ranking
1		5		1
2		5		2
3		4		3
4		2		4
5		1		5

排名问题

https://leetcode-cn.com/problems/rank-scores/

（mysql 8）

select 
    a.Score as Score,
    (select count(distinct b.Score) from Scores as b where b.Score>=a.Score) as `Rank`
from Scores as a
order by Score desc;

（常规解法）

select 
	a.Score as Score, 
	(select count(b.score) from Scores as b where b.Score>=a.Score) as `Rank`
from Scores as a
order by Score desc;

思路：对于每个成绩，表中大于该成绩的成绩的个数，就是成绩的排名

topN问题

假设有表Scores

Stu_id		subject		score 
1			math		99
2			chinese		100
1			english		66
2			math		80
2			english		80
1			chinese		98

我们希望了解，每个学生考的最好的两门课

select *, row_number() over (partition by Stu_id order by score) as ranking
from Scores
where ranking<2;

Stu_id		subject		score	ranking 
1			math		99		1
1			chinese		98		2
1			english		66		3
2			chinese		100		1
2			math		80		2
2			english		80		3
# 通过ranking<2过滤