sql 日期排序_（七）数据分析-sql高级功能

文章内容主要框架如下：

一、引入窗口函数

日常数据处理中，会有一些数据需求：按照业绩对员工进行排名；取出排名前N的员工进行奖励；此时单纯使用order by函数，并不能解决实际问题；以某司面试题为例：

图1.1

选取top1，有两个解决方案：

1.1 使用关联子查询：

图 1.2

1.2 使用窗口函数：‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)

图1.3

然而，如果需要取每个渠道的top10，用窗口函数，将n取值为10即可；（当然此处用union 函数处理也可以(手动狗头.jpg)麻烦程度可想而知）；

二、什么是窗口函数

2.1 定义：窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

2.2 基本语法：‹窗口函数› over (partition by ‹用于分组的列名›order by ‹用于排序的列名›)

2.3 有哪些<窗口函数>：

(1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

(2） 聚合函数，如sum. avg, count, max, min等

注意事项：因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

三、使用窗口函数

3.1 专用窗口函数作为窗口函数

3.1.1 如：rank

例如下左图，是班级表内容，如果想得到图右，各班以成绩进行排名：

图3.1 截图来源于猴子老师课程（知乎名称：猴子）

使用rank可以得出：

图3.2

（1）语句解析：

rank() over (partition by 班级 order by 成绩 desc)

运行逻辑：

① 分组：partition by 班级，是根据班级进行分组；

② order by 对班级内成绩进行排序，desc表示降序排列；

（2）区分 partition by 和group by

之前也学过group by进行分组汇总，但group by 分组汇总结果，对每一组汇总后只展示一行结果，但是partition by函数分组汇总，不减少原表的行数；

图3.3 截图来源于猴子老师课程（知乎名称：猴子）

综上，partition by分组后的结果称为“窗口”，窗口表示“范围”的意思。

3.1.2 简单来说，窗口函数有以下功能：

（1）同时具有分组和排序的功能

（2）不减少原表的行数

（3）语法如下：‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)

3.1.3 专用窗口函数区别

1、专用窗口函数rank, dense_rank, row_number区别是什么；

如果对图2.1左图，仅对成绩进行排名；

图3.4

结果：

图3.5 截图来源于猴子老师课程（知乎名称：猴子）

从上面的结果可以看出：

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

注意点：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以；

3.1.4 面试经典题——topN问题

如文章开头部分引入的topN问题，那么前面基础上，举一反三解决下面问题：

案例：现有“成绩表”，记录了每个学生各科的成绩。表内容如下。问题：查找每个学生成绩最高的2个科目；

图3.6 截图来源于猴子老师课程（知乎名称：猴子）

思路分析：

1、每个学生，那么先进行分组，用partition by；

2、科目成绩最高，进行排序，用order by；然后用desc进行降序；

3、选出最高的2个科目，需要进行排序，用row_number()，不考虑成绩并列；

图3.7

但是以上语句运行的结果只是展示出成绩排名，还需要选出topN，可以作为一个子查询，嵌套select语句选出topN；

图3.8

所以TopN 问题的处理模板：

图3.9

3.2 聚合函数作为窗口函数

3.2.1 常用的聚合函数

sum()，avg()，count()，max()，min()等聚合函数均可作为窗口函数；

聚和窗口函数和前面的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

比如，以下score表；有学号，课程号，成绩；

图3.10

(1) 用专用窗口函数，rank()排序：

图3.11

(2)用聚合函数 sum() 替换rank():

图3.12

当用sum()替换掉rank()，其实是对分组后的成绩，按照排序依次求和；

此处会有一个问题，我们发现，单纯的使用order by成绩，当每个组内的成绩相同时，无法累积求和；（此处和rank()类似，无法进行排序时，直接求和）解决方法如下：order by排序时，增加一个排序列；同时对成绩和学号进行排序，以解决此类问题；

图3.13

综上所述，聚合函数的运行结果：

图3.14 截图来源于猴子老师课程（知乎名称：猴子）

同理：avg()，count()，max()，min()依次求得结果是累计平均值，累计计数，累计最大值，累计最小值；

应用：聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等），同时可以看出每一行数据，对整体统计数据的影响。

3.2.2 聚合函数用途

1、“薪水表”中记录了员工发放的薪水。包含雇员编号，薪水、起始日期、结束日期。其中，薪水是指该雇员在起始日期到结束日期这段时间内的薪水。当前员工是指结束日期 = '9999-01-01'的员工（该公司业务：没有离职的员工，用这个值表示）。

图3.15 截图来源于猴子老师课程（知乎名称：猴子）

问题：按照雇员编号升序排列，查找当前员工薪水和当前员工的累计薪水。其中累计薪水是前N个当前员工( 结束日期 = '9999-01-01')的薪水的累计和，其他以此类推。

答案：用where定位当前员工的条件，然后用聚合函数求累计薪水；

图3.16

累计求和模板：

图3.17

2、举一反三

题目：下表为确诊人数表，包含日期和该日期对应的新增确诊人数

图3.18 截图来源于猴子老师课程（知乎名称：猴子）

按照日期进行升序排列，查找日期、确诊人数以及对应的累计确诊人数。

图3.19

3.3 窗口函数组合运用：

3.3.1 组内比较

1、现有“成绩表”，记录了每个学生各科的成绩。表内容如下：

图3.20 截图来源于猴子老师课程（知乎名称：猴子）

问题：查找单科成绩高于该科目平均成绩的学生名单

分析：先以科目分组，分别找出科目平均成绩；然后再用子查询进行筛选；

图3.21

3.3.2 移动平均

语句：<窗口函数> over （order by <排序列名> rows N preceding）；

1、比如用聚合函数avg的窗口函数举例说明：

select *,avg(成绩) over (order by 学号 rows 2 preceding) as current_avg from 班级表;

图3.22 截图来源于猴子老师课程（知乎名称：猴子）

用了rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及前2行的平均。

每一行得到的结果，都是当前行和前面2行的平均（共3行）。想要计算当前行与前n行（共n+1行）的平均时，只要调整rows…preceding中间的数字即可。

这里需要注意：在移动平均中，被选出的数据构成一个“框架”，例如，刚才例子中0002、0003、0004行数据，就是一个“框架”。

四、存储过程

4.1 什么是存储过程

把常用的SQL写好存储起来，这就是存储过程

4.2 如何使用存储

4.2.1 无参数存储

无参数语法形式：create procedure 存储过程名称() begin ‹sql语句› ; end（begin...end用于表示sql语句的开始和结束）

使用步骤：

（1）创建存储：create procedure 存储过程名称() begin ‹sql语句› ; end

（2）调用存储：call 存储过程名称();

示例：查出“学生表”里的学生姓名。sql语句是：select 姓名 from 学生表;

把这个sql语句放入存储过程的语法里，并给这个存储过程起个名字叫做就a_stuent1：

create procedure a_stuent1() begin select 姓名 from 学生表; end；

演示示例：创建存储：按性别汇总学生数：

图4.1

调用存储：

图4.2

4.2.2 有参数的存储过程

无参数存储过程名称后面是()，括号里面没有参数。当括号里面有参数时，就是下面的语法：

有参数语法形式：create procedure 存储过程名称(参数1,参数2,...) begin ‹sql语句› ; end；

使用步骤：

（1）创建存储：create procedure 存储过程名称(参数1,参数2,...) begin ‹sql语句› ; end；

（2）调用存储：call getNum(0001);

示例：在“学生表”里查找出指定学号的学生姓名。如果指定学号是0001，那么sql语句是：select 姓名 from 学生表 where 学号='0001';

如果需要更换查找学号；那么学号就是一个变量；因此，加入getNUM（）

创建存储：create procedure getNum(num varchar(100)) begin select 姓名 from 学生表 where 学号=num; end；

其中getNum是存储过程的名称，后面括号里面的num varchar(100)是参数，参数由2部分组成：参数名称是num；参数类型是是varchar(100)，这里表示是字符串类型。

调用：call getNum(0001);

演示示例：

创建查询：查询不同学号的选课情况；

图4.3

调用存储：

图4.4

4.2.3 默认参数的存储过程

1、存储过程还一种情况是有默认参数，是下面的语法：

（1）in 输入参数:参数初始值在存储过程前被指定为默认值，在存储过程中修改该参数的值不能被返回。

图4.5 截图来源于猴子老师课程（知乎名称：猴子）

（2）out输出参数:参数初始值为空，该值可在存储过程内部被改变，并可返回

图4.6 截图来源于猴子老师课程（知乎名称：猴子）

（3）inout输入输出参数：参数初始值在存储过程前被指定为默认值，并且可在存储过程中被改变和在调用完毕后可被返回

图4.7 截图来源于猴子老师课程（知乎名称：猴子）

4.2.4 有哪些注意事项

（1）定义存储过程语法里的SQL语句代码块必须是完整的sql语句，必须用“;”结尾

create procedure 存储过程名称(参数1,参数2,...) begin ‹sql语句› ; end；

（2）定义不同的存储过程，要使用不同的存储过程名称，相同的存储过程的名字会引起系统报错。

4.3 存储的作用

存储和视图的差异，存储过程约等于编程，可以实现复杂的操作，例如传参、建表、写入、数据同步等过程。

重复要做的事情整理成一步一步的业务步骤，然后把业务步骤写成sql语句，然后再把sql语句写到存储过程的语法里。视图只是一段固定的sql语句，方便重复查询;。