课程思维导图:
![df628084ba916a8458eb0399ecea3dfe.png](https://i-blog.csdnimg.cn/blog_migrate/98dcf576aceaa9588d8ce317c9a178f8.jpeg)
1、什么是窗口函数?
OLAP函数-需要在每组内排名的业务需求
‹窗口函数› over (partition by ‹用于分组的列名›
order by ‹用于排序的列名›)
窗口函数›的位置,可以放以下两种函数:
1) 专用窗口函数,ank, dense_rank, row_number等。
2) 聚合函数,如sum. avg, count, max, min等
用在select子句中
2、如何使用窗口函数?
窗口函数有以下功能:
1)同时具有分组和排序的功能
2)不减少原表的行数
rank 窗口函数语句:
select *,rank() over (partition by 班级 order by 成绩 desc)
as rankingfrom 班级表
3、其他专用窗口函数
![feb5e9428ff9e0539a1ce95a1fd22cfa.png](https://i-blog.csdnimg.cn/blog_migrate/5ed574f1fa73ba874efa224d1d01a887.png)
rank函数:有并列名次的行,会占用下一名次的位置
dense_rank函数:有并列名次的行,不占用下一名次的位置
row_num函数:不考虑有并列名次
案例:经典排名问题
记住rank, dense_rank, row_number排名的区别:
dense的意思是紧密,也就是并列名次后,下一个名次应该是下一个连续的整数值。
![8e8c1283a460e992b61369dfbb8c5083.png](https://i-blog.csdnimg.cn/blog_migrate/08518dab867a2e1e87401d585cf6f162.jpeg)
sql语句:
select * dense_rank() over (partition by 班级 order by 成绩 desc)as ranking
from 班级表;
4、聚合函数作为窗口函数
聚合函数作为窗口函数,可以在每一行的数据里直观的看到,截止到本行数据,统计数据是多少(最大值、最小值等)。同时可以看出每一行数据,对整体统计数据的影响。
案例:累计求和问题
![37978efd5a14e52d1845e79fd4647eec.png](https://i-blog.csdnimg.cn/blog_migrate/36b9d9bd688ba75a6e56a8468ca66694.jpeg)
select 雇员编号,薪水,sum(薪水) over (order by 雇员编号) as 累计薪水
from 薪水表
where 结束日期='9999-01-01';
![1adfc2fa33df220daf60b1e1ca3a8208.png](https://i-blog.csdnimg.cn/blog_migrate/78c3cfa42daee24ac63aa7ac6f5b7c57.jpeg)
select 日期,确诊人数,sum(确诊人数) over (order by 日期) as 累计确诊人数
from 确诊人数表;
案例:如何在每个组里比较?
![078514995fa2734cc1bb43a45e0ecb0c.png](https://i-blog.csdnimg.cn/blog_migrate/220d9df7ac8e78dca7e6682c008b6010.jpeg)
select * from (select *,avg(成绩) over (partition by 科目) as avg_score
from 成绩表) as b where 成绩 › avg_score;
5、窗口函数的移动平均
功能:直观地查看到与相邻名次业绩的平均、求和等统计数据
rows n preceding:每一行得到的结果,都是当前行和前面n行的平均(共n+1行)
select * avg(成绩) over (order by 学号 rows 2 preceding) as current_avg from 班级表;
6、窗口函数总结
注意事项
- partition子句可是省略,省略就是不指定分组
- 窗口函数原则上只能写在select子句中
7、存储过程
使用存储过程需要:1)先定义存储过程 2)使用已经定义好的存储过程
(1)无参数的存储过程
定义存储过程的语法形式:
create procedure 存储过程名称() begin ‹sql语句› ; end;
(2)有参数的存储过程
create procedure 存储过程名称(参数1,参数2,...) begin ‹sql语句› ; end;
如:getNum是存储过程的名称,num表示参数名称,varchar(100)表示参数类型
create procedure getNum(num varchar(100)) begin select 姓名 from 学生表 where 学号=num; end;
(3)默认参数的存储过程
![6d6349e53d79e51bf259a2ce9a309301.png](https://i-blog.csdnimg.cn/blog_migrate/428f583ec473ef7544fefc71e59be9ee.jpeg)
![55f80059670a363927f7c753a6312ae5.png](https://i-blog.csdnimg.cn/blog_migrate/be221837eb5fef7bb2a3011778f86b54.jpeg)
![3d7a79bab32dfcb0a67babe296f14970.png](https://i-blog.csdnimg.cn/blog_migrate/2aef4452f9ad96e4680b9a29023f751f.jpeg)
(4)注意事项
1、sql语句,必须用“;”结尾
2、定义不同的存储过程,要使用不同的存储过程名称,相同的存储过程的名字会引起系统报错。
3、与视图的区别:视图只是一段固定的sql语句,方便重复查询;。存储过程约等于编程,可以实现复杂的操作,例如传参、建表、写入、数据同步等过程。
(5)存储过程的作用
业务比较复杂、重复性工作比较多,存储过程会比较实用