sql 聚合函数_SQL基础第五讲：分析函数之聚合和偏移运算

    咱们书接上回，上回说到关于给学生成绩排名的三个分析函数：

1.相同成绩不同编号：                  row_number()over() 1 2 3 42.相同成绩相同编号，按照顺序排名：     dense_rank()over()  1 2 2 33.相同成绩相同编号，下一位跨越排：      rank()over()       1 2 2 4

    一、这次咱们聊聊聚合函数在分析函数中的应用，聚合方式主要有以下五种：

max(字段)over() -- 求分组内最大值min(字段)over() -- 求分组内最小值sum(字段)over() -- 求分组内数据之和avg(字段)over() -- 求分组内平均值count(字段)over() -- 求分组内数据个数

    还是拿之前的表格举例，假如校长想看每个班级和年级的 最高分，最低分，平均分，学生个数，那么我们就可以用到以上函数进行计算：

select t.xh as 学号,t.bj as 班级,t.cj as 成绩,max(t.cj)over(partition by t.bj) as 班级最高分,max(t.cj)over() as 年级最高分,min(t.cj)over(partition by t.bj) as 班级最低分,min(t.cj)over() as 年级最低分,avg(t.cj)over(partition by t.bj) as 班级平均分,avg(t.cj)over() as 年级平均分,count(t.xh)over(partition by t.bj) as 班级人数,count(t.xh)over() as 年级人数from student t

查询结果如下：

    可以看到，非常清晰的将每个班级和年级的聚合分数计算出来了，学校老师也可以根据这些数据判断哪些班级的整体水平更高一些！

    这就是聚合函数在分析函数中的应用。

    二、下面我们看看分析函数如何处理位移数据

    在数据库中，有这样两个函数

lag(exp_str,offset,defval) over()--offset 是 exp_str 字段的偏移量，即 offset 为N ，  指的是在表中从当前行位置向前数N行就是我们所要找的那一行了。  defval是超出临界值以后的默认值，比如向前数n行后超出了表的取值范围，    则会返回这个默认值。如果没有设置这个值，则超出后返回值为空。lead(exp_str,offset,defval) over()--lead 函数与上面类似，只是这个函数是向后偏移

     直接看代码可能不是很好理解，接下来我还用学生成绩表来举例，我们先看下原始数据

    现在有这样一个需求，学生想知道自己距离班级和年级的前一名及后一名的差距是多少。

    在做这个需求之前我们首先要知道直接是得不到的这个差距，必须先将前一名及后一名的成绩放在与当前学生同一行才可以进行差值运算，在这里有一个特殊情况，就是最后一名的学生后面就没有人了，所以标记倒数第一，第一名的学生前面也没人，所以标记正数第一。

    接下来我们来使用偏移函数进行计算

select t.xh,t.bj,t.cj,lag(t.cj,1,999) over(partition by t.bj order by t.cj desc,t.xh asc)  as 班级前一名,case when lag(t.cj,1,999) over(partition by t.bj order by t.cj desc,t.xh asc) =999 then '班级第一'   else to_char(lag(t.cj,1,999) over(partition by t.bj order by t.cj desc,t.xh asc) -t.cj  ) end as 与班级前一名成绩差,    case when lag(t.cj,1,999) over( order by t.cj desc,t.xh asc) =999 then '年级第一'   else to_char(lag(t.cj,1,999) over( order by t.cj desc,t.xh asc) -t.cj  ) end as 与年级前一名成绩差,lag(t.cj,1,999) over( order by t.cj desc,t.xh asc)  as 年级前一名,lead(t.cj,1,-999) over(partition by t.bj order by t.cj desc,t.xh asc)  as 班级后一名,lead(t.cj,1,-999) over( order by t.cj desc,t.xh asc)  as 年级后一名,case when lead(t.cj,1,-999) over(partition by t.bj order by t.cj desc,t.xh asc) =-999 then '班级倒数第一'   else to_char(t.cj  -lead(t.cj,1,-999) over(partition by t.bj order by t.cj desc,t.xh asc)   ) end as 与班级后一名成绩差,    case when lead(t.cj,1,-999) over( order by t.cj desc,t.xh asc) =-999 then '年级倒数第一'   else to_char(t.cj  -lead(t.cj,1,-999) over( order by t.cj desc,t.xh asc)  ) end as 与年级后一名成绩差 from student t

    我们看下查询结果:

     可以看到已经显示出来前一名后一名的成绩以及相互之间的成绩差，其实这种计算在一些很多团队的比赛中经常使用，需要计算出与上一名的差距和下一名的差距，然后还会将自身的名次显示出来(ps：不会求自身名次的同学可以看下上一篇文章，有详细讲解)

    最后不知道大家发现一个问题没有，当有两名学生成绩相同时，比如 07 08 学生成绩都是140 距离上一名的成绩差应该相同，但是现在成绩差不同，这样肯定是不行的，大家可以思考一下有没有什么解决方案可以解决这个问题。有好的想法可以给我留言呦~

    下一节我会给大家讲解这种情况的处理方法(ps:这一章节内容比较多，大家好好消化一下，工作中用处很大呦~)。

to_char(列)  --此函数可以将其他格式的数据转化为字符型--因为计算出的成绩差是数字，但是显示第一名和最后一名的时候用的是字符，-- 所以将数字转化为字符，统一格式，不然数据库会报错case  when 条件1 then 结果1   when 条件2 then 结果2  else 其余结果 end --这是一个条件判断语句，当运行到这里时，会按照从条件1 到最后的顺序进行判断，-- 遇到成立的条件，立马返回结果，不会继续往下判断，如果所有条件都不符合，-- 则返回else后面的值，如果没有写else  则返回空值-- 这里是为了判断学生是不是第一名，当学生是第一名的时候，他前面就超出了界限-- 会得到我们定义的默认值，我们将默认值设置的比总成绩要大，-- 判断符合时可以返回此特殊情况，其他情况则为正常计算

  刚开始编写文章，很多地方都有不足，希望以后可以为大家贡献出更好的文章，谢谢大家！

    ps:还没有讲解如何安装数据库软件，我会在公众号的菜单栏中添加了安装教程供大家安装，谢谢，碰到安装上的问题也可以给作者发消息或者留言~~

                      感觉还不错的话，点下在看鼓励一下作者吧  

   没有关注的也可以关注下公众号~再次感谢