一. 窗口函数
1.应用场景
在实际工作中【组内排名】需要运用sql高级功能窗口函数。
排名问题：每个部门按业绩排名
TopN问题： 找出每个部门排名前N的员工。
2.窗口函数定义：
窗口函数（OLAP函数：online analytical processing）可以对数据库进行实时分析处理。
3.函数语法
<窗口函数> over (partition by <用于分组的列名>
order by <用于拍排序的列名>）
3.函数语法
<窗口函数> over (partition by <用于分组的列名>
order by <用于拍排序的列名>）
1）<窗口函数>的位置，可以存放2种函数：
- 专用窗口函数，包括rank, dense_rank, row_number等
- 聚合函数：如sum, avg,count,max,min等
2） 窗口函数原则上只能写在select子句中，因为窗 口函数是对where or group by子句处理后的结果进行操作。
二、窗口函数的使用
- rank函数 ：案例1：
- 目的：每个班级内按成绩排名
- 数据理解：现在的数据一共有3个字段，学号，班级和成绩
-
代码：select *, rank() over(partition by 班级 order by desc) as ranking from 班级表
按班级分组。partition by用来对表分组。这个例子中用班级分组。
按成绩排名：order by子句的功能是对分组后的结果进行排序，默认是升序（asc）。
2. partition by和group by的区别
- group by分组汇总后改变了表的行数，一行只有一个类别。
- partition by和rank函数不会减少原表中的行数
3. 总结
partition by 分组后的结果称为窗口，表示范围的意思。窗口函数功能如下：
- 同时具有分组和排序功能
- 不减少原表的行数
- 语法如下
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>）
三、其他专用窗口函数
- rank, dense_rank 与 row_number的含义
select *, rank() over(order by desc) as ranking,
select *, dense_rank() over( order by desc) as dese_rank,
select *, row_number() over( order by desc) as row_number from 班级表
- rank : 5,5,5,8。 有并列名次占用下一个名次的位置
- dense_rank: 5556. 有并列名词不占用下一名次的位置
- row_number:5678。不考虑并列名次的情况
- 上述函数后面的括号不需要任何参数。保持（）空着就可以
四、聚合函数作为窗口函数
- 聚合函数的语法
聚合函数的语法与专用窗口函数的语法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后括号不能为空，例如sum（成绩）。
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 班级表;
2. 聚合函数的理解
- sum聚合函数是对自身记录，及位于自身记录以上的数据进行求和的结果。例如0005号，在使用sum窗口函数后的结果，是对0001-00005号全部求和。
- 平均，计数，最大/最小值同上
3. 聚合函数的作用
- 可以在每一行的数据里直观的看到，截至到本行数据，统计数据是多少（最大值，最小值等）
- 可以看出每一行数据，对整体统计数据的影响
五、窗口函数的移动平均
- 代码理解
select *, avg(成绩) over (order by 学号 rows 2 preceding）as curent_avg from 班级表
- rows+num+preceding：rows和preceding从翻译角度是之前的行，中间num表示数字
- rows 2 preceding表示之前2行。即得到的结果是自身记录及前2行的平均。
- 例如0005号同学，即0003、0004和0005 三位同学的平均值
- 在移动平均中，被选出的数据构成了一个框架
2. 窗口函数作用
通过preceding关键字调整作用的范围，在以下场景非常使用
- 在公司业绩排名中，可以通过移动平均，直观的查看与相邻名次业绩的平均，求和等统计数据
- 在管理咨询当中可以通过这个窗口函数，进行集中度/市场占比的的计算
六、窗口函数总结
- 注意事项
partition子句可以省略，省略就是不指定分组。
2. 总结
1)窗口函数语法：
<窗口函数> over (partition by <用于分组的列名>
order by <用于拍排序的列名>）
2)<窗口函数>的位置，可以存放2种函数：
- 专用窗口函数，包括rank, dense_rank, row_number等
- 聚合函数：如sum, avg,count,max,min等
3) 功能：
- 同时具备分组和排序功能
- 不减少原表的行数，所以经常用来在每组内排名
4）窗口函数原则上只能写在select子句中
5）使用场景
- 经典topN问题：找出每个部门排名前N的员工进行奖励
- 经典排名问题：业务需求【在每组内排名】，比如，每个部门按业绩进行排名
- 在每个组里比较的问题：比如查找每个组大于平均值的数据，a)使用窗口函数案例 b）使用关联子查询
- 累计求和问题
案例1：面试经典排名问题
答案：
select *, dense_rank() over ( order by 成绩 desc）as dese_rank from 班级表
select score, dense_rank() over(order by score desc） as rank from scores案例2：面试经典TOPN问题
答案：
select*
from(
select * ,row_number() over (partition by 姓名order by 成绩 desc) as ranking
from 成绩表）as a
where ranking <=2;模板：
# topN 问题模板
select *，
from(
select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc）as ranking
from 表名） as a
where ranking<=N;案例3：累计求和问题
答案：
select 雇员编号，薪水，sum(薪水) over（order by 雇员编号） as 累计薪水
from 薪水表
where 结束日期=’9999-01-01‘案例4：如何在每个组里比较
答案：
select
from(
select * , avg(成绩）over （partition by 科目）as avgscore
from score ）as b
where 成绩>avg_score七、存储过程
1.存储过程是什么
在工作里遇到重复性的工作，把常用的sql写好存储起来，就是存储过程。遇到相同的问题，直接使用存储过程就好了，不用再重新写一遍sql。提高工作效率。
2. 如何使用存储过程
使用存储过程需要: a)先定义存储过程；b)使用已经定义好存储过程
1）无参数的存储过程
A. 语法形式：
create procedure 存储过程名称（） begin<sql语句>；
end；
语法里begin---end用于表示sql语句的开始和结束。语法里的【sql语句】就是重复使用的sql语句。
B. 案例
- 创建存储过程
- 使用存储过程
2）有参数的存储过程
A. 语法形式：
create procedure 存储过程名称（参数1,参数2,参数3---） begin<sql语句>；
end；
B. 案例(针对有条件的sql语句）
- 创建存储过程
其中getNum是存储过程名，其中num表示参数，而varchar（100）表示字符串类型；使用了where 学号=num就可以灵活的按照业务需求来查询。
- 使用存储过程
3）默认参数存储过程
- in 输入参数：参数初始值在存储过程前被指定为默认值，在存储过程中修改该参数的值不能被返回
out输出函数：参数初始值为空，该值可在存储过程内部被改变，并可返回
inout输入输出参数：参数初始值在存储过程前别指定为默认值，并且可在存储过程中被改变和在调用完毕后可被返回
3. 注意事项
- 定义存储过程语法里的sql语句代码是完整的，必须用[;]结尾
- 定义不同的存储过程，要使用不同的存储过程名，相同名字会报错
- 存储过程和视图的差别：视图只有固定的sql语句，方便重复查询；存储过程等于编程，可以是实现复杂的操作。
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
