扫码加入数据分析学习群

开窗函数（分析函数）

2.7.1 特点：

• 开窗函数也就是在满足某种条件的记录集合上执行的特殊函数；
• 对于每条记录都要在此窗口内执行函数；
• 有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；
• 有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口；
• 开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。

2.7.2 开窗函数和普通聚合函数的区别

• 聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条
• 聚合函数也可以用于开窗函数中。

2.7.3 语法

开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<窗口分区>])

• partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
• order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
• frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

关于over的参数

• over参数为空，没有指定分区、排序、滑动窗口时，开窗函数将整个表作为一个区，默认计算的是所有值；
• over指定了分区，未指定排序和滑动窗口，开窗函数默认计算分区内的所有值；
• over指定了分区、排序，未指定滑动窗口，开窗函数默认计算的时第一行到当前行的值；

2.7.4 滑动窗口范围指定的两种方式

方式一：基于行

对于滑动窗口的范围指定，通常使用 between frame_start and frame_end 语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

current row     边界是当前行，一般和其他范围关键字一起使用 
unbounded preceding    边界是分区中的第一行 
unbounded following    边界是分区中的最后一行 
expr preceding    边界是当前行减去expr的值 
expr following    边界是当前行加上expr的值

移动平均可用来消除差额较大的情况，平滑数据

方式二：基于范围

和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：interval 7 day preceding。

2.7.5 开窗函数分类

• 动态窗口函数：first_value() / last_value()/nth_value()/聚合函数用于开窗

如没有指定排序和滑动窗口范围，默认计算的是分区内的所有记录。 指定分区和排序后，如没有指定滑动窗口范围，默认计算的是分区内的第一行到当前行。

• 静态窗口函数：row_number() / rank() / dense_rank()/percent_rank() / cume_dist()/lag() / lead()/ntile()

不管是否指定滑动窗口范围，窗口都是固定的，所以指定的滑动窗口范围无效。

2.7.6 按照函数功能不同，MySQL支持的开窗函数分为如下几类：

• 序号函数：row_number() / rank() / dense_rank()

函数名显示内容显示样式row_number()显示分区中不重复不间断的序号1，2，3，4...dense_rank()显示分区中重复不间断的序号1，1，2，3，3...rank()显示分区中重复间断的序号1，1，3，4...

• 分布函数：percent_rank() / cume_dist()

1. percent_rank()：和之前的rank()函数相关，每行计算：(rank - 1) / (rows - 1)

其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数。

rank() over(partition by user_no order by amount desc) rank1,
percent_rank() over(partition by user_no order by amount desc) percent

1. cume_dist()： 分组内小于等于当前rank值的行数/分组内总行数

示例：查询大于等于当前订单金额的订单比例

rank() over(partition by user_no order by amount desc) rank1,
cume_dist() over(partition by user_no order by amount desc) cume

• 前后函数：lag() / lead()

分区中位于当前行前n行（lag）/后n行(lead)的记录值
示例：查询上一个订单距离当前订单的间隔天数

lag(create_date,1) over(partition by user_no order by create_date) last_date

• 头尾函数：first_value() / last_value()

分区中的第一个/最后一个指定参数的值
示例：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额

first_value(amount) over(partition by user_no order by create_date) first_amount,
last_value(amount) over(partition by user_no order by create_date) last_amount

• 其他函数：nth_value() / nfile()

1. nth_value(expr,n)：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名
   示例：每个用户订单中显示金额排名第二和第三的订单金额

nth_value(amount,2) over(partition by user_no order by amount) second_amount,
nth_value(amount,3) over(partition by user_no order by amount) third_amount

2. nfile(n)：将分区中的有序数据分为n个桶，记录桶号
示例：将每个用户的订单按照订单金额分成3组

此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用nfile(n)对数据进行分组，由于记录数不一定被n整除，所以数据不一定完全平均，然后将不同桶号的数据再分配。