10月份给我最大的感觉是忙碌和崩溃吧，个人事务和工作事务夹杂在一起，让我觉得成年人的崩溃往往就在一瞬间！马上要从这个公司离职了，个人方面也马上要步入婚姻生活，希望以后的日子能够越来越好，无论是生活或者工作。
知识小汇：
一：什么是窗口函数
1.窗口函数有什么用？
在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：
排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励
面对这类需求，就需要使用sql的高级功能窗口函数了。
2.什么是窗口函数？
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下：
<窗口函数> over ( partition by<用于分组的列名> order by<用于排序的列名>)
‹窗口函数›的位置，可以放以下两种函数：
① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
二：如何使用窗口函数？
1.专用窗口函数rank
例如下图，是班级表中的内容
如果我们想在每个班级内按成绩排名，得到下面的结果。
以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名了。
得到上面结果的sql语句代码如下：
我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：
1）每个班级内：按班级分组
partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）
2）按成绩排名
order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。
窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。
简单来说，窗口函数有以下功能：
1）同时具有分组和排序的功能
2）不减少原表的行数
三.其他专用窗口函数
专用窗口函数rank, dense_rank, row_number有什么区别呢？
它们的区别我举个例子，你们一下就能看懂：
从上面的结果可以看出：
rank函数：如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。dense_rank函数：如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。row_number函数：不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。
最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。
A：面试经典排名问题
【题目】
下图是"成绩"表中的内容，编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
【本题考点】
1.考察如何使用窗口函数
2.专用窗口函数排名的区别：rank, dense_rank, row_number
【操作答案】
B：面试经典topN问题
【面试题类型】topN问题
工作中会经常遇到这样的业务问题：如何找到每个类别下用户最喜欢的产品是哪个？如果找到每个类别下用户点击最多的5个商品是什么？
这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。下面我们通过成绩表的例子来给出答案。成绩表是学生的成绩，里面有学号（学生的学号），课程号（学生选修课程的课程号），成绩（学生选修该课程取得的成绩）
- 分组取每组最大值
案例：按课程号分组取成绩最大值所在行的数据
我们可以使用分组（group by）和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。但是无法得到成绩最大值所在行的数据。
我们可以使用关联子查询来实现：
上面查询结果课程号“0001”有2行数据，是因为最大成绩80有2个
- 分组取每组最小值
案例：按课程号分组取成绩最小值所在行的数据
同样的使用关联子查询来实现
- 每组最大的N条记录
案例：现有“成绩表”，记录了每个学生各科的成绩，表内容如下。
问题：查找每个学生成绩最高的2个科目
【解题思路】
- 看到问题中要查“每个”学生最高的成绩。还记得我们之前课程里讲过的吗？当有“每个”出现的时候，就要想到是要分组了。这里是“每个学生”，结合表的结构，是按学生“姓名”来分组。
- 将表按学生姓名分组后，把成绩按降序排列，排在最前面的2个就是我们要找的“成绩最高的2个科目”。
- 现在分组后，需要排序，又不减少原表的行数，这种功能自然想到是窗口函数。
- 使用哪个专用窗口函数？为了不受并列成绩的影响，使用row_number专用窗口函数：row_number函数：也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。
【考核知识点】
- 主要考查对窗口函数的灵活使用。
- 在筛选过程中，非常容易因为子查询问题报错，本题也考察了对子查询的熟练运用。
- 本题间接考察了对sql语句执行顺序的熟悉程度。
【举一反三】
经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。
本题的sql语句修改下（将where字句里的条件修改成N），就可以成为这类问题的一个万能模板，遇到这类问题往里面套就可以了：
topN问题 sql模板 ：select * from (select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking from 表名) as a where ranking ‹= N;
四： 聚合函数作为窗口函数
聚合窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。
我们来看一下窗口函数是聚合函数时，会出来什么结果：
如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。
不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。
这样使用窗口函数的作用：
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。
C：累计求和问题
【题目】下表为确诊人数表，包含日期和该日期对应的新增确诊人数，按照日期进行升序排列，查找日期、确诊人数以及对应的累计确诊人数。
所以，我们可以得出“累计求和”问题的万能模板是：
select 列1,列2, sum(列名) over (order by ‹用于排序的列名›) as 累计值的别名
from 表名;
D案例：如何在每个组里比较？
【题目】现有“成绩表”，记录了每个学生各科的成绩。表内容如下：
问题：查找单科成绩高于该科目平均成绩的学生名单
【解题思路】
1."查找单科成绩高于该科目平均成绩"，也就是在“每个”科目里比较。还记得我们之前课程里讲过的吗？当有“每个”出现的时候，就要想到是要分组了。能实现“分组”功能的sql有两种，一是group by字句，另一个是窗口函数的partition by。
2.使用聚合窗口函数(求平均值avg)，将每门课的平均成绩求出以后，然后找出大于比平均成绩的数据。这就要求分组后不能减少表的行数。group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。所以这里我们使用使用窗口函数的partition by。
【本题考点】
- 主要考查对窗口函数的灵活使用。
- 在筛选过程中，非常容易因为子查询问题报错，本题也考察了对子查询的熟练运用。
- 本题间接考察了对sql语句执行顺序的熟悉程度。
【举一反三】
查找每个组里大于平均值的数据，可以有两种方法：
- 使用上面讲到的窗口函数来实现
- 使用关联子查询，可以回顾《从零学会sql：复杂查询》里的案例
五：窗口函数的移动平均
这里我们直接用聚合函数avg的窗口函数举例说明：
仔细看上面的窗口函数中，用了rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及前2行的平均。例如：学号0004学生的current_avg，是自己和前2位同学的平均，即0002，0003，0004三位同学成绩的平均，其他数据的情况也一样，下图非常直观的可以看到计算过程：
每一行得到的结果，都是当前行和前面2行的平均（共3行）。想要计算当前行与前n行（共n+1行）的平均时，只要调整rows…preceding中间的数字即可。
这里需要注意：在移动平均中，被选出的数据构成一个“框架”，例如，刚才例子中的0002、0003、0004行数据，就是一个“框架”。
这样使用窗口函数有什么用呢？
由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：
在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。
六：窗口函数总结
一.注意事项
partition子句可是省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序：
二.总结
1.窗口函数语法
‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)
‹窗口函数›的位置，可以放以下两种函数：
1） 专用窗口函数，比如rank, dense_rank, row_number等
2） 聚合函数，如sum. avg, count, max, min等
2.窗口函数有以下功能：
1）同时具有分组（partition by）和排序（order by）的功能
2）不减少原表的行数，所以经常用来在每组内排名
3.注意事项
窗口函数原则上只能写在select子句中
4.窗口函数使用场景
1）经典top N问题：找出每个部门排名前N的员工进行奖励
2）经典排名问题：业务需求“在每组内排名”，比如：每个部门按业绩来排名
3）在每个组里比较的问题：比如查找每个组里大于平均值的数据，可以有两种方法：
方法1，使用前面窗口函数案例来实现
方法2，使用关联子查询
4）累计求和问题
七： 存储过程
面试中有时候会问：存储过程有什么用？看了今天的知识，你就知道如何回答了。
1.存储过程是什么？
假如你每天要开车完成一些列重复的操作：车钥匙启动车，倒车。现在出现了一款新车，可以自动的完成这些重复的工作。每次你上车以后，车就自动帮助你倒出来了。同样的，在工作里也会经常遇到重复性的工作，这时候就可以把常用的SQL写好存储起来，这就是存储过程。这样下次遇到同样的问题，直接使用存储过程就可以了，就不需要再重新写一遍SQL了，这就极大的提高了工作效率。
2.如何使用存储过程？
使用存储过程需要：1）先定义存储过程
2）使用已经定义好的存储过程。
（1）无参数的存储过程
定义存储过程的语法形式：
create procedure 存储过程名称() begin ‹sql语句› ; end；
语法里的begin...end用于表示sql语句的开始和结束。语法里面的‹sql语句›就是重复使用的sql语句。下面通过一个例子看下如何使用。例如查出“各科成绩表”里的学生姓名。
sql语句是：
select 姓名 from 各科成绩表;
把这个sql语句放入存储过程的语法里，并给这个存储过程起个名字叫做就a_stuent1：
create procedure a_stuent1() begin select 姓名 from 各科成绩表; end；
在navicat里运行以后，建立的存储过程就会出现在下图的地方：
下次使用存储过程的用下面sql语句就可以，就不需要重新写一遍sql了。
call 存储过程名称();
（2）有参数的存储过程
前面的存储过程名称后面是()，括号里面没有参数。当括号里面有参数时，就是下面的语法：
create procedure 存储过程名称(参数1,参数2,...) begin ‹sql语句› ; end；
通过一个案例看下，现在要在“各科成绩表”里查找出指定学号的学生姓名。如果指定科目是语文，那么sql语句是：
select 姓名 from 各科成绩表 where 科目='语文';
现在问题来了，一开始不知道指定科目是哪一个，只有使用的时候才知道业务需求。比如今天要查找语文，明天要查找数学。这时候就需要用到参数，来灵活应对这种情况。把sql语句放入存储过程语法里就是：
create procedure getNum(name varchar(100)) begin select 姓名 from 各科成绩表 where 科目=name; end；
其中getNum是存储过程的名称，后面括号里面的name varchar(100)是参数，参数由2部分组成：参数名称是name；参数类型是是varchar(100)，这里表示是字符串类型。
存储过程里面的sql语句（where 科目=name）使用了这个参数name。这样在使用存储过程的时候，给定参数的值就可以灵活的按业务需求来查询了。
比如现在要查询科目=数学的学生姓名，那么就在使用存储过程的参数中给出科目的值，也就是下面括号里的数学：
（3）默认参数的存储过程
前面的存储过程名称后面是(参数1,参数2)，括号里面只包含参数的类型和名字，方便调用。存储过程还一种情况是有默认参数，是下面的语法。
in 输入参数:参数初始值在存储过程前被指定为默认值，在存储过程中修改该参数的值不能被返回。
out输出参数:参数初始值为空，该值可在存储过程内部被改变，并可返回
inout输入输出参数：参数初始值在存储过程前被指定为默认值，并且可在存储过程中被改变和在调用完毕后可被返回
3.有哪些注意事项
1）定义存储过程语法里的SQL语句代码块必须是完整的sql语句，必须用“;”结尾
create procedure 存储过程名称(参数1,参数2,...) begin ‹sql语句› ; end；
2）定义不同的存储过程，要使用不同的存储过程名称，相同的存储过程的名字会引起系统报错。
3）存储过程和视图在实际应用中有什么区别？目前我只进行过简单的操作感觉作用差不多，视图只是一段固定的sql语句，方便重复查询;。存储过程约等于编程，可以实现复杂的操作，例如传参、建表、写入、数据同步等过程。目前做到了解基本概念就可以，后期在工作中遇到实际业务需要，再跟进具体问题深入就可以了。
4.存储过程有什么用？
如果业务比较复杂、重复性工作比较多，存储过程会比较实用。
把重复要做的事情整理成一步一步的业务步骤，然后把业务步骤写成sql语句，然后再把sql语句写到存储过程的语法里。就像自动驾驶一样，把可能遇到的状况提前规划好，就不需要自己操纵方向盘，车子就按照我们写的步骤向前开了。
