如何在Spark SQL中为每个组创建zscore

Question

我有一个像这样的数据帧

        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

现在我想创建第三列，其中包含每个TranAmount的z-score，这将是

^{pr2}$

这里的平均值和标准差将基于每个dSc的组

现在我可以在sparksql中计算平均值和标准差。在

(datafromdb
  .groupBy("dSc")
  .agg(datafromdb.dSc, func.avg("TranAmount") ,func.stddev_pop("TranAmount")))

但我不知道如何在数据帧中使用z分数来实现第三列。 如果能找到正确的方法，我将不胜感激

Answer 1

例如，您可以使用原始数据计算统计信息和join：

stats = (df.groupBy("dsc")
  .agg(
      func.stddev_pop("TranAmount").alias("sd"), 
      func.avg("TranAmount").alias("avg")))

df.join(broadcast(stats), ["dsc"])

(df
    .join(func.broadcast(stats), ["dsc"])
    .select("dsc", "TranAmount", (df.TranAmount - stats.avg) / stats.sd))

或使用窗口函数with standard deviation formula：

^{pr2}$