Spark Scala:使用分析函数获取累积总和(运行总计) [英] Spark Scala : Getting Cumulative Sum (Running Total) Using Analytical Functions
本文介绍了Spark Scala:使用分析函数获取累积总和(运行总计)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用窗口函数在 Spark 中实现累积总和.但是在应用窗口分区函数时,记录输入的顺序没有保持
I am implementing the Cumulative Sum in Spark using Window Function. But the order of records input is not maintained while applying the window partition function
输入数据:
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|
+-------+--------+----+----------+-----------+
我已经尝试了下面的逻辑
I have tried the below logic
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 时,记录不按输入顺序计算.
for dept_no=10, the records are computed in expected order, whereas for dept_no=30, the records were not computed in the input order.
推荐答案
发生这种情况是因为类型不正确.因为薪水是一个string
This happens because of incorrect types. Because salary is a string
DS1.printSchema
root
|-- dept_no: string (nullable = true)
|-- emp_name: string (nullable = true)
|-- sal: string (nullable = true)
|-- date: string (nullable = true)
按字典顺序排序:
DS1.orderBy("sal").show
+-------+--------+----+----------+
|dept_no|emp_name| sal| date|
+-------+--------+----+----------+
| 30| Martin|1250|2017-11-21|
| 30| Ward|1250|2018-02-05|
| 10| MILLER|1300|2017-11-03|
| 10| Clark|2450| 2017-12-9|
| 10| King|5000|2018-01-28|
| 30| James| 950|2017-10-18|
+-------+--------+----+----------+
要获得所需的结果,您必须进行转换(并且不需要框架定义):
To get desired result you have to cast (and there is no need for frame definition):
DS1.withColumn("Dept_CumSal", sum("sal").over(
Window
.partitionBy("dept_no")
.orderBy(col("sal").cast("integer"), col("emp_name"), col("date").asc))).show
+-------+--------+----+----------+-----------+
|dept_no|emp_name| sal| date|Dept_CumSal|
+-------+--------+----+----------+-----------+
| 30| James| 950|2017-10-18| 950.0|
| 30| Martin|1250|2017-11-21| 2200.0|
| 30| Ward|1250|2018-02-05| 3450.0|
| 10| MILLER|1300|2017-11-03| 1300.0|
| 10| Clark|2450| 2017-12-9| 3750.0|
| 10| King|5000|2018-01-28| 8750.0|
+-------+--------+----+----------+-----------+
这篇关于Spark Scala:使用分析函数获取累积总和(运行总计)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文