大数据之指标计算（7） -- 使用Spark根据dwd层fact_change_record表和dim_machine表统计，计算每个车间设备的月平均运行时长与所有设备的月平均运行时长对比结果

目录

前言

题目：

一、读题分析

二、处理过程

三、重难点分析

总结 

---

前言

本题来源于全国职业技能大赛之大数据技术赛项工业数据处理赛题 - 离线数据处理 - 指标计算

注：由于个人设备问题，代码执行结果以及最后数据显示结果将不会给出。

题目：

---

提示：以下是本篇文章正文内容，下面案例可供参考（使用Scala语言编写） 

一、读题分析

涉及组件：Scala，spark，hive，clickhouse

涉及知识点：

1. HiveSQL语法的使用
2. ...

---

二、处理过程

  

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

import java.util.Properties

object answer_No7 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("task7").master("local[*]").enableHiveSupport().getOrCreate()
    val df1 = spark.read.table("dwd.fact_change_record")
      .filter("ChangeRecordState !='运行'")
      .filter("ChangeEndTime is not null")
    val df2 = spark.read.table("dwd.dim_machine")

    val result = df1.join(df2, df1("ChangeMachineID") === df2("BaseMachineID"), "left")
      .withColumn("running_time", unix_timestamp(col("ChangeEndTime")) - unix_timestamp(col("ChangeStartTime")))
      .withColumn("start_month", date_format(to_date(col("ChangeStartTime")), "yyyy-MM"))
      .withColumnRenamed("MachineFactory", "machine_factory")
      .select("start_month", "machine_factory", "running_time")
      .groupBy(col("start_month"), col("machine_factory"))
      .agg(avg(col("running_time")).alias("factory_avg"))

    val companyAvg = result.agg(avg("factory_avg")).first().getDouble(0)
    val df = result.withColumn("comparison", when(
      col("factory_avg") > companyAvg, "高")
      .when(
        col("factory_avg") < companyAvg, "低")
      .otherwise("相同"))

      sendToClickHouse(resultDF,"shtd_industry.machine_running_compare","","root","")


  }
}

-- 代码仅供参考 -- 

关于sendToClickHouse函数怎么写，设备问题，在这里就不做展示了，可以参考我写的另外一篇文章

大数据之指标计算（2） -- 使用Spark根据dwd层fact_change_record表关联dim_machine表统计每个车间中所有设备运行时长...将计算结果存入clickhouse数据库

---

三、重难点分析

本题与大数据之指标计算（6） -- 编写Hive SQL代码，根据dwd层dwd.fact_environment_data表，统计检测设备的每月平均湿度与厂内检测结果做对比存入Mysql数据库中

类似。也是最后存入的数据库不同。

        本期为指标计算第7篇，后续应该还会出1篇。

---

总结 

这是一个关于使用Scala和Spark统计每个车间设备的月平均运行时长并与所有设备的月平均运行时长做比较，并将结果存储在clickhouse数据库中，然后在Linux命令行工具中查询前两条车间号倒序排列的数据的问题。下面是具体的要求和表结构：

• 在dwd层fact_change_record表和dim_machine表中关联，统计每个车间设备的月平均运行时长与所有设备的月平均运行时长对比结果，将无法参与计算的设备状态（change_end_time值为空）剔除。得到车间号（machine_factory）、月份（start_month）、车间平均时长（factory_avg）、所有设备平均时长（company_avg）和比较结果（comparison，取值为“高”、“低”或“相同”）字段。
• 将计算结果存储在clickhouse数据库shtd_store的表machine_running_compare中，表结构包含start_month、machine_factory、comparison、factory_avg和company_avg字段。
• 通过Linux的clickhouse命令行工具查询出前2条车间号倒序排列的数据，将SQL语句和执行结果截图粘贴到报告中。

注意：这个题目需要掌握Spark编程、clickhouse数据库以及Linux命令行工具的使用。

本篇是作者的大数据之指标计算系列之一：

        请关注我的大数据技术专栏大数据技术 作者: Eternity.Arrebol

        请关注我获取更多与大数据相关的文章Eternity.Arrebol的博客

Q-欢迎在评论区进行交流-Q