mysql 窗口函数_MySQL 第七关窗口函数

1. 窗口函数，也叫OLAP函数（Online Analytical Processing, 联机分析处理），对数据库数据进行实时分析处理。

2. 窗口函数语法：

<

partition by 分组后的结果称为“窗口”，表示“范围”的意思

3. 窗口函数功能：

• 同时具有分组和排序的功能
• 不减少原表的行数

4. 窗口函数包含：

• 专用窗口函数，包括rank, denserank, row_number等
• 聚合函数，如sum, avg, count, max, min等

注意：窗口函数是对where 或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

5. 常见窗口函数的用法：

1）专用窗口函数rank

将班级表内学生成绩按班级依次排序：

select 

解读：

• partition by用来对表进行分组。这个例子中，我们按“班级”进行了分组 （partition by 班级）
• order by 子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中，我们按“成绩”进行排列，加了desc表示降序排列

窗口函数包含了group by 和order by的功能，但又有所不同:

• group by 分组后改变了表的行数：

• partition by和rank函数不会改变表的行数：

2）其他专用窗口函数：dense_rank 和 row_number:

与rank函数功能和语法类似，函数后的括号不需要任何参数，保持空值即可

select 

区别：

• rank函数：如果有并列次的行，会占用下一名次的位置。
• dense_rank函数：如果有并列出的行，不占用下一名次的位置
• row_number函数：不考虑并列次的情况

3) 聚合函数，用法与专用窗口函数相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面的括号不能为空，需要指定聚合的列名。

select 

注意：

• sum, avg, count, max, min 这些聚合函数是对自身记录、及位于自身记录以上的数据进行运算的结果。比如0007号后面的聚合窗口函数结果是，学号0001~0007七人成绩的总和、平均、计数及最大最小值。
• 可以在每一行的数据里直观得看到，截止到本行数据，统计数据是多少。同时可以看出每一行数据对整体统计数据的影响。

4）聚合函数的移动平均：对之前的几行进行运算得出结果

插入 rows~preceding 进行运算。

计算班级表中当行和前两行的平均成绩：

select 

rows~preceding表示“之间~行”的意思。想要计算当前行和前n行（共n+1行）的平均时，只要调整rows~preceding中间的数字即可。

适用于：在公司业绩名单排名中，可以通过移动平均，直观得查看到与相邻名次业绩的平均、求和等统计数据。

5. 面试问题解决：

常见的topN问题：分组取每组最大值、最小值，每组最大的N条（topN）记录。

案例：按课程号分组取成绩最大值所在行的数据

• 使用关联子查询：

select 

案例: 在成绩表中，查找每个学生成绩最高的两个科目。

• 使用窗口函数：为了不受并列成绩的影响，使用row_number专用窗口函数

select 

由此可以举一反三得出topN问题的模板：

select 

案例：在每个组里进行比较。

在成绩表中，查找单科成绩高于该科目平均成绩的学生名单

• 使用聚合窗口函数

select 

举一反三：查找每个组里大于平均值的数据，有两种方法：

• 使用窗口函数
• 使用关联子查询