时间戳

首页 > 解决方案 > 如何查找并填充第三高金额并将相同的第三高金额填充到新列（Cut_of_3）

scala - 如何查找并填充第三高金额并将相同的第三高金额填充到新列（Cut_of_3）

问题描述

如何查找并填充第三高金额并将相同的第三高金额填充到 Cut_of_3（新列）中并将相同的内容重复到该相关 ID，如果该 ID 没有第三高金额，则需要将 100 填充到该相关ID。请找到样本数据集和预期结果。提前致谢。！

样本数据集：-

ID  Status       Date      Amount
1   New         01/05/20    20
1   Assigned    02/05/20    30
1   In-Progress 02/05/20    50
2   New         02/05/20    30
2   Removed     03/05/20    20
3   New         09/05/20    50
3   Assigned    09/05/20    20
3   In-Progress 10/05/20    30
3   Closed      10/05/20    10
4   New         10/05/20    20
4   Assigned    10/05/20    30

预期结果：-

ID  Status       Date      Amount  Cut_of_3
1   New         01/05/20    20        20
1   Assigned    02/05/20    30        20
1   In-Progress 02/05/20    50        20
2   New         02/05/20    30        100
2   Removed     03/05/20    20        100
3   New         09/05/20    50        35
3   Assigned    09/05/20    35        35
3   In-Progress 10/05/20    40        35
3   Closed      10/05/20    10        35
4   New         10/05/20    20        100
4   Assigned    10/05/20    30        100

标签： scalaapache-spark

解决方案

以下是使用Window函数实现的方法

val window = Window.partitionBy("ID").orderBy("ID")

// collect as list and sort descending and get the third value 
df.withColumn("Cut_of_3", sort_array(collect_list($"Amount").over(window), false)(2))
  // if if there is no third value it returns null and replace null with 100
  .na.fill(100, Seq("Cut_of_3"))
  .sort("ID")
  .show(false)

输出：

+---+-----------+--------+------+--------+
|ID |Status     |Date    |Amount|Cut_of_3|
+---+-----------+--------+------+--------+
|1  |New        |01/05/20|20    |20      |
|1  |Assigned   |02/05/20|30    |20      |
|1  |In-Progress|02/05/20|50    |20      |
|2  |New        |02/05/20|30    |100     |
|2  |Removed    |03/05/20|20    |100     |
|3  |New        |09/05/20|50    |20      |
|3  |Assigned   |09/05/20|20    |20      |
|3  |In-Progress|10/05/20|30    |20      |
|3  |Closed     |10/05/20|10    |20      |
|4  |New        |10/05/20|20    |100     |
|4  |Assigned   |10/05/20|30    |100     |
+---+-----------+--------+------+--------+

推荐阅读