开窗函数(分析函数)
2.7.1 特点：开窗函数也就是在满⾜某种条件的记录集合上执⾏的特殊函数；
对于每条记录都要在此窗⼝内执⾏函数；
有的函数随着记录不同，窗⼝⼤⼩都是固定的，这种属于静态窗⼝；
有的函数则相反，不同的记录对应着不同的窗⼝，这种动态变化的窗⼝叫滑动窗⼝；
开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每⼀⾏，都使⽤与该⾏相关的⾏进⾏计算并返回计算结果。
2.7.2 开窗函数和普通聚合函数的区别聚合函数是将多条记录聚合为⼀条；⽽开窗函数是每条记录都会执⾏，有⼏条记录执⾏完还是⼏条
聚合函数也可以⽤于开窗函数中。
两个分区由边界分隔，开窗函数在不同的分区内分别执⾏，在跨越分区边界时重新初始化。
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()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) percentcume_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()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组
rank函数怎么用此函数在数据分析中应⽤较多，⽐如由于数据量⼤，需要将数据平均分配到N个并⾏的进程分别计算，此时就可以⽤nfile(n)对数据进⾏分组，由于记录数不⼀定被n整除，所以数据不⼀定完全平均，然后将不同桶号的数据再分配。
发布评论