特定列的百分位数

Question

我有下面的数据。

scala> df.show
+---+------+---+
|  M|Amount| Id|
+---+------+---+
|  1|     5|  1|
|  1|    10|  2|
|  1|    15|  3|
|  1|    20|  4|
|  1|    25|  5|
|  1|    30|  6|
|  2|     2|  1|
|  2|     4|  2|
|  2|     6|  3|
|  2|     8|  4|
|  2|    10|  5|
|  2|    12|  6|
|  3|     1|  1|
|  3|     2|  2|
|  3|     3|  3|
|  3|     4|  4|
|  3|     5|  5|
|  3|     6|  6|
+---+------+---+

创建者

val df=Seq( (1,5,1), (1,10,2), (1,15,3), (1,20,4), (1,25,5), (1,30,6), (2,2,1), (2,4,2), (2,6,3), (2,8,4), (2,10,5), (2,12,6), (3,1,1), (3,2,2), (3,3,3), (3,4,4), (3,5,5), (3,6,6) ).toDF("M","Amount","Id")

在这里，我有一个基本列M，并根据 as 将其排序为ID。我试图计算百分位数，保持M为一个组，但对于最后的三个值。

我使用以下代码来为一个组找到百分位数。但我怎样才能瞄准最后三个值。？

 df.withColumn("percentile",percentile_approx(col("Amount") ,lit(.5)) over Window.partitionBy("M"))

预期产出

+---+------+---+-----------------------------------+
|  M|Amount| Id| percentile                        |
+---+------+---+-----------------------------------+
|  1|     5|  1| percentile(Amount) whose (Id-1)   |
|  1|    10|  2| percentile(Amount) whose (Id-1,2) |
|  1|    15|  3| percentile(Amount) whose (Id-1,3) |
|  1|    20|  4| percentile(Amount) whose (Id-2,4) |
|  1|    25|  5| percentile(Amount) whose (Id-3,5) |
|  1|    30|  6| percentile(Amount) whose (Id-4,6) |
|  2|     2|  1| percentile(Amount) whose (Id-1)   |
|  2|     4|  2| percentile(Amount) whose (Id-1,2) |
|  2|     6|  3| percentile(Amount) whose (Id-1,3) |
|  2|     8|  4| percentile(Amount) whose (Id-2,4) |
|  2|    10|  5| percentile(Amount) whose (Id-3,5) |
|  2|    12|  6| percentile(Amount) whose (Id-4,6) |
|  3|     1|  1| percentile(Amount) whose (Id-1)   |
|  3|     2|  2| percentile(Amount) whose (Id-1,2) |
|  3|     3|  3| percentile(Amount) whose (Id-1,3) |
|  3|     4|  4| percentile(Amount) whose (Id-2,4) |
|  3|     5|  5| percentile(Amount) whose (Id-3,5) |
|  3|     6|  6| percentile(Amount) whose (Id-4,6) |
+---+------+---+----------------------------------+

这对我来说似乎有点棘手，因为我仍然在从这里的发烧友那里学习spark.Expecting答案。

Answer 1

将orderBy("Amount")和rowsBetween(-2,0)添加到窗口定义将获得所需的结果：

• orderBy按数量对组内的行进行排序
• 计算百分位数时，rowsBetween只考虑当前行和前两行。

val w = Window.partitionBy("M").orderBy("Amount").rowsBetween(-2,0)

df.withColumn("percentile",PercentileApprox.percentile_approx(col("Amount") ,lit(.5))
      .over(w))
  .orderBy("M", "Amount") //not really required, just to make the output more readable
  .show()

版画

+---+------+---+----------+
|  M|Amount| Id|percentile|
+---+------+---+----------+
|  1|     5|  1|         5|
|  1|    10|  2|         5|
|  1|    15|  3|        10|
|  1|    20|  4|        15|
|  1|    25|  5|        20|
|  1|    30|  6|        25|
|  2|     2|  1|         2|
|  2|     4|  2|         2|
|  2|     6|  3|         4|
|  2|     8|  4|         6|
|  2|    10|  5|         8|
|  2|    12|  6|        10|
|  3|     1|  1|         1|
|  3|     2|  2|         1|
|  3|     3|  3|         2|
|  3|     4|  4|         3|
|  3|     5|  5|         4|
|  3|     6|  6|         5|
+---+------+---+----------+