spark sql窗口大小设定：rowsBetween方法的使用

准备工作

• 准备依赖库

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.types._
import org.apache.spark.sql.functions._

• 准备数据

case class Salary(depName: String, empNo: Long, name: String, 
    salary: Long, hobby: Seq[String])
val empsalary = Seq(
  Salary("sales",     1,  "Alice",  5000, List("game",  "ski")),
  Salary("personnel", 2,  "Olivia", 3900, List("game",  "ski")),
  Salary("sales",     3,  "Ella",   4800, List("skate", "ski")),
  Salary("sales",     4,  "Ebba",   4800, List("game",  "ski")),
  Salary("personnel", 5,  "Lilly",  3500, List("climb", "ski")),
  Salary("develop",   7,  "Astrid", 4200, List("game",  "ski")),
  Salary("develop",   8,  "Saga",   6000, List("kajak", "ski")),
  Salary("develop",   9,  "Freja",  4500, List("game",  "kajak")),
  Salary("develop",   10, "Wilma",  5200, List("game",  "ski")),
  Salary("develop",   11, "Maja",   5200, List("game",  "farming"))).toDS
empsalary.createTempView("empsalary")
empsalary.show()

范围Frame
可以使用范围函数来改变Frame的边界。通过范围函数可以把计算（比如：sum,min,max,avg等操作）限定在一定的范围（基于当前行的向前或向后的条数）之内。我们先来看一个例子来直观的理解一下什么叫一定范围。

```正常的求和的例子```

