scala - 在 Spark 中使用前导窗口函数时是否可以忽略空值

Question

我的数据框是这样的

id  value  date    
1   100    2017 
1   null   2016 
1   20     2015 
1   100    2014

我想获得最近的先前值但忽略 null

id  value  date   recent value
1   100    2017    20
1   null   2016    20
1   20     2015   100
1   100    2014   null

有没有办法在使用前导窗口函数时忽略空值。

Answer 1

Is it possible to ignore null values when using lead window function in Spark

它不是。

I would like to get most recent value but ignoring null

只需使用 last (或 first)与 ignoreNulls :

def last(columnName: String, ignoreNulls: Boolean): Column

Aggregate function: returns the last value of the column in a group.

The function by default returns the last values it sees. It will return the last non-null value it sees when ignoreNulls is set to true. If all values are null, then null is returned.

import org.apache.spark.sql.functions._
import org.apache.spark.sql.expressions._

val df = Seq(
  (1, Some(100), 2017), (1, None, 2016), (1, Some(20), 2015), 
  (1, Some(100), 2014)
).toDF("id", "value", "date")

df.withColumn(
  "last_value",
   last("value", true).over(Window.partitionBy("id").orderBy("date"))
).show

+---+-----+----+----------+                                                     
| id|value|date|last_value|
+---+-----+----+----------+
|  1|  100|2014|       100|
|  1|   20|2015|        20|
|  1| null|2016|        20|
|  1|  100|2017|       100|
+---+-----+----+----------+