一、什么是窗口函数?
窗口函数也叫OLAP函数(Online analytical processing),叫做联机分机处理。
窗口函数的基本语法
<窗口函数> over (partition by <列名> order by <列名>)as <名字>
from 表名
有两种窗口函数
1)专用窗口函数
rank, dense_rank, row_number
2)聚合函数
sum, avg, max, min.....
二、几种窗口函数的使用方法
- rank函数
对每个班级内的成绩进行排名
select*, rank() over (partition by 班级 order by 成绩 desc) as ranking
from 班级表
其中partition by是对班级进行分组,order by 表示对成绩进行排名。
group by 与 partition by 的区别
group by分组汇总后会改变行数,而partition by 不会改变行数,总行数不变。
2. dense_rank, row_number
select *
rank() over (partition by 班级 order by 成绩 desc) as ranking,
dense_rank() over (partition by 班级 order by 成绩 desc) as denserank,
row_number() over (partition by 班级 order by 成绩 desc) as rownumber
from 班级表;
上图代码得到
rank函数会将相同数值的行排在相同的排名,在出现下一数值后,排名是前面累积位数的后一位。
dense_rank函数会将相同数值的行排在相同的排名,但是出现下一数值后,排名时前一位排位后一位的自然数。
row_number函数会直接排列连续的自然数,没有重复出现。
三、topN问题
分组取最大值
select *
from score as a
where 成绩=(select max(成绩)
from score as b
where a.课程号=b.课程号);
使用关联子查询进行分组取最大值。同理,也可进行分组取最小值。
每组最大的N条记录
select *
from (select *, row_number() over (partition by 班级 order by 成绩 desc) as ranking
from score)
where ranking<=N;
四、聚合函数作为窗口函数
select *,
sum(成绩) over (order by 学号) as current_sum,
avg(成绩) over (order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count,
max(成绩) over (order by 学号) as current_max,
min(成绩) over (order by 学号) as current_min
from 班级表;
可以发现,使用聚合函数做窗口函数时,可以在每一行看到截止到这一行为止的统计值。这一功能帮助我们可以直观看到每一行数据对整体的影响。
五、窗口函数的移动平均
select *,
avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
from 班级表;
得到
rows 2 preceding 表示显示当前行加上前两行的统计数据,可以是求和、平均值、计数等。
功能:可以直观看出相邻名次间的统计数据。
六、存储过程
什么是存储过程?
存储过程可以将写好的sql语句保存下来,方便下次直接使用,不用重新写。
1)无参数的存储过程
语法形式
create procedure 名称() begin <SQL语句> ; end;
例子
create procedure a_stuent1() begin
select 姓名
from 学生表;
end;
要再次使用时,使用
call 名称();
2)有参数的存储过程
语法形式
create procedure 名称(参数1,参数2,.....) begin <SQL语句> ; end;
例子
create procedure getNum(num varchar(100)) begin
select 姓名
from 学生表
where 学号=num;
end;
要查询学号为0002的学生时 使用
call getNum(0002);
注意事项
1.存储的代码块必须是完整的,必须用“;”结尾。
2.定义不同的代码时,要使用不同的存储名称。