MySQL数据库窗口函数应用

黄明辉

湖北三峡职业技术学院电子信息学院 湖北 宜昌 443000

引言

MySQL版本从8.0开始支持窗口函数，窗口函数有的也叫分析函数。

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口[1]。简单地说，窗口函数就是对于查询的每一行，都使用与该行相关的行进行计算。

1 窗口函数和普通聚合函数的区别

聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

聚合函数也可以用于窗口函数。在导入数据之前，Sqoop使用JDBC检查导入的数据表，检索出表的所有列以及列的SQL数据类型，并将这些SQL类型映射为Jave数据类型，在转换后的MapReduce应用中使用这些对应的Jave类型来保存字段的值，Sqoop的代码生成器使用这些信息来创建对应表的类，用于保存从表中抽取的记录[2]。

2 窗口函数应用特点

数据表，它代表各省中主要城市的国内生产总值（Gross Domestic Product，简称GDP）[3]。创建表gdp，表的数据如下表：

表1 gdp数据表

要求计算每个省的GDP总额、在全国的GDP总额，每个城市的GDP占所在省GDP中的比率，以及占总GDP中的比率。

方法一：使用传统函数来解决

步骤1：创建临时表t1，显示全国的GDP值。

步骤2：创建临时表t2，显示每个城市的GDP值。

步骤3：连接表t1与t2，显示出要的结果。

JOIN t2 ON g.province=t2.province－连接省统计结果临时表

JOIN t1－连接总计生产总值临时表

结果如下表。

表2 多表查询结果

方法二：应用窗口函数

结果如下表。

表3 窗口函数查询结果

应用窗口函数得到同样结果。

窗口函数应用，一步就能实现最终查询结果。因为没有用到临时表，不占用服务器的内存，提高了执行效率。对于这种需要用到分组统计的结果对每一条记录进行计算的情况下，使用窗口函数更能发挥出效果。

3 简单实用窗口函数应用

示例表如下：

表4 student数据表

表5 scoret数据表

表6 course数据表

3.1 求每门课程的成绩排名

结果如下表。

表7 成绩排名图

3.2 查询每位学生的成绩总分并排名

SELECT sid, SUM(grade) 总成绩, RANK() over(ORDER BY SUM(grade) DESC) 排名

FROM score

GROUP BY sid;

结果如下表。

表8 成绩总分排名图

3.3 查询每门功课成绩最好的前两名学生姓名及功课

结果如下表。

续表

表9 成绩最好的前两名结果图

4 结论

窗口函数有聚合、取值、排名、序列4种功能，其中聚合、取值、排名3种使用比较多，也容易理解，而使用序列的不太常用。