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過濾