val overCategory = Window.partitionBy('depName)
val df = empsalary.withColumn("salaries", collect_list('salary) over overCategory).
								withColumn("total_salary", sum('salary) over overCategory)

df.select("depName", "empNo", "name", "salary", "salaries", "total_salary").show(false)

+---------+-----+------+------+------------------------------+------------+     
|depName  |empNo|name  |salary|salaries                      |total_salary|
+---------+-----+------+------+------------------------------+------------+
|develop  |7    |Astrid|4200  |[4200, 6000, 4500, 5200, 5200]|25100       |
|develop  |8    |Saga  |6000  |[4200, 6000, 4500, 5200, 5200]|25100       |
|develop  |9    |Freja |4500  |[4200, 6000, 4500, 5200, 5200]|25100       |
|develop  |10   |Wilma |5200  |[4200, 6000, 4500, 5200, 5200]|25100       |
|develop  |11   |Maja  |5200  |[4200, 6000, 4500, 5200, 5200]|25100       |
|sales    |1    |Alice |5000  |[5000, 4800, 4800]            |14600       |
|sales    |3    |Ella  |4800  |[5000, 4800, 4800]            |14600       |
|sales    |4    |Ebba  |4800  |[5000, 4800, 4800]            |14600       |
|personnel|2    |Olivia|3900  |[3900, 3500]                  |7400        |
|personnel|5    |Lilly |3500  |[3900, 3500]                  |7400        |
+---------+-----+------+------+------------------------------+------------+

```添加了范围函数后的结果```

假设我们希望计算

val overCategory = Window.partitionBy('depName).rowsBetween(Window.currentRow, 1)
val df = empsalary.withColumn("salaries", collect_list('salary) over overCategory).
				withColumn("total_salary", sum('salary) over overCategory)

df.select("depName", "empNo", "name", "salary", "salaries", "total_salary").show(false)

通过这种方式得到的结果如下：

+---------+-----+------+------+------------+------------+                       
|depName  |empNo|name  |salary|salaries    |total_salary|
+---------+-----+------+------+------------+------------+
|develop  |7    |Astrid|4200  |[4200, 6000]|10200       |
|develop  |8    |Saga  |6000  |[6000, 4500]|10500       |
|develop  |9    |Freja |4500  |[4500, 5200]|9700        |
|develop  |10   |Wilma |5200  |[5200, 5200]|10400       |
|develop  |11   |Maja  |5200  |[5200]      |5200        |
|sales    |1    |Alice |5000  |[5000, 4800]|9800        |
|sales    |3    |Ella  |4800  |[4800, 4800]|9600        |
|sales    |4    |Ebba  |4800  |[4800]      |4800        |
|personnel|2    |Olivia|3900  |[3900, 3500]|7400        |
|personnel|5    |Lilly |3500  |[3500]      |3500        |
+---------+-----+------+------+------------+------------+

可以看到，通过使用范围函数：rowsBetween，把计算限定在一个范围之内。

这相当于一个滑动窗口，这种方式对于求时间序列窗口的数据统计非常实用。

范围Frame(Range Frane)的基本概念
Range Frame是基于与当前输入行的位置的逻辑偏移量，并且具有与ROW帧相似的语法。逻辑偏移是当前输入行的排序表达式的值与Frame的边界行的相同表达式的值之间的差。

由于此定义，当使用RANGE帧时，仅允许单个排序表达式。另外，对于RANGE帧，就边界计算而言，与当前输入行具有相同排序表达式值的所有行都被视为同一行。

使用范围Frame的步骤
使用范围Frame时需要按照以下步骤进行：

1、通过Window.partitionBy创建一个或多个列；
2、通常需要使用 orderBy来对数据进行排序；
3、进行以上两步后，接着再使用rangeBetweenorrowsBetween`函数
4、每一行都有一个对应的Frame
5、Frame的边界通过rangeBetween or rowsBetween进行控制
6、Aggregate/Window函数可以被用在每个row+frame上，来产生单一的值
范围函数
范围函数有两个，定义如下：

def rowsBetween( start: Long, end: Long): WindowSpec

def rangeBetween(start: Long, end: Long): WindowSpec

两个范围函数都接收两个参数，[start，end]包括边界在内。这两个参数，可以是以下的值：

• Window.unboundedPreceding
• Window.unboundedFollowing
• Window.currentRow
• 整数：相对于Window.currentRow的值，可以是负值或正值。

范围函数使用说明
rowsBetween函数

函数使用	说明
.rowsBetween(Window.currentRow, 1)	按相对于当前行的下1行进行计算，比如：sum，max等
.rowsBetween(Window.currentRow, 2)	按相对于当前行的下2行进行计算，比如：sum，max等
.rowsBetween(-1, Window.currentRow)	按相对于当前行的前一行进行计算
.rowsBetween(-2, Window.currentRow)	按相对于当前行的前两行进行计算
.rowsBetween(-1, 1)	按相对于当前行的前一行和 后一行进行计算
.rowsBetween(Window.unboundedPreceding, Window.currentRow)	计算基于当前行向前，分区内的所有行
.rowsBetween(Window.currentRow, Window.unboundedFollowing)	计算基于当前行向后，分区内的所有行
.rowsBetween(Window.unboundedPreceding, Window.unboundedFollowing)	窗口内所有的行

rangeBetween根据窗口中的行值获取帧边界。与rowsBetween比较的区别是，它与当前行的值进行比较。另外要注意，在比较时，若行的值相等，则会当成一样对待。

它的参数意义如下：

参数名	说明
Window.currentRow	该参数的值为：0
Window.unboundedPreceding	该参数的值为：LongMinValue
Window.unboundedFollowing	该参数的值为：LongMaxValue

使用举例：

使用例子	说明
rangeBetween(Window.currentRow,300)	从当前行开始往后的第1行，取值>300的第行。若当前行的值为：100，则取： >400的行
rangeBetween(-100,Window.currentRow)	从当前行开始往前，和第1行的值比较，若当前行的前1行的值比当前行的值小100，则取值
rangeBetween(Window.unboundedPreceding,200)	取：当前行后一行>200，当前行的前一行边界不限
rangeBetween(-100,Window.unboundedFollowing)	取：小于当前行值100的行，当前行的后面的行值不限
rangeBetween(-100,300)	若当前行的值为：100，则取：0<v<400的前一行和后一行

要注意的是：若比较的行的值相等，则会当成一行对待，会直接取该行

rangeBetween使用实战

val overCategory = Window.partitionBy('depName).orderBy("salary").rangeBetween(Window.currentRow, 300)
val df = empsalary.withColumn("salaries", collect_list('salary) over overCategory).
				withColumn("total_salary", sum('salary) over overCategory)

df.select("depName", "empNo", "name", "salary", "salaries", "total_salary").show(false)

输出结果：

+---------+-----+------+------+------------------+------------+
|depName  |empNo|name  |salary|salaries          |total_salary|
+---------+-----+------+------+------------------+------------+
|develop  |7    |Astrid|4200  |[4200, 4500]      |8700        |
|develop  |9    |Freja |4500  |[4500]            |4500        |
|develop  |10   |Wilma |5200  |[5200, 5200]      |10400       |
|develop  |11   |Maja  |5200  |[5200, 5200]      |10400       |
|develop  |8    |Saga  |6000  |[6000]            |6000        |
|sales    |3    |Ella  |4800  |[4800, 4800, 5000]|14600       |
|sales    |4    |Ebba  |4800  |[4800, 4800, 5000]|14600       |
|sales    |1    |Alice |5000  |[5000]            |5000        |
|personnel|5    |Lilly |3500  |[3500]            |3500        |
|personnel|2    |Olivia|3900  |[3900]            |3900        |
+---------+-----+------+------+------------------+------------+

注意：5200这个值，有相同的行，则当成一样的行对待。

小结
本文讲述了窗口函数的范围函数的使用。
————————————————
版权声明：本文为CSDN博主「一 铭」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zg_hover/article/details/109400563