title: Spark Scala: moving average for multiple columns
body: <p><strong>Input:</strong></p>
  
  <pre><code>val customers = sc.parallelize(List(("Alice", "2016-05-01", 50.00,4),
                                  ("Alice", "2016-05-03", 45.00,2),
                                  ("Alice", "2016-05-04", 55.00,4),
                                  ("Bob", "2016-05-01", 25.00,6),
                                  ("Bob", "2016-05-04", 29.00,7),
                                  ("Bob", "2016-05-06", 27.00,10))).
                             toDF("name", "date", "amountSpent","NumItems")
  </code></pre>
  
  <p><strong>Procedure:</strong> </p>
  
  <pre><code> // Import the window functions.
   import org.apache.spark.sql.expressions.Window
   import org.apache.spark.sql.functions._
  
   // Create a window spec.
   val wSpec1 = Window.partitionBy("name").orderBy("date").rowsBetween(-1, 1)
  </code></pre>
  
  <p>In this window spec, the data is partitioned by customer. Each customer’s data is ordered by date. And, the window frame is defined as starting from -1 (one row before the current row) and ending at 1 (one row after the current row), for a total of 3 rows in the sliding window. <strong>The problem is to take window-based summation for a list of columns</strong>. In this case, they're "amountSpent","NumItems". But the problem can have up to hundreds of columns. </p>
  
  <p>Below is the solution for doing window-based summation for each column. However, <strong>how to perform the summation more effectively?</strong> because we don't need to do find slided-window rows every time for each column. </p>
  
  <pre><code> // Calculate the sum of spent
   customers.withColumn("sumSpent",sum(customers("amountSpent")).over(wSpec1)).show()
  
    +-----+----------+-----------+--------+--------+
    | name|      date|amountSpent|NumItems|sumSpent|
    +-----+----------+-----------+--------+--------+
    |Alice|2016-05-01|       50.0|       4|    95.0|
    |Alice|2016-05-03|       45.0|       2|   150.0|
    |Alice|2016-05-04|       55.0|       4|   100.0|
    |  Bob|2016-05-01|       25.0|       6|    54.0|
    |  Bob|2016-05-04|       29.0|       7|    81.0|
    |  Bob|2016-05-06|       27.0|      10|    56.0|
    +-----+----------+-----------+--------+--------+
  
   // Calculate the sum of items
   customers.withColumn( "sumItems",
                  sum(customers("NumItems")).over(wSpec1)  ).show()
  
    +-----+----------+-----------+--------+--------+
    | name|      date|amountSpent|NumItems|sumItems|
    +-----+----------+-----------+--------+--------+
    |Alice|2016-05-01|       50.0|       4|       6|
    |Alice|2016-05-03|       45.0|       2|      10|
    |Alice|2016-05-04|       55.0|       4|       6|
    |  Bob|2016-05-01|       25.0|       6|      13|
    |  Bob|2016-05-04|       29.0|       7|      23|
    |  Bob|2016-05-06|       27.0|      10|      17|
    +-----+----------+-----------+--------+--------+
  </code></pre>