1.应用场景
窗口函数解决的问题包括: 1)排名问题 2)top N问题 应用工作中, 面试中. |
2.学习/操作
前言
一.窗口函数有什么用?在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:
面对这类需求,就需要使用sql的高级功能窗口函数了。 二.什么是窗口函数?窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下:
那么语法中的<窗口函数>都有哪些呢?<窗口函数>的位置,可以分为以下两种函数:
因为窗口函数是对 where 或者 group by 子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。 三.如何使用?接下来,就结合实例,给大家介绍几种窗口函数的用法。 1.专用窗口函数rank例如下图,是班级表中的内容 如果我们想在每个班级内按成绩排名,得到下面的结果。 以班级“1”为例,这个班级的成绩“95”排在第1位,这个班级的“83”排在第4位。 上面这个结果确实按我们的要求在每个班级内,按成绩排名了。 得到上面结果的sql语句代码如下:
我们来解释下这个sql语句里的select子句。rank是排序的函数。 要求是“每个班级内按成绩排名”,这句话可以分为两部分:
通过下图,我们就可以理解partiition by(分组)和order by(在组内排序)的作用了。 窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。 那么,为什么还要用窗口函数呢?这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。 而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。 相信通过这个例子,你已经明白了这个窗口函数的使用:
现在我们说回来,为什么叫“窗口”函数呢?这是因为partition by分组后的结果称为“窗口”,这里的窗口不是我们家里的门窗,而是表示 "范围" 的意思。 简单来说,窗口函数有以下功能:
2.其他专业窗口函数专用窗口函数rank, dense_rank, row_number有什么区别呢?
得到结果: 从上面的结果可以看出:
这三个函数的区别如下: 最后,需要强调的一点是:
Note
3.聚合函数作为窗口函数聚和窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。 我们来看一下窗口函数是聚合函数时,会出来什么结果:
得到结果: 有发现什么吗?我单独用sum举个例子: 如上图,聚合函数sum在窗口函数中,是对自身记录、及位于自身记录以上的数据进行求和的结果。 比如0004号,在使用sum窗口函数后的结果,是对0001,0002,0003,0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。 不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算,现在再结合刚才得到的结果(下图),是不是理解起来容易多了? 比如0005号后面的聚合窗口函数结果是:学号0001~0005五人成绩的总和、平均、计数及最大最小值。 如果想要知道所有人成绩的总和、平均等聚合结果,看最后一行即可。 这样使用窗口函数有什么用呢?
四. 注意事项partition子句可是省略,省略就是不指定分组,结果如下,只是按成绩由高到低进行了排序:
得到结果: 但是,这就失去了窗口函数的功能,所以一般不要这么使用。 五. 总结1.窗口函数语法
<窗口函数>的位置,可以放以下两种函数:
2.窗口函数有以下功能:
3.注意事项
4.窗口函数使用场景1)业务需求“在每组内排名”,比如:
下一次会跟大家分享一些窗口函数的面试题,从而让各位在面试、工作中都能遇到这类问题,就想到哦,这用窗口函数就可以解决。 后续补充 .... |
3.问题/补充
TBD |
4.参考
后续补充
...