|
目录：1、窗口函数简介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 结束位置，这个参数仅针对滑动窗口函数有用，是在当前窗口下分出更小的子窗口。
其中起始位置和结束位置可写：
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()是排名函数mssql 函数，不需要参数，因为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)
（编辑：南平站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
