mysql表窗口固定_MySQL窗口函数

目录：窗口函数简介

语法简介

具体案例将每个班级中的成绩进行排名

对比窗口函数rank()、dense_rank()、row_number()的区别

聚合窗口函数

经典top N问题

在每个组里进行比较

窗口函数的移动

1、窗口函数简介MySQL从8.0开始支持开窗函数，这个功能在大多商业数据库中早已支持，也叫分析函数。

开窗函数与分组聚合比较像，分组聚合是通过制定字段将数据分成多份，每一份执行聚合函数，每份数据返回一条结果。

开窗函数也是通过指定字段将数据分成多份，也就是多个窗口，对每个窗口的每一行执行函数，每个窗口返回等行数的结果。

窗口函数分为静态窗口和滑动窗口，静态窗口的大小是固定的，滑动窗口的大小可以根据设置进行变化，在当前窗口下生成子窗口。

：

1、专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2、 聚合函数，如sum. avg, count, max, min等

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

2、语法简介

语法：

函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows/range between 起始位置 and 结束位置)

函数解读：

函数分为两个部分

第一部分是函数名称，开窗函数的数量较少，只有11个窗口函数+聚合函数(所有聚合函数都可以用作开窗函数)，根据函数性质，有的要写参数，有的不需要写参数；

第二部分是over语句，over()是必须要写的，里面有三个参数，都是非必须参数，根据需求选写：

1.第一个参数是 partition by +分组字段，将数据根据此字段分成多份，如果不加partition by参数，那会把整个数据当做一个窗口。

2.第二个参数是 order by +排序字段，每个窗口的数据要不要进行排序。

3.第三个参数 rows/range between 起始位置 and 结束位置，这个参数仅针对滑动窗口函数有用，是在当前窗口下分出更小的子窗口。

其中起始位置和结束位置可写：current row 边界是当前行

unbounded preceding 边界是分区中的第一行

unbounded following 边界是分区中的最后一行

expr preceding 边界是当前行减去expr的值

expr following 边界是当前行加上expr的值。rows是基于行数，range是基于值的大小，到讲解到滑动窗口函数时再详细介绍。

3、具体案例

1、创建一张班级表：mysql> create table class(id int,classname int,grade int);

2、插入数据：mysql> insert into class values(1,1,86),(2,1,95),(3,2,89),(4,1,83),(5,2,86),(6,3,92),(7,3,86),(8,1,88);基础数据示例1：将每个班级中的成绩进行排名

mysql> select *,rank() over (partition by classname order by grade desc) as ranking from class;已按照班级中的成绩进行排名

(如果我们不用窗口函数，直接用group by分组，则会改变行数；partition by分组的话行数不会改变。)

特点：

1、不减少原表的行数

2、同时具有分组和排序的功能示例2：对比窗口函数rank()、dense_rank()、row_number()的区别

mysql> select *,rank() over (order by grade desc) as ranking, dense_rank() over (order by grade desc) as dense_ranking, row_number() over (order by grade desc) as row_num from class;

代码解读：

rank()是排名函数，不需要参数，因为rank函数没有参数，但需要指定按照那个字段进行排名，所以使用rank函数必须用order by参数；

over语句里面没有partition by参数，也就是整个数据视为一个窗口；

当出现名次并列时，我们使用denserank()函数就可以让下一个人的名次是连续的。

row_number连续排名示例3：聚合窗口函数

mysql> select *,sum(grade) over (order by id) as current_sum,

-> avg(grade) over (order by id) as current_avg,

-> count(grade) over (order by id) as current_count,

-> max(grade) over (order by id) as current_max,

-> min(grade) over (order by id) as current_min

-> from class;示例4：经典top N问题

所有学生中取成绩排名前三的学生：

mysql> select a.classname,a.id,a.grade,a.rownum from (select id,classname,grade,row_number() over (order by grade desc) as rownum from class) as a inner join class as b on a.id=b.id where a.rownum<=3 order by a.rownum ;

+-----------+------+-------+--------+

| classname | id | grade | rownum |

+-----------+------+-------+--------+

| 1 | 2 | 95 | 1 |

| 3 | 6 | 92 | 2 |

| 2 | 3 | 89 | 3 |

+-----------+------+-------+--------+

3 rows in set (0.00 sec)

每门课程中取成绩排名前2的学生：

也就是每门课程中都要取出两名学生

mysql> select a.classname,a.id,a.grade,a.rownum from (select id,classname,grade,row_number() over (partition by classname order by grade desc) as rownum from class) as a inner join class as b on a.id=b.id where a.rownum<=2 order by a.classname ;

+-----------+------+-------+--------+

| classname | id | grade | rownum |

+-----------+------+-------+--------+

| 1 | 2 | 95 | 1 |

| 1 | 8 | 88 | 2 |

| 2 | 3 | 89 | 1 |

| 2 | 5 | 86 | 2 |

| 3 | 6 | 92 | 1 |

| 3 | 7 | 86 | 2 |

+-----------+------+-------+--------+

6 rows in set (0.00 sec)示例5：在每个组里进行比较

用窗口函数方法实现：

mysql> select * from (select a.*,avg(a.grade) over (partition by classname) as avg from class a inner join class x on x.id=a.id) q where q.grade >q.avg;

+------+-----------+-------+---------+

| id | classname | grade | avg |

+------+-----------+-------+---------+

| 2 | 1 | 95 | 88.0000 |

| 3 | 2 | 89 | 87.5000 |

| 6 | 3 | 92 | 89.0000 |

+------+-----------+-------+---------+

3 rows in set (0.00 sec)

用子查询方法实现：

mysql> select * from class a where grade > ( select avg(grade) from class b where b.classname=a.classname);

+------+-----------+-------+

| id | classname | grade |

+------+-----------+-------+

| 2 | 1 | 95 |

| 3 | 2 | 89 |

| 6 | 3 | 92 |

+------+-----------+-------+

3 rows in set (0.00 sec)示例6：窗口函数的移动

用rows和preceding这两个关键字是之前多少行的意思，也就是自身结果的之前两行的平均，一共三行做聚合函数

mysql> select *,avg(grade) over (order by id rows 2 preceding) as current_avg from class;

+------+-----------+-------+-------------+

| id | classname | grade | current_avg |

+------+-----------+-------+-------------+

| 1 | 1 | 86 | 86.0000 |

| 2 | 1 | 95 | 90.5000 |

| 3 | 2 | 89 | 90.0000 |

| 4 | 1 | 83 | 89.0000 |

| 5 | 2 | 86 | 86.0000 |

| 6 | 3 | 92 | 87.0000 |

| 7 | 3 | 86 | 88.0000 |

| 8 | 1 | 88 | 88.6667 |

+------+-----------+-------+-------------+

8 rows in set (0.00 sec)

自身加上前两行求和：

mysql> select *,sum(grade) over (order by id rows 2 preceding) as current_avg fro

m class;

+------+-----------+-------+-------------+

| id | classname | grade | current_avg |

+------+-----------+-------+-------------+

| 1 | 1 | 86 | 86 |

| 2 | 1 | 95 | 181 |

| 3 | 2 | 89 | 270 |

| 4 | 1 | 83 | 267 |

| 5 | 2 | 86 | 258 |

| 6 | 3 | 92 | 261 |

| 7 | 3 | 86 | 264 |

| 8 | 1 | 88 | 266 |

+------+-----------+-------+-------------+

8 rows in set (0.00 sec)