MySQL 窗口函数太好用了

公众号「古时的风筝」，专注于后端技术，尤其是 Java 及周边生态。

个人博客：www.moonkite.cn

大家好，我是风筝

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

得出的结果是:

2、获取学科的报名人数

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

得到的结果为:

3、求学科的总分

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

得到的结果:

4、使用 order by 求累加分数

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

得到的结果：

我们看这是怎么算出来的，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;

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

获取的结果为：

第一行 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;

查询结果为：

如果不用 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` desc) as `排名` FROM scores;

查询结果为：

DENSE_RANK()

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

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

查询结果为：

配合其他窗口函数

NTILE()

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

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

查询结果为：

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;

得到的结果为：

LEAD()

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

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

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

得到的结果：

推荐阅读

➿ 我的第一个 Chrome 插件上线了，欢迎试用！

➿ 什么是向量数据库

➿ 《轻解计算机网络》系列离线版 PDF 终于来了

➿ JDK21引入协程，再也不用为并发而头疼了