如何获得SparkSQL两个日期之间的几个月差异

Question

我遇到了错误：

org.apache.spark.sql.analysisexception: cannot resolve 'year'

我的输入数据：

1,2012-07-21,2014-04-09

我的代码：

val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.implicits._
import org.apache.spark.sql.SaveMode
import org.apache.spark.sql._
import org.apache.spark.sql.functions._
case class c (id:Int,start:String,end:String)
val c1 = sc.textFile("date.txt")
val c2 = c1.map(_.split(",")).map(r=>(c(r(0).toInt,r(1).toString,r(2).toString)))
val c3 = c2.toDF();
c3.registerTempTable("c4")
val r = sqlContext.sql("select id,datediff(year,to_date(end), to_date(start)) AS date from c4")

我该怎么办上述错误？

我尝试了以下代码，但是我几天就得到了输出，并且在几年中需要它

val r = sqlContext.sql("select id,datediff(to_date(end), to_date(start)) AS date from c4")

如果我可以使用任何功能，请告知我，例如to_date获得差异。

Answer 1

将字符串施放到Spark SQL中的数据类型并在列上应用SQL日期和时间函数的另一种简单方法，如以下内容：

import org.apache.spark.sql.types._
val c4 = c3.select(col("id"),col("start").cast(DateType),col("end").cast(DateType))
c4.withColumn("dateDifference", datediff(col("end"),col("start")))
  .withColumn("monthDifference", months_between(col("end"),col("start")))
  .withColumn("yearDifference", year(col("end"))-year(col("start")))
  .show()

Answer 2

上述答案之一不会在两个小于365的日期之间的天数返回正确的一年。以下示例提供了正确的年份和每月和年度的正确年份。

Seq(("2019-07-01"),("2019-06-24"),("2019-08-24"),("2018-12-23"),("2018-07-20")).toDF("startDate").select(
col("startDate"),current_date().as("endDate"))
.withColumn("datesDiff", datediff(col("endDate"),col("startDate")))
.withColumn("montsDiff", months_between(col("endDate"),col("startDate")))
.withColumn("montsDiff_round", round(months_between(col("endDate"),col("startDate")),2))
.withColumn("yearsDiff", months_between(col("endDate"),col("startDate"),true).divide(12))
.withColumn("yearsDiff_round", round(months_between(col("endDate"),col("startDate"),true).divide(12),2))
.show()

输出：

+----------+----------+---------+-----------+---------------+--------------------+---------------+
| startDate|   endDate|datesDiff|  montsDiff|montsDiff_round|           yearsDiff|yearsDiff_round|
+----------+----------+---------+-----------+---------------+--------------------+---------------+
|2019-07-01|2019-07-24|       23| 0.74193548|           0.74| 0.06182795666666666|           0.06|
|2019-06-24|2019-07-24|       30|        1.0|            1.0| 0.08333333333333333|           0.08|
|2019-08-24|2019-07-24|      -31|       -1.0|           -1.0|-0.08333333333333333|          -0.08|
|2018-12-23|2019-07-24|      213| 7.03225806|           7.03|         0.586021505|           0.59|
|2018-07-20|2019-07-24|      369|12.12903226|          12.13|  1.0107526883333333|           1.01|
+----------+----------+---------+-----------+---------------+--------------------+---------------+

您可以在URL下面找到一个完整的工作示例

https://sparkbyexamples.com/spark-calculate-difference-betney-two-dates-ind-ind-in-days-months-months and yars/

希望这会有所帮助。

快乐学习！

Answer 3

val r = sqlContext.sql("select id,datediff(year,to_date(end), to_date(start)) AS date from c4")

在上面的代码中，"年"不是数据框中的列，即它不是表" C4"中的有效列，这就是为什么抛出分析异常的原因，因为查询无效，查询找不到查找"年"专栏。

使用Spark用户定义的功能（UDF），这将是一种更强大的方法。

Answer 4

因为dateDiff仅返回几天之间的差异。我更喜欢使用自己的UDF。

import java.sql.Timestamp
import java.time.Instant
import java.time.temporal.ChronoUnit
import org.apache.spark.sql.functions.{udf, col}
import org.apache.spark.sql.DataFrame
def timeDiff(chronoUnit: ChronoUnit)(dateA: Timestamp, dateB: Timestamp): Long = {
    chronoUnit.between(
      Instant.ofEpochMilli(dateA.getTime),
      Instant.ofEpochMilli(dateB.getTime)
    )
}
def withTimeDiff(dateA: String, dateB: String, colName: String, chronoUnit: ChronoUnit)(df: DataFrame): DataFrame = {
    val timeDiffUDF = udf[Long, Timestamp, Timestamp](timeDiff(chronoUnit))
    df.withColumn(colName, timeDiffUDF(col(dateA), col(dateB)))
}

然后我称其为数据框转换。

df.transform(withTimeDiff("sleepTime", "wakeupTime", "minutes", ChronoUnit.MINUTES)