目录:窗口函数简介
语法简介
具体案例将每个班级中的成绩进行排名
对比窗口函数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)