Sorting in Spark SQL for the Month

问题:

I have a column as Month with Contents as

(Jan2016,Feb2016,Mar2016,Jun2016)

I'm trying to order it as

df.orderBy("Month")

But the month Col gets ordered as

Feb2016,Jan2016

in the alphabetical order, How can I order it by month?

回答1:

I refer the code of Antot.

    val monthWithIndex = Seq("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec").zipWithIndex.toMap

val monthSim = udf( (mon : String) => {
  monthWithIndex( mon.substring( 0, 3))
})
val df = session.sparkContext.parallelize( Seq("Jan2016","Feb2016","Mar2016","Jun2016")).toDF("Month")
df.withColumn("newMonth", monthSim($"Month")).orderBy("newMonth").drop("newMonth").show

If you wanto order by year and month, you can add the year column by above code.

回答2:

Parsing dates looks like a way to go:

import org.apache.spark.sql.functions.{to_date, month}

df.orderBy(month(to_date($"Month", "MMMyyy")))