SparkSQL窗口函数

SparkSQL窗口函数

MySQL参考

表达式：

function OVER (PARITION BY … ORDER BY … FRAME_TYPE BETWEEN … AND …)

窗口定义部分和窗口函数部分

(1)窗口定义部分（over后面的小括号中的内容）：

如果只写over，所有的数据都会被分到同一个窗口中去。

1.1 PARTITION BY 用来控制哪些行的数据会被分到同一个窗口中，Spark中同一个窗口中的数据会被放到同一台机器进行处理（PARTITION BY不是必须的）

1.2 ORDER BY 用来控制同一个窗口内的数据排序方式（ORDER BY不是必须的）

1.3 Frame 定义：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。主要用来控制每行数据在应用窗口函数时，这个窗口函数的作用范围。默认情况下，如果只进行了PARTITION BY，每行数据在应用窗口函数时，都能看到这个窗口中的所有数据；如果还使用了ORDER BY定义排序方式，那么此时每行数据在应用窗口函数时，可以看到排序后的第一条数据，直到当前数据所在的那一行。我们也可以通过手动指定的方式，来更加细粒度的控制每次调用窗口函数时的作用范围。（简单的说，就是每行数据，可以看到它所属于的窗口中的数据范围）

1.3.1 Row Frame方式：通过 “行号” 来表示（相对当前行）

rows between …T… and …T…
T 如下：
unbounded preceding 前面所有行
unbounded following 后面所有行
current row 当前行
n following 后面n行
n preceding 前面n行

rows between 例子
– 当前行与后面所有行的累加（分区内）
sum(sales_volume) over(partition by id rows between current row and unbounded following) as sum_sales
– 前面所有行与当前行的累加（分区内）
sum(sales_volume) over(partition by id rows between unbounded preceding and current row) as sum_sales
– 当前行与后两行的累加（分区内）
sum(sales_volume) over(partition by id rows between current row and 2 following) as sum_sales
– 前一行与当前行的累加（分区内）
sum(sales_volume) over(partition by id rows between 1 preceding and current row) as sum_sales
– 前一行的值+当前行的值+后一行的值
sum(id) over(partition by category rows between 1 preceding and 1 following) as rank

1.3.2 Range Frame方式：通过某一个列的差值来表示（很多时候跟RowFrame等同）

rows表示 行，就是前n行，后n行
range表示的是 具体的值，比这个值小n的行，比这个值大n的行

range between是以当前值为锚点进行计算

比如：
range between 4 preceding AND 7 following
表示：如果当前值为10的话就取前后的值在6到17之间的数据。

(2)窗口函数部分：

2.1 排名函数（以分数为例，假设5个学生分数分别为： 100，100，95， 80， 60）：
rank： 1，1，3，4，5 （行号会有空挡）
dense_rank：1，1，2，3，4 （行号没有空挡）
row_number：1，2，3，4，5（相同分数，排名也分先后)
2.2 分析函数
first_value:获取这个组第一条数据
last_value:获取这个组最后一条数据
lag: lag(field, n) 获取当前数据的 field 列向前 n 条数据
lead: lead(field, n) 获取当前数据的 field 列向后 n 条数据
2.3 聚合函数
所有的 functions 中的聚合函数都支持,包括 sum, count, avg, min, max等