查看原文
其他

MySQL 窗口函数是什么,有这么好用

The following article is from 古时的风筝 Author 风筝

先看这段像天书一样的 SQL ,看着就头疼。

SELECT
s1.name,
s1.subject,
s1.score,
sub.avg_score AS average_score_per_subject,
(SELECT COUNT(DISTINCT s2.score) + 1 FROM scores s2 WHERE s2.score > s1.score) AS score_rank
FROM scores s1
JOIN (
SELECT subject, AVG(score) AS avg_score
FROM scores
GROUP BY subject
) sub ON s1.subject = sub.subject
ORDER BY s1.score DESC;

这段SQL是干什么用的呢,就是为了计算一个成绩排名,简直大动干戈啊。

那有没有简化的方法呢?有的。

简化后的版本就是利用今天说的窗口函数。

SELECT
name,
subject,
score,
AVG(score) OVER (PARTITION BY subject) AS average_score_per_subject,
RANK() OVER (ORDER BY score DESC) AS score_rank
FROM scores
ORDER BY score DESC;

是不是看上去就简洁清晰多了。

下面我们看看是什么样的功能。

首先创建一个表,包含姓名、学科、分数三个字段,用于后面功能的演示。

CREATE TABLE `scores` (
`name` varchar(20) COLLATE utf8_bin NOT NULL,
`subject` varchar(20) COLLATE utf8_bin NOT NULL,
`score` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

然后向表中插入一些随机记录。

INSERT INTO scores (name, subject, score) VALUES ('Student1', '化学', 75);
INSERT INTO scores (name, subject, score) VALUES ('Student2', '生物', 92);
INSERT INTO scores (name, subject, score) VALUES ('Student3', '物理', 87);
INSERT INTO scores (name, subject, score) VALUES ('Student4', '数学', 68);
INSERT INTO scores (name, subject, score) VALUES ('Student5', '英语', 91);
INSERT INTO scores (name, subject, score) VALUES ('Student6', '化学', 58);
INSERT INTO scores (name, subject, score) VALUES ('Student7', '物理', 79);
INSERT INTO scores (name, subject, score) VALUES ('Student8', '数学', 90);
INSERT INTO scores (name, subject, score) VALUES ('Student9', '数学', 45);

##什么是窗口函数

在 MySQL 8.x 版本中,MySQL 提供了窗口函数,窗口函数是一种在查询结果的特定窗口范围内进行计算的函数。

很早以前用 Oracle 和 MS SQL 的时候会用到里面的窗口函数,但是用 MySQL 后才发现,MySQL 竟然没有窗口函数,以至于一些负责的统计查询都要用各种子查询、join,层层嵌套,看上去很简单的需求,结果搞得 SQL 语句写的是龙飞凤舞,别人一看跟天书似的。就一个字儿,

窗口函数主要的应用场景是统计和计算,例如对查询结果进行分组、排序和计算聚合,通过各个函数的组合,可以实现各种复杂的逻辑,而且比起 MySQL 8.0之前用子查询、join 的方式,性能上要好得多。

OVER()

OVER() 是用于定义窗口函数的子句,它必须结合其他的函数才有意义,比如求和、求平均数。而它只用于指定要计算的数据范围和排序方式。

function_name(...) OVER (
[PARTITION BY expr_list]
[ORDER BY expr_list]
[range]
)

PARTITION BY

用于指定分区字段,对不同分区进行分析计算,分区其实就列,可以指定一个列,也可以指定多个列。

ORDER BY

用于对分区内记录进行排序,排序后可以与「范围和滚动窗口」一起使用。

范围和滚动窗口

用于指定分析函数的窗口,包括范围和滚动窗口。

范围窗口(Range window)

指定窗口的起止行号,使用UNBOUNDED PRECEDING表示起点,UNBOUNDED FOLLOWING表示终点。

例如:

SUM(salary) OVER (ORDER BY id
RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING)

这会计算当前行及之前5行和之后5行的salary总和。

滚动窗口(Row window)

使用了基于当前行的滚动窗口

例如:

SUM(salary) OVER (ORDER BY id
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

这会计算当前行及之前2行和之后2行的salary总和。

OVER()可搭配的函数:

聚合函数

MAX(),MIN(),COUNT(),SUM()等,用于生成每个分区的聚合结果。

排序相关

ROW_NUMBER(),RANK(),DENSE_RANK()等,用于生成每个分区的行号或排名。

窗口函数

LAG(),LEAD(),FIRST_VALUE(),LAST_VALUE()等,用于基于窗口框生成结果。

搭配聚合函数

1、按subject列进行分区,并求出某学科的最大最小值

获取分数和此学科最高分

SELECT subject,score, MAX(score) OVER (PARTITION BY subject) as `此学科最高分` FROM scores;

得出的结果是:

subjectscore此学科最高分
化学7575
化学5875
数学6890
数学9090
数学4590
物理8787
物理7987
生物9292
英语9191

2、获取学科的报名人数

SELECT subject,score, count(name) OVER (PARTITION BY subject) as `报名此学科人数` FROM scores;

得到的结果为:

subjectscore报名此学科人数
化学752
化学582
数学683
数学903
数学453
物理872
物理792
生物921
英语911

3、求学科的总分

SELECT subject, SUM(score) OVER (PARTITION BY subject) as `此学科总分` FROM scores;

得到的结果:

subject此学科总分
化学133
化学133
数学203
数学203
数学203
物理166
物理166
生物92
英语91

4、使用 order by 求累加分数

SELECT name,subject,score, SUM(score) OVER (order BY score) as `累加分数` FROM scores;

得到的结果:

namesubjectscore累加分数
Student9数学4545
Student6化学58103
Student4数学68171

我们看这是怎么算出来的,OVER 函数里面是 order by 。

首先根据分数排序(默认升序),得到第一行分数是45,所以累加分数就是它自己,也就是45。

然后排序得到第二行 58,然后将第一行和第二行相加,这样得到累加分数就是45+58=103。

同理,第三行就是前三行的总和,也就是45+58+68=171。

以此类推,第 N 行就是1~N的累加和。

5、使用 order by + 范围

前面因为没有限定范围,所以就是前 N 行的累加,还可以限定范围。

SELECT name,subject,score, SUM(score) OVER (order BY `score` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as `累加分数` FROM scores;

这里的累加分数是指当前行+前一行+后一行的和。

获取的结果为:

namesubjectscore累加分数
Student9数学45103
Student6化学58171
Student4数学68201
Student1化学75222
Student7物理79241
Student3物理87256
Student8数学90268
Student5英语91273

第一行 103,是当前行 45+后一行(58)的和,等于103,因为没有前一行。

第二行171,是当前行58+前一行(45)+后一行(68)的和,等于171。

以此类型,后面的累加分数都是这样算出来的。

搭配排序相关函数

ROW_NUMBER()

ROW_NUMBER() 函数用于为结果集中的每一行分配一个唯一的排序。

如下,对成绩进行排名,分数高的排在前面,如果有两个人分数相同,那仍然是一个第一,另一个第二。

SELECT name,subject,score, ROW_NUMBER() OVER (order BY `score` descas `排名` FROM scores;

查询结果为:

namesubjectscore排名
Student2生物921
Student5英语912
Student8数学903
Student3物理874
Student7物理795

如果不用 ROW_NUMBER(),比如在 MySQL 5.7的版本中,就会像下面这样:

SELECT s1.name, s1.subject, s1.score, COUNT(s2.score) + 1 AS `排名`
FROM scores s1
LEFT JOIN scores s2 ON s1.score < s2.score
GROUP BY s1.name, s1.subject, s1.score
ORDER BY s1.score DESC;

是不是比使用 ROW_NUMBER()复杂的多。

RANK()

RANK() 函数用于为结果集中的每一行分配一个排名值,它也是排名的,但是它和 ROW_NUMBER()有,RANK()函数在遇到相同值的行会将排名设置为相同的,就像是并列排名。

就像是奥运比赛,如果有两个人都是相同的高分,那可能就是并列金牌,但是这时候就没有银牌了,仅次于这两个人的排名就会变成铜牌。

SELECT name,subject,score, RANK() OVER (order BY `score` descas `排名` FROM scores;

查询结果为:

namesubjectscore排名
Student1化学921
Student2生物921
Student5英语913
Student8数学904
Student3物理875

DENSE_RANK()

DENSE_RANK() 也是用作排名的,和 RANK()函数的差别就是遇到相同值的时候,不会跳过排名,比如两个人是并列金牌,排名都是1,那仅次于这两个人的排名就是2,而不像 RANK()那样是3。

SELECT name,subject,score, DENSE_RANK() OVER (order BY `score` descas `排名` FROM scores;

查询结果为:

namesubjectscore排名
Student1化学921
Student2生物921
Student5英语912
Student8数学903

配合其他窗口函数

NTILE()

NTILE() 函数用于将结果集划分为指定数量的组,并为每个组分配一个编号。例如,将分数倒序排序并分成4个组,相当于有了4个梯队。

SELECT name,subject,score, NTILE(4OVER (order BY `score` descas `组` FROM scores;

查询结果为:

namesubjectscore
Student1化学921
Student2生物921
Student5英语911
Student8数学902
Student3物理872
Student7物理793
Student4数学683
Student6化学584
Student9数学454

LAG()

LAG() 函数用于在查询结果中访问当前行之前的行的数据。它允许您检索前一行的值,并将其与当前行的值进行比较或计算差异。LAG()函数对于处理时间序列数据或比较相邻行的值非常有用。

LAG()函数完整的表达式为 LAG(column, offset, default_value),包含三个参数:

column:就是列名,获取哪个列的值就是哪个列名,很好理解。

offset: 就是向前的偏移量,取当前行的前一行就是1,前前两行就是2。

default_value:是可选值,如果向前偏移的行不存在,就取这个默认值。

例如比较相邻两个排名的分数差,可以这样写:

SELECT
  name,
  subject,
  score,
  ABS(score - LAG(score, 1,score) OVER (ORDER BY score DESC)) AS `分值差`
FROM
  scores;

得到的结果为:

namesubjectscore分值差
Student1化学920
Student2生物920
Student5英语911
Student8数学901
Student3物理873
Student7物理798
Student4数学6811

LEAD()

LEAD() 函数和 LAG()的功能一致,只不过它的偏移量是向后偏移,也就是取当前行的后 N 行。

所以前面的比较相邻两行差值的逻辑,也可以向后比较。

SELECT
name,
subject,
score,
score - LEAD(score, 1,score) OVER (ORDER BY score DESC) AS `分值差`
FROM
scores;

得到的结果:

namesubjectscore分值差
Student1化学920
Student2生物921
Student5英语911
Student8数学903
Student3物理878
Student7物理7911
Student4数学6810




迎学编程的朋友们加入鱼皮的 编程导航 ,和 2 万多名编程学习者共享知识、交流进步,学习鱼皮全程直播开发的原创项目、上千篇优质编程学习求职经验分享、并获取 1 对 1 答疑指导服务。

往期推荐

我的学习小圈子

搞台虚拟机玩玩!

有自信了,再战阿里!

用公司电脑访问奇怪的网站,被抓到了

公司用的技术不主流,想跑了...

简历共赏|一位不愿透露姓名的技术专家

继续滑动看下一个

MySQL 窗口函数是什么,有这么好用

向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存