spark scala中的运行时间戳差异

Question

输入DF：

main_id sub_id time
 1 .     11 .  12:00
 1 .     12     1:00
 1 .     12 .   3:00
 1 .     12 .   5:00
 1 .     13 .   7:00
 1 .     13 .   8:00
 2 .     21 .  12:00
 2 .     21     5:00

我试图找到运行时间戳的差异就main_id

输出DF：

main_id  sub_id .  time    diff
  1 .      11 .    12:00    null
  1.       12 .    1:00 .    1
  1 .      12 .    3:00 .    2
  1 .      12 .    5:00 .    2
  1 .      13 .    7:00 .    2
  1 .      13 .    8:00 .    1
  2 .      21 .   12:00 .   null
  2 .      21 .    5:00 .    5

已尝试的代码：

val needed_window = Window.partitionBy($"main_id").orderBy($"main_id")
val diff_time = diff($"time").over(partitionWindow)
df.select($"*", diff_time as "time_diff").show

我得到的差异函数的错误，有一种方法来实现这一点。任何建议请。

Answer 1

假设您的time列的类型为Timestamp，您可以使用unix_Timestamp和滞后窗口函数计算当前行和前一行之间的time差。

import java.sql.Timestamp
import org.apache.spark.sql.functions._
import org.apache.spark.sql.expressions.Window

val df = Seq(
  (1, 11, Timestamp.valueOf("2018-06-01 12:00:00")),
  (1, 12, Timestamp.valueOf("2018-06-01 13:00:00")),
  (1, 12, Timestamp.valueOf("2018-06-01 15:00:00")),
  (1, 12, Timestamp.valueOf("2018-06-01 17:00:00")),
  (1, 13, Timestamp.valueOf("2018-06-01 19:00:00")),
  (1, 13, Timestamp.valueOf("2018-06-01 20:00:00")),
  (2, 21, Timestamp.valueOf("2018-06-01 12:00:00")),
  (2, 21, Timestamp.valueOf("2018-06-01 17:00:00"))
).toDF("main_id", "sub_id", "time")

val window = Window.partitionBy($"main_id").orderBy($"main_id")

df.withColumn("diff",
  (unix_timestamp($"time") - unix_timestamp(lag($"time", 1).over(window))) / 3600.0
).show
// +-------+------+-------------------+----+
// |main_id|sub_id|               time|diff|
// +-------+------+-------------------+----+
// |      1|    11|2018-06-01 12:00:00|null|
// |      1|    12|2018-06-01 13:00:00| 1.0|
// |      1|    12|2018-06-01 15:00:00| 2.0|
// |      1|    12|2018-06-01 17:00:00| 2.0|
// |      1|    13|2018-06-01 19:00:00| 2.0|
// |      1|    13|2018-06-01 20:00:00| 1.0|
// |      2|    21|2018-06-01 12:00:00|null|
// |      2|    21|2018-06-01 17:00:00| 5.0|
// +-------+------+-------------------+----+