Microsoft Business Intelligence (Data Tools)|Scala - How to Calculate Running Total Or Accumulative Sum in DataBricks

In this tutorial, you will learn "How to calculate Running Total Or Accumulative Sum by using Scala" in DataBricks.

Scala is a computer language that combines the object-oriented and functional programming paradigms. Martin Odersky invented it, and it was initially made available in 2003. "Scala" is an abbreviation for "scalable language," signifying the language's capacity to grow from simple scripts to complex systems.

Scala is a language designed to be productive, expressive, and compact that can be used for a variety of tasks, from large-scale corporate applications to scripting. It has become more well-liked in sectors like banking, where its robust type system and expressive syntax are very helpful.

To compute a running total in Scala using a DataFrame in Apache Spark, you can use the Window function along with sum aggregation.

To compute a running total within groups in a DataFrame using Scala and Apache Spark, you can still utilize the Window function, but you'll need to partition the data by the group column.

Steps to be followed -

💎 Import necessary classes and functions from Apache Spark.

// import libraries
import org.apache.spark.sql.{SparkSession, Row}
import org.apache.spark.sql.functions._ 
import org.apache.spark.sql.expressions.Window

💎 Create SparkSession

//create spark session
val spark =SparkSession.builder().appName("RunningToatl").getOrCreate()

💎 Read data from raw data file into Dataframe

//file path
val FilePath="dbfs:/FileStore/Demo/EmpDept.csv"

//read file into dataframe
val df=spark.read.option("header","true").csv(FilePath)

//show the schema of dataframe
df.printSchema()


//show the data from dataframe
df.show()

💎 Add the running total as a new column to the DataFrame using withColumn.

//calculate the running total
val df1=df.withColumn("RunningSalary",sum("Salary").over(Window.orderBy("EmpId")))

//display dataframe
df1.show()

💎Define a window specification using Window.partitionBy("group").orderBy("some_column"), where "group" is the column you want to partition your data by, and "some_column" is the column you want to order your data by within each partition.

//partition by department and order by empid
val windowSpec = Window.partitionBy("Department").orderBy("EmpId")

💎 Apply the sum aggregation function over the window using sum("value").over(windowSpec), where "value" is the column containing the numbers you want to compute the running total for.

// add specific variable to dataframe
val df2=df1.withColumn("DeptRunningSalary", sum("Salary").over(windowSpec)).sort("RunningSalary")

This code will compute the running total of the "value" column within each group specified by the "group" column, ordered by the "some_column" within each group.

💎 Adjust the orderBy and partitionBy clauses according to your DataFrame's schema and requirements.