一、SQL高级功能之窗口函数
1、什么是窗口函数
1) 定义:窗口函数也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
2) 基本语法:
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
3) 功能:
① 窗口函数同时具有分组和排序的功能
② 结果不减少原表的行数
③ 主要可用于解决排名问题(如每个部门按业绩排名)、topN问题(如找出每个部门排名前N名的员工进行奖励)、组内比较问题(如查找每个组里大于平均值的数据,亦可使用关联子查询解决)、累计求和问题以及求移动平均值问题等等。
2、如何使用窗口函数
1) 以一个例子说明窗口函数的使用,在一个班级表中有学号,班级,成绩三个字段,现要求在每个班级内按成绩排名,SQL语句如下:
select * rank() over (partition by 班级
order by 成绩 desc) as ranking
from 班级表;
其中rank就是用在窗口函数位置的排序函数。要求“每个班级内按成绩排名”,这里可分为两个部分:
① 每个班级内:按班级分组,partition by子句的功能是用来对表分组。
② 按成绩排名:order by子句的功能是对分组后的结果进行排序,默认是升序(asc)排列。在本例子中,是按降序(desc)排列。
![bf11c0f97cf6b4e3e31b98340627ec17.png](https://i-blog.csdnimg.cn/blog_migrate/ce927a706b3df6b0000eb09685c693e0.jpeg)
2) 窗口函数和group by分组功能的区别
group by分组汇总后改变了表的行数,一行只有一个类别;而partition by和rank函数不会减少原表中的行数。如下统计每个班级的人数。
![179d27b558dd5244a09fc84f2ebe47ef.png](https://i-blog.csdnimg.cn/blog_migrate/b6e83afb33ae1526260c4531a6593d06.png)
3、窗口函数的类别
1) 专用窗口函数用于对数据进行排名,有rank,dense_rank,row_number等
① rank,dense_rank,row_number的区别:
- Rank函数:排名时有并列名次的行,会占用下一名次的位置,如前三名是并列名次,排序结果是:1,1,1,4;
- Dense_rank:排名时有并列名次的行,不占用下一名次的位置,如前三名是并列名次,排序结果是:1,1,1,2;
- Row_number:不考虑并列名次的情况,如前三名是并列名次,排序结果是1,2,3,4。
![edac83c44f239601f8c6d64d3e854aad.png](https://i-blog.csdnimg.cn/blog_migrate/0f0269ed59e0614243eb07f85d27a0fe.jpeg)
② 应用场景1--经典排名问题:在班级表中有学号,班级,成绩三个字段,现要求按成绩排名
- 解题思路:
a. 涉及到排名问题,选择用窗口函数;
b. 排名可能会出现并列名次,选择名次并列时下一个名次是连续的整数值的排名函数dense_rank;
c. 按成绩排名是最高分为第一名,故选择按成绩从大到小降序(desc)排列。
- SQL语句:
select *,dense_rank() over (order by 成绩 desc) as dese_rank from 班级;
③ 应用场景2--经典topN问题:在成绩表中有姓名,科目,成绩三个字段,先要求查找每个学生成绩最高的2个科目
- 解题思路:
a. 每个学生最高的成绩:按姓名分组;
b. 成绩最高的2个科目:按成绩降序排列;
c. 既要分组又要排序,且不能减少原表的行数,选择用窗口函数;
d. 为了不受并列成绩的影响,选择使用row_number专用窗口函数;
e. 只要找出每个学生成绩最高的前2个科目,所以需要在用窗口函数查询出的结果中指定条件排名小于等于2。
- SQL语句:
select * from
(select *, row_number() over (partition by 姓名
order by 成绩 desc) as ranking
from 成绩表) as a
where ranking <= 2;
- 延伸topN问题SQL模板:
select * from
(select *,row_number() over (partition by <要分组的列名> order by <要排序的列名> desc) as ranking from <表名>) as a where ranking <= N;
2) 聚合函数用于计算截止到本行的数据,有sum,avg,count,max,min等
① 聚合函数作为窗口函数的作用:可以在每一行的数据里直观的看到,截止到本行的数据,统计数据是多少(求和,平均值,计数,最大值,最小值等);同时可以看出每一行数据对整体统计数据的影响。如下sum作为窗口函数:
![74710a8ab4aa25992413fc09005de3b5.png](https://i-blog.csdnimg.cn/blog_migrate/73f8674767d60c8b9fb980edca2b68a1.jpeg)
② 应用场景1--累计求和问题:确诊人数表中有日期和确诊人数两个字段,现要求按照日期进行升序排列,查找日期,确诊人数以及对应的累计确诊人数
- 解题思路:
a. 要求累计确诊人数,选择用sum作为窗口函数,需注意聚合函数用在窗口函数时,括号中需有统计的列名;
b. 要求按日期进行升序排列:order by 日期。
- SQL语句:
select 日期,确诊人数,
sum(确诊人数) over (order by 日期) as 累计确诊人数
from 确诊人数表;
③ 应用场景2--组内比较问题:成绩表中有姓名,科目,成绩三个字段,现要求查找单科成绩高于该科目平均成绩的学生名单
- 解题思路:
a. 科目平均成绩:按科目分组求平均值,选择用avg作为窗口函数,求出每个科目的平均成绩;
b. 单科成绩高于该科目平均成绩:是说单科成绩要和所在科目的平均成绩比较,即要求不能减少表的行数,且需要在窗口函数查找后的结果中指定条件-单科成绩>该科目平均成绩。
- SQL语句:
select * from
(select *,avg(成绩) over (partition by 科目) as avg_score from 成绩表) as a
where 成绩 > avg_score;
- 拓展--这题也可使用关联子查询求得,SQL语句如下:
select 姓名,科目,成绩 from 成绩表 a where 成绩 > (select avg(成绩) from 成绩表 b where b.科目 = a.科目 group by 科目);
④ 应用场景3--移动平均问题:成绩表中有学号,班级,成绩三个字段,现要求当前和前2位同学的平均成绩
- 解题思路:
a. 求平均成绩,选择用avg函数;
b. 因限定了范围-当前和前2位同学的平均成绩,选择用窗口函数,这里的窗口其实就是范围的意思,并用rows和preceding两个关键字(意思是“之前…行,包含当前行”)限定范围。
- SQL语句:
select *,
avg(成绩)over (order by 学号 rows 2 preceding) as current_avg
from 成绩表;
![e8ceb9dd428cacd68bb03b6d0b91e3b1.png](https://i-blog.csdnimg.cn/blog_migrate/636874e41e462ccf3954ac6c82fd181a.jpeg)
4、窗口函数的注意事项
1) 窗口函数原则上只能写在select子句中;
2) Partition by子句可以省略,结果不指定分组;
3) 专用窗口函数(rank,dense_rank,row_number)后的括号--“()”没有列名,且不能省略;聚合函数(sum,avg,count,max,min)括号中需有统计的列名。
二、SQL高级功能之存储过程
1、什么是存储过程
存储过程就是为以后使用而保存的一条或多条SQL语句。在工作中经常遇到重复性的工作,这时候可以把常用的SQL语句写好存储起来,这就是存储过程。
2、存储过程的作用
1) 可以简化复杂的操作;
2) 很大程度上保证了数据的一致性;
3) 提高了安全性和处理性能。
3、如何使用存储过程:使用存储过程需要①先定义存储过程;②使用已经定义好的存储过程
1) 无参数的存储过程
① 定义的语法形式:
Create procedure 存储过程名称() begin <sql语句>; end;
语法里的begin...end用于表示sql语句的开始和结束;语法里的<sql语句>就是重复使用的sql语句。
② 例子:定义一个存储过程a_student1,用于查找“学生表”里的学生姓名。Sql语句是:select 姓名 from 学生表;;存储过程定义语句是:create procedure a_student1() begin select 姓名 from 学生表; end;
③ 使用定义好的存储过程:定义好的存储过程可理解为一个函数,若在navicat中运行后会放置在f()函数项目下,使用时用call语句调取,语句如下:call 存储过程名称();
2) 有参数的存储过程
① 定义的语法形式:
Create procedure 存储过程名称(参数1,参数2,...) begin <sql语句> ; end;
② 例子:定义一个存储过程getNum,用于查找“学生表”里的指定学号的学生姓名。Sql语句是:select 姓名 from 学生表 where 学号=num;;存储过程定义语句是:create procedure getNum(num varchar(100)) begin select 姓名 from 学生表 where 学号=num; end;;其中getNum是存储过程的名称,后面括号里的num varchar(100)是参数,参数由2部分组成:参数名称是num;参数类型是varchar(100),这里表示的是字符串类型。
③ 使用定义好的存储过程,语句如下:call getNum(0001);
3) 默认参数的存储过程
① In输入参数:参数初始值在存储过程前被指定为默认值,在存储过程中修改该参数的值不能被返回。
![5a4dca91a6c92ea8908595a9935967ab.png](https://i-blog.csdnimg.cn/blog_migrate/3418f9ce009c127885a620e600d9179f.jpeg)
② Out输出参数:参数初始值在存储过程前为空,该值可在存储过程内部被改变,并可返回。
![af0064509034fde21c1eecc4c7245359.png](https://i-blog.csdnimg.cn/blog_migrate/4f4afedfcf63866509f0e313d24af520.jpeg)
③ Inout输入输出参数:参数初始值在存储过程前被指定为默认值,可在存储过程中被改变,且在调用完毕后可被返回。
![faad38821df0ddfb915acbf923d96c74.png](https://i-blog.csdnimg.cn/blog_migrate/55e2969c7a1d0c9ca3fa3a62d32a3ff5.jpeg)
4、存储过程的注意事项
1) 定义存储过程语法里的SQL语句代码块必须是完整的SQL语句,必须用“;”结尾;
2) 定义不同的存储过程,需使用不同的存储过程名称,相同的存储过程名称会引起系统报错;
3) 存储过程和视图区别:视图只是一段固定的SQL语句,方便重复查询;存储过程约等于编程,可以实现复杂的操作,如传参、建表、写入、数据同步等过程。