Spark中over partition窗口函数（开窗函数）

一、窗口函数

	定义：能为每行数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果给该行数据。

语法：select * ,函数(amount) over(窗口范围) total_amount from order_info;

1、 窗口范围分为：基于值和基于行两种（用between and指定范围）。

1-1、基于行：unbounded preceding从第一行开始（负无穷），[num] preceding从前num行开始，current row从当前行开始，[num] followwing后的num行开始；[num] preceding 前num行结束，current tow当前行结束，[num] following后num行结束，unbounded following至末尾结束（正无穷）；

1-2、基于值：order by 排序时只能用整数型，between [num1] preceding and [num2] preceding 基于范围时：取(当前值-num1到当前值+num2)

2、缺省

over()中的三个部分内容partition by 、 order by 、(row|range)between…and…均可省略不写。

2-1、partition by 省略不写

1. 表示不分区

2-2、order by 省略不写

1.表示不排序
2.通常rows方法都会用order by排序
3.当rows的范围是从第一行到最后一行，通常才会省略
4.当range方法没有用order by时，无法确定划分区域，后面的between...and无效，相当于从第一行到最后一行

2-3、（rows|range）between…and…省略不写

则使用其默认值，默认值下：
1、若over()包含order by ,则默认值为：
range between unbounded preceding and current row
2、 若over()中不包含order by，则默认值为
row between unbounded preceding and unbounded follwing

二、常用窗口函数

按照功能，常用窗口可划分为以下几类：聚合函数、跨行取值函数、排名函数。		

1、聚合函数

1-1、max：最大值

求指定范围窗口中的最大值

1-2、 min：最小值

求指定范围窗口中的最小值

1-3、 sum：求和

求指定范围窗口中的某字段相加之和

1-4、 avg：平均值

求指定范围窗口中的字段平均值

1-5、couont：计数

求指定范围窗口中的字段记录进行计数

2、跨行取值函数

2-1、lag和lead

注：lag和lead不支持指定自定义窗口
功能：获取当前行的上/下边某行、某字段的值
语法：lag/lead(字段名，num，默认值) over(范围)
例：lag(order_date,1,‘2003-08-21’) over(partition by usser_id order_date) last_date
根据用户id分区，订单日期升序排序，取前一条的订单日期，默认值为‘2003-08-21’重新命名为last_date

2-2、first_value和last_value

可以窗口自定义
功能：获取第一个值/最后一 个值
语法：first_value(字段名,布尔值) over(范围)
布尔值的值为true:不跳过Null值,直接返回
布尔类型的值为false:当第一条记录这个字段为Null时，往下一条记录继续找，找到为止

例：first_value(order_date,false) over(partition by user_id order by order_date) first_date
`根据用户id分区，订单日期升序排序，取第一行数据的订单日期字段，找不到则返回null`

5、排名函数

常用排名函数：rank()、dense_rank()、row_number()

功能：计算排名
语法：rank() over(窗口范围)
例：rank() over(partition by course order by score desc ) rk
根据课程分组，成绩升序排序，然后成绩排名

5-1、rank()

当遇到相同结果时，返回并列排序结果，第三位是3

5-2、dense_rank()

当遇到相同结果时，返回并列排序结果，第三位是2

5-3、row _number()

当遇到相同结果时，随机排出1，2，3

6、列表函数

常用与列表有关的窗口函数函数：collect_list()、concat_ws()

6-1、collect_list()

功能：将某个范围内容写进列表
语法1：collect_list(存入字段名) over(窗口范围)
例：collect_list(provinceids) over(partition by regionid) provinceids
根据地区id分组后，取出范围内的每个省份名称放到provinceids中

6-2、concat_ws()

功能：将列表中的数据用指定字符分隔提取出来
语法：concat_ws(分隔字符串,提取列表名)
例：concat_ws(“,”,provinceids) provinceids
将provinceids列表中的数据通过,分隔读取出来，放到provinceids字段中