窗口函数介绍

窗口函数介绍

        窗口函数是用于分析的一类函数,是一种在数据库中执行聚合、分析和排序操作的高级工具。

        与常规的聚合函数不同的是,开窗计算出来的结果,并不会影响原有的查询,只是把查询出来的结果拼接在后面。就像开了一个窗户,在不影响房屋原有结构的基础上能从自己想要的角度观察内部关系。

先简单描述一下窗口函数的语法：

SELECT XX函数() OVER (

PARTITION BY 用于分组的列

ORDER BY 用于排序的列 ROWS/RANGE BETWEEN 开始位置 AND 结

束位置);

那么这个语法的每一个关键字都是什么作用呢？

SELECT不必多说，XX函数和OVER（）就意味着你调用了开窗函数。

PARTITION BY是用于分组的关键字，那么它跟GROUP BY有什么区别

PARTITION BY相较于GROUP BY，能在保留所有数据的基础上，只对其中部分字段做分组排序，但使用GROUP BY就只能保留参与分组的字段和聚合函数的结果。或者可以吧PARTITION BY 理解成是仅对数据进行一个逻辑上的区分,而非物理上的割裂。

开窗函数的ORDER BY与普通的ORDER BY并没有什么区别，但是当它跟聚合函数一起使用的时候，就会形成顺序聚合。

比如说SELECT ename，sal，SUM（sal） OVER（ORDER BY sal）FROM EMP；

这一句SQL的结果，SUM（sal）就会从上往下逐个求和。

        ROWS和RANGE两者在绝大部分情况下是能通用的，但是

若选定的窗口范围内排序不连贯,ROWS会按物理选择行数,RANGE会按排序的值进行选择

        比如5789四行按大小排序取第一个值与后面量行共三行的值的时候

        ROWS选择578

        RANGE选择57

这就是因为RANGE对行数的判断是基于逻辑判断，根据值进行区间划分；而ROWS对行数的判断基于物理实际行数判断，仅仅根据行号。

然后就是BETWEEN AND ，要讲这个就涉及到了移动窗口。

普通的窗口函数是划定了固定的范围进行判断计算，而移动窗口则会根据数据的前后重新分配窗口，每一行接受的数据都是不同的。

可以可以简单理解成，普通的情况下，分组了之后每一行下去，它的窗口都是同一个窗口。但是这个移动窗口，每到新的一行它的窗口都会包含新的内容，也去掉原有的一部分，所以每次参与计算的数据都是不同的。

这里对行的前前后后也有几个关键字。

CURRENT ROW 这个简单翻译过来就是当前行的意思；

PRECEDING 英语做动词译为”在...之前”，这是将计算结果的范围往前延伸，比如说ROWS 2 PRECEDING 就是将范围往前延伸两行，从当前行的前两行开始计算，计算到当前行。

相对的 FOLLOWING就是将范围往后延伸，比如说ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING 就是往后延伸两行，计算当前行与后面两行。

还有就是UNBOUNDED关键字,它与PRECEDING或者FOLLOWING组合,分别表示该窗口的第一行和最后一行。

有一个要补充的是，如果ORDERBY排序了后面却没有跟上窗口范围的话，会默认为从第一行到当前行；如果ORDERBY也没有，仅有OVER（）和前面的函数的话，那么范围则默认为从头到尾的所有数据。

接下来讲一下窗口函数的细分

窗口函数一般分为三类：聚合型窗口、分析型窗口和取值型窗口

聚合型窗口使用的函数就是我们都已经耳熟能详的那些，SUM求和、AVG平均值、COUNT技术、MAX取最大值、MIN取最小值。

分析型窗口函数则是计算每一行的数据在窗口内的排名和占比，比如说

RANK（）、DENSE_RANK()、ROW_NUMBER（）

这三者就是计算当前行在自己的组内的排名，但同样有所区别

比如说有一串数据降序排列出来是这样的

1000,900,900,900,800

那么，RANK 出来的结果是1、2、2、2、5

DENSE_RANK 出来的结果是1、2、2、2、3

ROW_NUMBER出来的结果则是1、2、3、4、5

这一下就看出来RANK的结果是结果是会在中间存在间断的，而且相同的值享受相同的序号，就像比赛的时候，几个人的成绩一样都是第二名，不代表他们下面的那个人就是第三。

ROW_NUMBER的结果则是不间断的，而且序号也不会重复，就像固定的行号排序那样。

DENSE_RANK的结果是不间断的，但相同值享受相同的序号，它更像RANK和ROW_NUMBER的混血，各取了一半

然后是一些其他的

PERCENT_RANK（），它计算小于当前行的值在所有行中的占比，类似于百分比排名，不过，它是通过(rank - 1) / (rows - 1)计算得出结果的

比如说在上面的这串数据里计算的话结果就是1，0.25，0.25，0.25,0

因为比900小的只有800，而它在5个数据中仅占1个，在总行数5去除掉当前行后就是1/4，为0.25。

下一个是CUME_DIST（），计算小于等于当前行的值在所有行中的占比

还是以上面的数据为例：1,0.8,0.8,0.8,0.2

900每一个都是0.8，因为CUME_DIST()是根据当前的值进行计算的,而900有3个,加上800一个就是4/5。

最后是NTILE（N），它会在排序后将数据按N的值切割分成N个同，并返回当前桶的序号，如果并不能均分，将会默认从前往后增加切片的分布。

然后是取值型窗口函数

LAG(列名, N, DEFAULT_VAL)，它会获取当前行往前N行的那一行的值，若没有获取到数据，就会输出DEFAULT_VAL，还是之前那串数据，如果使用LAG（num，2,1）

那么结果就是1,1,1000,900,900

相对的LEAD（列名，N，DEFAULT_VAL）就是往后N行

还有就是FIRST_VALUE（列名）和LASST_VAALUE（列名）

一个是获取分组内第一个值，一个是获取分组内最后一个值。

但是它们并不是真正意义上的第一个和最后一个，而是截至当前行的第一个和最后一个

按上面那串数据来说的话，结果分别是

1000,1000,1000,1000,1000

1000,900,900,900,800

目前学过的窗口函数主要就这些。