sql sum求和 值小于不显示_SQL学习之旅-SQL高级功能

一、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）排列。

2) 窗口函数和group by分组功能的区别

group by分组汇总后改变了表的行数，一行只有一个类别；而partition by和rank函数不会减少原表中的行数。如下统计每个班级的人数。

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。

②　应用场景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作为窗口函数：

②　应用场景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 成绩表;

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输入参数：参数初始值在存储过程前被指定为默认值，在存储过程中修改该参数的值不能被返回。

②　Out输出参数：参数初始值在存储过程前为空，该值可在存储过程内部被改变，并可返回。

③　Inout输入输出参数：参数初始值在存储过程前被指定为默认值，可在存储过程中被改变，且在调用完毕后可被返回。

4、存储过程的注意事项

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

2) 定义不同的存储过程，需使用不同的存储过程名称，相同的存储过程名称会引起系统报错；

3) 存储过程和视图区别：视图只是一段固定的SQL语句，方便重复查询；存储过程约等于编程，可以实现复杂的操作，如传参、建表、写入、数据同步等过程。