icu.wuhufly.dws.machine_running_compare07.scala Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of shtd-bd Show documentation

bigdata source code for shtd

The newest version!

package icu.wuhufly.dws

import icu.wuhufly.utils.{CreateUtils, WriteUtils}
import org.apache.spark.SparkContext
import org.apache.spark.sql.{DataFrame, SparkSession}

object machine_running_compare07 {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = CreateUtils.getSpark()
    val sc: SparkContext = spark.sparkContext
    import spark.implicits._
    import org.apache.spark.sql.functions._

    spark.sql("use dwd")
    val df: DataFrame = spark.sql(
      """
        |select start_month, machine_factory,
        |  case when factory_avg > company_avg then '高'
        |    when factory_avg = company_avg then '相同'
        |    else '低' end as comparison,
        |    factory_avg, company_avg
        |from (
        |select distinct start_month, machine_factory,
        |  avg(duration) over(partition by machine_factory) as factory_avg,
        |  avg(duration) over() as company_avg
        |from (
        |select t2.MachineFactory as machine_factory,
        |   cast(to_timestamp(t1.ChangeEndTime, 'yyyy-MM-dd HH:mm:ss') as long) -
        |   cast(to_timestamp(t1.ChangeStartTime, 'yyyy-MM-dd HH:mm:ss') as long) as duration,
        |   date_format(to_timestamp(t1.ChangeStartTime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM') as start_month
        |  from fact_change_record t1
        |  join dim_machine t2
        |    on t1.ChangeMachineID = t2.BaseMachineID
        |  where ChangeRecordState='运行' and t1.ChangeEndTime is not null
        |) t1
        |) t1
        |""".stripMargin)

    WriteUtils.writeToCK(
      "machine_running_compare", df, "start_month"
    )

    sc.stop()
  }
}