简单易懂的SQL的窗口函数-案例清晰

窗口函数有啥用？

窗口函数是分析函数，聚合函数是统计函数。
普通常用的聚合函数只能返回一个值，而窗口函数每一行都能返回一个值。

举个例子：查看每一个班级的总分数。

首先表结构大概为这个样子：

普通的聚合函数实现为：

而窗口函数的实现为：


通过这两种情况的比较，可以看出来：

1. 普通的聚合函数侧重于统计出结果，假如你想分析一下前30名同学的总成绩的话是没有办法实现的，所以说普通函数最后生成一个值，也就是按照班级进行分组后取第一行。

2. 聚合函数侧重于对过程的分析，对总成绩的变化分析等等，也就是每一行会生成一个值，在这里就是对总成绩的累加。

窗口函数怎样写？（很简单哦）

窗口函数+over（分区+排序+范围）
例如：rank() over(partition by 班级 order by 合计) as 排名
解释：首先是你需要用的窗口函数，其次在over子句中进行动态的分区或者排序（分区就相当于group by），所以窗口函数由三部分组成：分区，排序和范围。
注意：分区、排序和范围为可选项，可以按照需求选择，而over子句一定要有，所以rank() over()也是正确的，对范围rows的介绍在聚合窗口函数部分着重介绍，像rank()这类的函数基本上不会用到rows，所以默认是表头到表尾，即整张表。

窗口函数有哪些？（也很简单哦）

1.排序函数

排序函数分为四种排序函数，分别是row_number()、rank()、dense_rank()、ntile()。
注意：over()里面的分组以及排序的执行晚于 where 、group by、 order by 的执行，同时要注意group by 只返回查询到的第一行，关于group by 和order by 一起使用的注意事项我会另外谈

• row_number()
  
  很容易看出规律吧，就是一直按照递增的顺序排下去。

• rank()
  
  从上图可以看出rank()函数并不是连续递增的，相同成绩的排名是一样的。

• dense_rank()
  
  从上图可以看出dense_rank()函数的排名是连续递增的，同时相同成绩的排名是一样的。

• dense_rank()
  
  
  从上面结果可以看出ntile()是将数据平均分成n个组，由于在over()子句中使用了分区，所以先分区，然后将每一个分区中的数据大致分成n个组（这个n个组是抽象的概念，是指窗口函数生成的数字，而paitition by是真正的进行分组处理，这里要清楚）

2.偏移函数

• first_value()和last_value()
  

从图中可以看出来，first_value()的结果值是不变的，为什么不变呢，因为结果集是遍历表的过程中一行一行添加的，所以第一个值是不变的，因为后面遍历的结果都是添加在第一行的下面的。但是last_value()的结果值就不同了，遍历的过程中每次添加一行，这一行就是最后一行，所以last_value表示的就是当前行的合计成绩，因为当前行就是当前结果集的最后一行。（从这一点也可以看出窗口函数是注重过程的，过程都会记录，方便分析使用，同时也能看出over子句动态分区排序的特点）

• lead()和lag()
  
  lead表示向下取值，参数表示向下取值的行数，lag表示向上取值。向上或者向下取值没有的话就是null。

3.聚合函数

大家对over子句中的partition by 和order by 都有了解了吧，在介绍聚合函数			前，先说一下语法的最后一个部分-rows。
格式为rows between···and···，也就是取一个范围，也就是所说的窗口。
当前行-current row
之前的行-preceding
之后的行-following
无界限-unbounded
表示从前面的起点-unbounded preceding
表示到后面的终点-unbounded following
不理解的话没关系，举几个例子就容易理解了。
取当前行和前五行：rows between 5 preceding and current row
取当前行和后五行：rows between current row and 5 following
取前五行和后五行：rows between 5 preceding and 5 folowing
	注意：在前面的行要写在前面，假如取当前行和前五行改一下：rows between current row and  5 preceding 这样写就是错误的。
下面介绍聚合函数。

• avg()
  计算本行以及前后相邻两行得合计成绩的平均值
  

• count()

• 按照班级进行分组，统计并列出每个班级中合计成绩>80的人数

• 

• sun()

• 按照班级分组，按照学号排序对学生的合计成绩进行累计求和
  （还是那句话，窗口函数是侧重过程的，侧重分析的，所以是对合计成绩的累加）

• max(),min()
  按照班级进行分组，统计每个班级的最高分和最低分
  （注意：求min最小值时不能desc逆序排序，因为窗口函数是注重过程的，当逆序排序的时候，会遍历表，判断当前行的合计成绩是不是当前行及之前行这个范围内的最小值，如果不是，那上一行一定是，拿过来，如果是，那就成为最小值填入当前行）
  

4.分布函数

• CUME_DIST函数
  累计分布函数：小于等于当前值的行数/分组内总行数（注意asc和desc）
  合计成绩大于等于当前成绩的人数占总学生人数的比例（注意，一定是大于等于，大于等于，光大于也不行）
  

• percent_rank函数
  当前行-1/总行数-1
  其实可以这样理解：从名字就可以看出是百分比排名函数，比如说你的成绩在你们班能排名百分之多少？（我怎么感觉跟cume_dist没什么区别）
  

• 最后，窗口函数基本上介绍完了，如有错误，欢迎指正！