一零一九、岗位数据分析（Spark）

目录

建表语句

原始数据

数据分析

完整代码

分析岗位数据如下要求：

分析不同学历的平均薪资（每个学历的平均估值（最高薪资平均值、最低薪资平均值求平均）

分析不同岗位的平均薪资（求每个学历的平均估值（最高薪资平均值、最低薪资平均值求平均）

分析各公司提供的岗位

建表语句

      DROP TABLE IF EXISTS `job`;
      CREATE TABLE `job` (
        `address` varchar(255) DEFAULT NULL,
        `company` varchar(255) DEFAULT NULL,
        `edu` varchar(255) DEFAULT NULL,
        `jobName` varchar(255) DEFAULT NULL,
        `salary` varchar(255) DEFAULT NULL,
        `size` varchar(255) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
 

原始数据

数据分析

1、获取原始数据

      val source: RDD[Row] = sprak.read
            .format("jdbc")
            .option("url", "jdbc:mysql://localhost:3306/crawler")
            .option("dbtable", "job")
            .option("user", "root")
            .option("password", "123456")
            .load()
            .rdd
          source.foreach(println)
  
 

 可见，题目要求为最低工资平均值 与 最高工资平均值，先对数据格式进行处理一下

 2、格式化数据

      val data: RDD[(String, String, String, String, Float, Float, String)] = source.map(item => {
           val salary: Any = item(4)
           val min: String = salary.toString.split("-")(0)
           val minsalary: String =
             if (min.contains("面议")) min.substring(4)
             else min
           val max: Int = salary.toString.split("-")(1).length - 1
           val maxsalary: String = salary.toString.split("-")(1).substring(0, max)
            (item(0).toString, item(1).toString, item(2).toString, item(3).toString, minsalary.toFloat, maxsalary.toFloat, item(5).toString)
          })
          data.foreach(println)
  
 

 处理完数据后如图，并将其转为小数，方便后续运算

3、转为DF，创建表

      import sprak.implicits._
         val dataDF: DataFrame = data.toDF("address", "company", "edu", "jobName", "minsalary", "maxsalary", "size")
          dataDF.createOrReplaceTempView("salary")
  
 

4、分析不同学历的平均薪资（每个学历的平均估值（最高薪资平均值、最低薪资平均值求平均））

      val sql1 =
           """
       |select s.edu,(s.avg_minsalary + s.avg_maxsalary)/2 as avgSalary from
       |(select edu,avg(minsalary) as avg_minsalary,avg(maxsalary) as avg_maxsalary from salary group by edu) s
       |""".stripMargin
          sprak.sql(sql1).show()
  
 

5、分析不同岗位的平均薪资（求每个学历的平均估值（最高薪资平均值、最低薪资平均值求平均））

      val sql2 =
           """
       |select s.jobName,(s.avg_minsalary+s.avg_maxsalary)/2 as avgSalary from
       |(select jobName,avg(minsalary) as avg_minsalary,avg(maxsalary) as avg_maxsalary from salary group by jobName) s
       |""".stripMargin
          sprak.sql(sql2).show()
  
 

 6、分析各公司提供的岗位

      val sql3 =
           """
       |select company,count(jobName) as jobNum from salary group by company
       |""".stripMargin //分析不同岗位的平均薪资
          sprak.sql(sql3).show()
  
 

完整代码

      package example.spark.test
      import org.apache.spark.rdd.RDD
      import org.apache.spark.sql.{DataFrame, Row, SparkSession}
      object WordCount {
       def main(args: Array[String]): Unit = {
         val sprak: SparkSession = SparkSession.builder().master("local[6]").appName("job")
            .config("spark.sql.warehouse.dir", "E:/")
            .getOrCreate()
          sprak.sparkContext.setLogLevel("ERROR")
         val source: RDD[Row] = sprak.read
            .format("jdbc")
            .option("url", "jdbc:mysql://localhost:3306/crawler")
            .option("dbtable", "job")
            .option("user", "root")
            .option("password", "123456")
            .load()
            .rdd
          source.foreach(println)
          println("------------------------------------------------------------------------------------")
         // val data: RDD[(String, String, String, String, Float, Float, String)] = source.map(item => {
         // var salary = item(4)
         // val minsalary: String = if (salary.toString.split("-")(0).contains("面议"))
         // salary.toString.split("-")(0).substring(4)
         // else salary.toString.split("-")(0)
         // val maxsalary: String = salary.toString.split("-")(1).substring(0, salary.toString.split("-")(1).length - 1)
         // (item(0).toString, item(1).toString, item(2).toString, item(3).toString, minsalary.toFloat, maxsalary.toFloat, item(5).toString)
         // })
         val data: RDD[(String, String, String, String, Float, Float, String)] = source.map(item => {
           val salary: Any = item(4)
           val min: String = salary.toString.split("-")(0)
           val minsalary: String =
             if (min.contains("面议")) min.substring(4)
             else min
           val max: Int = salary.toString.split("-")(1).length - 1
           val maxsalary: String = salary.toString.split("-")(1).substring(0, max)
            (item(0).toString, item(1).toString, item(2).toString, item(3).toString, minsalary.toFloat, maxsalary.toFloat, item(5).toString)
          })
          data.foreach(println)
          println("------------------------------------------------------------------------------------")
         import sprak.implicits._
         val dataDF: DataFrame = data.toDF("address", "company", "edu", "jobName", "minsalary", "maxsalary", "size")
          dataDF.createOrReplaceTempView("salary")
         val sql1 =
           """
       |select s.edu,(s.avg_minsalary + s.avg_maxsalary)/2 as avgSalary from
       |(select edu,avg(minsalary) as avg_minsalary,avg(maxsalary) as avg_maxsalary from salary group by edu) s
       |""".stripMargin
          sprak.sql(sql1).show()
          println("------------------------------------------------------------------------------------")
         val sql2 =
           """
       |select s.jobName,(s.avg_minsalary+s.avg_maxsalary)/2 as avgSalary from
       |(select jobName,avg(minsalary) as avg_minsalary,avg(maxsalary) as avg_maxsalary from salary group by jobName) s
       |""".stripMargin
          sprak.sql(sql2).show()
          println("------------------------------------------------------------------------------------")
         val sql3 =
           """
       |select company,count(jobName) as jobNum from salary group by company
       |""".stripMargin //分析不同岗位的平均薪资
          sprak.sql(sql3).show()
        }
      }
  
 

  
 
 

文章来源: tuomasi.blog.csdn.net，作者：托马斯-酷涛，版权归原作者所有，如需转载，请联系作者。

原文链接：tuomasi.blog.csdn.net/article/details/126039041