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

先看这段像天书一样的口函 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;1.2.3.4.5.6.7.8.9.10.11.12.13.

这段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;1.2.3.4.5.6.7.8.

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

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

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

复制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;1.2.3.4.5.

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

复制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);1.2.3.4.5.6.7.8.9.

##什么是窗口函数

在 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] )1.2.3.4.5.

PARTITION BY

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

ORDER BY

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

范围和滚动窗口

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

范围窗口(Range window)

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

例如:

复制SUM(salary) OVER (ORDER BY id RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING)1.2.

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

滚动窗口(Row window)

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

例如:

复制SUM(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)1.2.

这会计算当前行及之前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;1.

得出的结果是:

subject

score

此学科最高分

化学

75

75

化学

58

75

数学

68

90

数学

90

90

数学

45

90

物理

87

87

物理

79

87

生物

92

92

英语

91

91

2、获取学科的报名人数

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

得到的结果为:

subject

score

报名此学科人数

化学

75

2

化学

58

2

数学

68

3

数学

90

3

数学

45

3

物理

87

2

物理

79

2

生物

92

1

英语

91

1

3、求学科的总分

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

得到的结果:

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;1.

得到的结果：

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 行的累加，还可以限定范围。

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

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

获取的结果为：

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() 函数用于为结果集中的每一行分配一个唯一的排序。

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

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

查询结果为：

name

subject

score

排名

Student2

生物

92

1

Student5

英语

91

2

Student8

数学

90

3

Student3

物理

87

4

Student7

物理

79

5

如果不用 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;1.2.3.4.5.

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

RANK()

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

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

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

查询结果为：

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。

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

查询结果为：

name

subject

score

排名

Student1

化学

92

1

Student2

生物

92

1

Student5

英语

91

2

Student8

数学

90

3

配合其他窗口函数

NTILE()

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

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

查询结果为：

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：是可选值，如果向前偏移的行不存在，就取这个默认值。

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

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

得到的结果为：

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 行。

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

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

得到的结果：

name

subject

score

分值差

Student1

化学

92

0

Student2

生物

92

1

Student5

英语

91

1

Student8

数学

90

3

Student3

物理

87

8

Student7

物理

79

11

Student4

数学

68

10

本文地址：http://www.bhae.cn/html/096e7699827.html