MySQL窗口函数很有用

城里的月光
114 阅读4分钟

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;

这个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;

是不是看起来简洁明了多了？

现在让我们看看它提供了什么样的功能。

首先，创建一个包含三个字段的表：name、subject和score，用于演示功能。

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', 'Chemistry', 75);
INSERT INTO scores (name, subject, score) VALUES ('Student2', 'Biology', 92);
INSERT INTO scores (name, subject, score) VALUES ('Student3', 'Physics', 87);
INSERT INTO scores (name, subject, score) VALUES ('Student4', 'Mathematics', 68);
INSERT INTO scores (name, subject, score) VALUES ('Student5', 'English', 91);
INSERT INTO scores (name, subject, score) VALUES ('Student6', 'Chemistry', 58);
INSERT INTO scores (name, subject, score) VALUES ('Student7', 'Physics', 79);
INSERT INTO scores (name, subject, score) VALUES ('Student8', 'Mathematics', 90);
INSERT INTO scores (name, subject, score) VALUES ('Student9', 'Mathematics', 45);

什么是Window函数

在MySQL 8.x中，MySQL引入了窗口函数，这些函数在结果集中的指定范围内执行计算。

在使用Oracle和MS SQL时，窗口函数是常用的，但当我开始使用MySQL时，我发现MySQL没有窗口函数。这意味着对于复杂的统计查询，需要各种子查询和连接，导致SQL语句过于复杂，难以理解。只有一个词：困惑。

窗口函数的主要用途是用于统计和计算，如分组、排序和聚合查询结果。通过组合不同的函数，可以实现复杂的逻辑，并且与MySQL 8.0之前使用子查询和连接的方法相比，性能要好得多。

OVER（）

OVER()是一个用于定义窗口函数的子句，它只有在与其他函数（如sum或average）组合时才有意义。它指定计算的数据范围和排序方法。

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

PARTITION BY

这将指定分区字段并分析不同的分区。分区本质上是列，您可以指定一个或多个列。

ORDER BY

这将对每个分区中的记录进行排序。排序后，它可以与“范围和滑动窗口”一起使用。

范围和滑动窗口

这指定了分析函数的窗口，包括范围和滑动窗口。

范围窗口

这指定了窗口的开始和结束行号，使用UNBOUNDED PRECEDING表示开始，使用UNBOUNDED FOLLOWING表示结束。

例如：

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

这将计算当前行及其前后5行的工资总额。

滑动窗口

这将使用基于当前行的滑动窗口。

例如：

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

这将计算当前行及其前后两行的工资总额。

可以与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 `Highest Score in Subject` FROM scores;

其结果是：

2.获取每个主题的注册数量：

SELECT subject, score, count(name) OVER (PARTITION BY subject) as `Number of Registrations in Subject` FROM scores;

其结果是：

3.求出每个科目的总分：

SELECT subject, SUM(score) OVER (PARTITION BY subject) as `Total Score in Subject` FROM scores;

其结果是：

4.使用ORDER BY计算累积分数：

SELECT name, subject, score, SUM(score) OVER (ORDER BY score) as `Cumulative Score` FROM scores;

其结果是：

累积分数是通过将前几行的分数相加来计算的。

5.使用ORDER BY，范围为：

SELECT name, subject, score, SUM(score) OVER (ORDER BY score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as `Cumulative Score` FROM scores;

此累积分数是当前行、前一行和下一行的总和。

其结果是：

第一行的累积分数是当前行的分数加上下一行的分数（45 + 58 = 103）。第二行的累积分数是前一行、当前行和下一行分数的总和（45 + 58 + 68 = 171）。

使用排序函数

ROW_NUMBER（）

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

例如，将分数最高的分数排在第一位。如果两个人有相同的分数，他们仍然得到独特的排名。

SELECT name, subject, score, ROW_NUMBER() OVER (ORDER BY score DESC) as `Ranking` FROM scores;

如果不使用ROW_NUMBER()，在MySQL 5.7中，它看起来像这样：

SELECT s1.name, s1.subject, s1.score, COUNT(s2.score) + 1 AS `Ranking`
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;
avatar
文章
阅读
粉丝