Q:

combining lag with row computation in windowing apache spark

assume there is a dataframe as follows: 
 a| b| 
 1| 3| 
 1| 5| 
 2| 6|  
 2| 9| 
 2|14| 

I want to produce a final dataframe like this 
 a| b|  c
 1| 3|  0
 1| 5| -2
 2| 6| -6
 2| 9| -10
 2| 14| -17

The value of c is computed for every row except the first one as a-b+c for the previous row. I tried to use lag as well as rowsBetween, but no success Since "c" value does not exist and it is filled with random variable!!
val w = Window.partitionBy().orderBy($"a", $"b)
df.withColumn("c", lead($"a", 1, 0).over(w) - lead($"b", 1, 0).over(w) + lead($"c", 1, 0).over(w)) 

A:

You can't reference c while calculating c; What you need is a cumulative sum, which could simply be:
df.withColumn("c", sum(lag($"a" - $"b", 1, 0).over(w)).over(w)).show

+---+---+---+
|  a|  b|  c|
+---+---+---+
|  1|  3|  0|
|  1|  5| -2|
|  2|  6| -6|
|  2|  9|-10|
|  2| 14|-17|
+---+---+---+

But note this is inefficient due to the lack of the partition column.

