我将使用Window函数在Spark中实现累计求和。但是,在应用窗口分区函数时,记录输入顺序没有保持。
输入数据:
我尝试了以下逻辑。
对于dept_no=10,记录按预期顺序计算,而对于dept_no=30,记录没有按输入顺序计算。
输入数据:
val base = List(List("10", "MILLER", "1300", "2017-11-03"), List("10", "Clark", "2450", "2017-12-9"), List("10", "King", "5000", "2018-01-28"),
List("30", "James", "950", "2017-10-18"), List("30", "Martin", "1250", "2017-11-21"), List("30", "Ward", "1250", "2018-02-05"))
.map(row => (row(0), row(1), row(2), row(3)))
val DS1 = base.toDF("dept_no", "emp_name", "sal", "date")
DS1.show()
+-------+--------+----+----------+
|dept_no|emp_name| sal| date|
+-------+--------+----+----------+
| 10| MILLER|1300|2017-11-03|
| 10| Clark|2450| 2017-12-9|
| 10| King|5000|2018-01-28|
| 30| James| 950|2017-10-18|
| 30| Martin|1250|2017-11-21|
| 30| Ward|1250|2018-02-05|
+-------+--------+----+----------+
预期输出:
+-------+--------+----+----------+-----------+
|dept_no|emp_name| sal| date|Dept_CumSal|
+-------+--------+----+----------+-----------+
| 10| MILLER|1300|2017-11-03| 1300.0|
| 10| Clark|2450| 2017-12-9| 3750.0|
| 10| King|5000|2018-01-28| 8750.0|
| 30| James| 950|2017-10-18| 950.0|
| 30| Martin|1250|2017-11-21| 2200.0|
| 30| Ward|1250|2018-02-05| 3450.0|
+-------+--------+----+----------+-----------+
我尝试了以下逻辑。
val baseDepCumSal = DS1.withColumn("Dept_CumSal", sum("sal").over(Window.partitionBy("dept_no").
orderBy(col("sal"), col("emp_name"), col("date").asc).
rowsBetween(Long.MinValue, 0)
))
baseDepCumSal.orderBy("dept_no", "date").show
+-------+--------+----+----------+-----------+
|dept_no|emp_name| sal| date|Dept_CumSal|
+-------+--------+----+----------+-----------+
| 10| MILLER|1300|2017-11-03| 1300.0|
| 10| Clark|2450| 2017-12-9| 3750.0|
| 10| King|5000|2018-01-28| 8750.0|
| 30| James| 950|2017-10-18| 3450.0|
| 30| Martin|1250|2017-11-21| 1250.0|
| 30| Ward|1250|2018-02-05| 2500.0|
+-------+--------+----+----------+-----------+
对于dept_no=10,记录按预期顺序计算,而对于dept_no=30,记录没有按输入顺序计算。