一、什么是开窗函数

开窗函数/分析函数：over()

开窗函数也叫分析函数，有两类：

• 一类是聚合开窗函数

• 一类是排序开窗函数

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化！

开窗函数的调用格式为：

函数名(列名) OVER(partition by 列名 order by列名)

其中【partition by字段】和【order by 字段】是可选择的

• partition by 字段：子句进行分组

• order by 字段：子句进行排序

OVER()函数不能单独使用，必须跟在 排名函数（ ROW_NUMBER、DENSE_RANK、RANK、NTILE） 或 聚合函数（SUM、MAX、MIN、AVG、COUNT）

我们知道聚合函数对一组值执行计算并返回单一的值，如sum()，count()，max()，min()， avg()等，这些函数常与group by子句连用。除了 COUNT 以外，聚合函数忽略空值。

但有时候一组数据只返回一组值是不能满足业务逻辑需求，如各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。

开窗函数和聚合函数的区别：

（1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER关键字区分开窗函数和聚合函数。

（2）聚合函数每组只返回一个值，开窗函数每组可返回多个值。

开窗函数与聚合函数一样，也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。

注：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在2003年ISO SQL标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持， MYSQL 8.0后对开窗函数给予支持。

在ISO SQL规定了这样的函数为开窗函数，在 Oracle中则被称为分析函数，而在DB2中则被称为OLAP函数。

二、使用及说明

1. 分区排序：row_number () over()

2. 几个排序函数的区别

• row_number() over()、顺序排序

• rank() over()、跳跃排序

• dense_rank() over()、连续排序

• ntile() over(), 分组排名

（1） row_number() over()：

对相等的值不进行区分，其实就是行号，相等的值对应的排名不同，序号从1到n连续。

ROW_NUMBER() 的应用场景非常多，比如获取分组内排序第一的记录、获取一个session中的第一条refer等。

（2） rank() over()：

相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。

RANK（）为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。

（3） dense_rank() over()：

对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。

（4） ntile( n ) over()：

可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号，序号从1到n连续。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。

说明：DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。也就是说如果两个相同的行生成序号3，那么接下来生成的序号还是4。

3. 执行顺序

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行，晚于 where 、group by、 order by 的执行。

4. 其他开窗函数

1、row_number() over(partition by … order by …)

增加一列,类似与增加伪列

2、rank() over(partition by … order by …)

3、dense_rank() over(partition by … order by …)

rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。
dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。

4、count() over(partition by … order by …)

5、max() over(partition by … order by …)

6、min() over(partition by … order by …)

7、sum() over(partition by … order by …)

8、avg() over(partition by … order by …)

9、first_value() over(partition by … order by …)

10、last_value() over(partition by … order by …)

与函数的功能一致,只是是开窗函数

11、lag() over(partition by … order by …)

12、lead() over(partition by … order by …)

lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）

5. 开窗的窗口范围：

over（order by salary range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。