【案例实操】数据分析必知必会的窗口函数

什么是窗口函数

定义
理解窗口函数，顾名思义，这是一个可以在滑动窗口上实现各种统计操作的函数。一个滑动窗口是一个移动变化的小区间，所以窗口函数可以在不断变化的小区间里实现各种复杂的统计分析，统计的数据范围灵活可变，在日常的数据分析中，也是一种非常重要且常用的分析(OLAP)函数。

窗口函数vs.聚合函数
经过上面的描述，窗口函数好像和我们用的聚合函数sum、count、avg功能很像啊？聚合函数不也是在一个区间范围内实现各种不同的聚合操作么？
窗口函数与聚合函数的功能相似，两者都可以对指定数据窗口进行统计分析，但窗口函数与聚合函数又有所区别，窗口函数可以为每行数据进行一次计算，因为窗口函数指定了数据窗口大小，这个数据窗口大小可能会随着行的变化而滑动变化，可以在这个滑动窗口里进行计算并返回一个值。而聚合函数只返回一行，因为它只能对分组下的所有数据进行统计。

另外，在使用聚合函数时，与分组列无关的列不可以出现在SELECT关键字下，如果想要把除了分组列之外的其他明细数据和聚合值同时提取，聚合函数是实现不了的 ，而窗口函数就可以方便地实现这一点。比如我想在一行同时呈现某个产品的销量和所有产品的销量汇总sum，以便于计算某个产品占所有销量的百分比，这个时候窗口函数就可以非常完美地支持。
所以，综合以上的问题，相对于传统死板固定的聚合函数，以更灵活的设置小区间的方式来计算统计值的窗口函数应运而生。

窗口函数的语法
窗口函数出现在 SELECT 子句的表达式列表中，它最显著的特点就是 OVER 关键字。语法定义如下：
window_function (expression) OVER (
[ PARTITION BY part_list ]
[ ORDER BY order_list ]
[ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

其中包括以下选项：
1、PARTITION BY 表示将数据先按 part_list 进行分组, 如果不指定 PARTITION BY，则不对数据进行分组，换句话说，所有数据看作同一个分组，和聚合函数就很像了。
2、ORDER BY 表示将各个分组内的数据按 order_list 进行排序。一般情况下都要指定，如果不指定 ORDER BY，则不对各分区做排序，通常用于那些顺序无关的窗口函数，例如SUM

3、ROWS/RANGE BETWEEN表示窗口范围的定义，即：当前窗口包含哪些数据
ROWS 选择前/后几行，例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示当前行往前数3行到往后 3 行，一共 7 行数据（或小于 7 行，如果碰到了边界）。所以ROWS是通过排序后的前后位置选取窗口范围。
RANGE 选择数据范围，例如 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示选取取值在 [c−3,c+3]这个范围内的行，其中c 为当前行的值。所以RANGE是通过数值的大小选取窗口范围。
若不指定 ORDER BY，则默认使用PARTITION BY分组内所有行，即等价于：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。若指定了 ORDER BY，使用PARTITION BY分组内第一行到当前值，即等价于：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
参考下图看看ROWS 和 RANGE的区别，当前行val的值是2，ROWS BETWEEN 1 PRECEDING AND CURRENT ROW指定的窗口就是前一行和当前行，共两行；
RANGE BETWEEN 1 PRECEDING AND CURRENT