系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

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

时间:2020-08-13来源:www.pcxitongcheng.com作者:电脑系统城

之前自己一直在用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过滤
分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载