6 窗口函数

6.1 窗口函数概述

🆔 介绍：

MySQL 8.0新增窗口函数，又被称为开窗函数，与 Oracle 窗口函数类似，属于 MySQL 的一大特点；

非聚合窗口函数是相对于聚合函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

🍌 语法结构：

WINDOW_FUNCATION(EXPRESSION) OVER(
  PARTITION BY...
  ORDER BY...
  FRAME_CLAUSE
)

 其中，WINDOW_FUNCATION 是窗口函数的名称，EXPRESSION 是参数，OVER 子句包含三个选项：

1. 分区（PARTITION BY）

 PARTITION BY 选项用于将数据行拆分成多个分区（组）。如果省略了 PARTITION BY ，所有的数据将会作为一个组计算。

2. 排序（ORDER BY）

 ORDER BY 用于指定分区内的排序方式。

3. 窗口大小（FRAME_CLAUSE）

 FRAME_CLAUSE 用于在当前分区内指定一个计算窗口，也就是一个与当前计算行相关的数据子集。

6.2 序号函数

 序号函数有三种，分别为ROW_NUMBER()、RANK()、DENSE_RANK()，可以实现分组排序，并添加序号。

🍌 语法结构：

ROW_NUMBER|RANK()|DENSE_RANK() OVER(
  PARTITION BY ...
  ORDER BY ...
)

🐱 操作数据准备：

 先使用下面的 SQL 语句创建一个表，表中的数据如图所示。

create table student
(
    sid     int         null,
    name    varchar(20) null,
    gender  varchar(20) null,
    age     int         null,
    birth   date        null,
    address varchar(20) null,
    score   double      null
);

6.2.1 ROW_NUMBER()

🐰 下面这段代码中，实现了根据地点分组并按照成绩排序查询(逆序)的操作，该函数会自动标上序号ano：

SELECT name, address, score, ROW_NUMBER() OVER (PARTITION BY address ORDER BY score DESC ) AS ano
FROM student

观察上方结果，我们可以发现，ROW_NUMBER()函数在实现标注序号时并不会把相同排名的行标号为同一序号，如上图红圈中，乔巴和路飞均为85分，但是序号并不相同！

6.2.2 RANK()

🐯 在该函数的示例中，同样 实现了根据地点分组并按照成绩排序查询(逆序)的操作，该函数会自动标上序号ano：

SELECT name, address, score, RANK() OVER (PARTITION BY address ORDER BY score DESC ) AS ano
FROM student

与 ROW_NUMBER() 的标号方式不同的是，相同排名该函数会标注上相同序号！且序号可能不连续，如图中路飞、乔巴的序号均为1，而撒谎布为3

6.2.3 DENSE_RANK()

🐱 与 RANK() 函数不同的是，该函数在进行标号时 虽然也将相同排名的行标号为同一序号，但是后面的序号依然连续！

SELECT name, address, score, DENSE_RANK() OVER (PARTITION BY address ORDER BY score DESC ) AS ano
FROM student

 在上图中，路飞和乔巴的成绩相同，因此序号都为1。但是本应该排名为3的撒谎布被标号为了2！

6.3 开窗聚合函数

🆔 简介：

在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以 动态计算在指定窗口内的各种聚合函数的值。

🐰 操作示例：

 根据地点进行分组，使用SUM()计算总成绩，在该案例中，可以理解为根据地点分的每一组均为一个小窗口，而在这个窗口中计算了和：

SELECT name, address, score, SUM(score) OVER (PARTITION BY address) AS SUM
FROM student

需要注意的是，如果指定了排序方式，结果会有比较大的区别， 如下代码：

SELECT name, address, score, SUM(score) OVER (PARTITION BY address ORDER BY score) AS SUM
FROM student