MySQL窗口函数怎么用

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

mysql

复制代码

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是干什么用的呢，就是为了计算一个成绩排名，简直大动干戈啊。

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

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

mysql

复制代码

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;

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

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

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

mysql

复制代码

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;

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

mysql

复制代码

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

mysql

复制代码

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

PARTITION BY

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

ORDER BY

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

范围和滚动窗口

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

范围窗口(Range window)

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

例如:

mysql

复制代码

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

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

滚动窗口(Row window)

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

例如:

mysql

复制代码

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列进行分区，并求出某学科的最大最小值

获取分数和此学科最高分

mysql

复制代码

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

得出的结果是:

subject	score	此学科最高分
化学	75	75
化学	58	75
数学	68	90
数学	90	90
数学	45	90
物理	87	87
物理	79	87
生物	92	92
英语	91	91

2、获取学科的报名人数

mysql

复制代码

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

得到的结果为:

subject	score	报名此学科人数
化学	75	2
化学	58	2
数学	68	3
数学	90	3
数学	45	3
物理	87	2
物理	79	2
生物	92	1
英语	91	1

3、求学科的总分

mysql

复制代码

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

得到的结果:

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

4、使用 order by 求累加分数

mysql

复制代码

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

得到的结果：

name	subject	score	累加分数
Student9	数学	45	45
Student6	化学	58	103
Student4	数学	68	171

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

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

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

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

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

5、使用 order by + 范围

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

mysql

复制代码

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

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

获取的结果为：

name	subject	score	累加分数
Student9	数学	45	103
Student6	化学	58	171
Student4	数学	68	201
Student1	化学	75	222
Student7	物理	79	241
Student3	物理	87	256
Student8	数学	90	268
Student5	英语	91	273

第一行 103，是当前行 45+后一行（58）的和，等于103，因为没有前一行。

第二行171，是当前行58+前一行（45）+后一行（68）的和，等于171。

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

搭配排序相关函数

ROW_NUMBER()

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

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

sql

复制代码

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

查询结果为：

name	subject	score	排名
Student2	生物	92	1
Student5	英语	91	2
Student8	数学	90	3
Student3	物理	87	4
Student7	物理	79	5

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

mysql

复制代码

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()函数在遇到相同值的行会将排名设置为相同的，就像是并列排名。

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

sql

复制代码

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

查询结果为：

name	subject	score	排名
Student1	化学	92	1
Student2	生物	92	1
Student5	英语	91	3
Student8	数学	90	4
Student3	物理	87	5

DENSE_RANK()

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

sql

复制代码

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

查询结果为：

name	subject	score	排名
Student1	化学	92	1
Student2	生物	92	1
Student5	英语	91	2
Student8	数学	90	3

配合其他窗口函数

NTILE()

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

sql

复制代码

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

查询结果为：

name	subject	score	组
Student1	化学	92	1
Student2	生物	92	1
Student5	英语	91	1
Student8	数学	90	2
Student3	物理	87	2
Student7	物理	79	3
Student4	数学	68	3
Student6	化学	58	4
Student9	数学	45	4

LAG()

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

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

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

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

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

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

sql

复制代码

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

得到的结果为：

name	subject	score	分值差
Student1	化学	92	0
Student2	生物	92	0
Student5	英语	91	1
Student8	数学	90	1
Student3	物理	87	3
Student7	物理	79	8
Student4	数学	68	11

LEAD()

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

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

mysql

复制代码

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