title: How to create a z-score in Spark SQL for each group
body: <p>I have a dataframe which looks like this </p>
  
  <pre><code>        dSc     TranAmount
   1: 100021      79.64
   2: 100021      79.64
   3: 100021       0.16
   4: 100022      11.65
   5: 100022       0.36
   6: 100022       0.47
   7: 100025       0.17
   8: 100037       0.27
   9: 100056       0.27
  10: 100063       0.13
  11: 100079       0.13
  12: 100091       0.15
  13: 100101       0.22
  14: 100108       0.14
  15: 100109       0.04
  </code></pre>
  
  <p>Now I want to create a third column with the z-score of each <code>TranAmount</code> which will be </p>
  
  <pre><code>(TranAmount-mean(TranAmount))/StdDev(TranAmount)
  </code></pre>
  
  <p>here mean and standard deviation will be based on groups of each dSc</p>
  
  <p>Now I can calculate mean and standard deviation in Spark SQL.</p>
  
  <pre><code>(datafromdb
    .groupBy("dSc")
    .agg(datafromdb.dSc, func.avg("TranAmount") ,func.stddev_pop("TranAmount")))
  </code></pre>
  
  <p>but I am at a loss on how to achieve a third column with the z-score in the data frame.
  I would appreciate any pointer to the right way of achieving this/</p>