大数据毕业设计Python+Spark知识图谱高考志愿推荐系统 高考数据分析 高考可视化 高考大数据 计算机毕业设计 机器学习 深度学习 人工智能

附件3

文山学院本科生毕业论文（设计）开题报告

备注：1. 题目来源栏应填：教师科研、社会实践、实验教学、教育教学等；2. 题目类别栏应填：应用研究、理论研究、艺术设计、程序软件开发等。

核心算法代码如下：

package com.sql
 
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.types._
import org.junit.Test
 
import java.util.Properties
 
class GaokaoSpark2024 {
  val spark = SparkSession.builder()
    .master("local[6]")
    .appName("高考数据实时计算")
    .getOrCreate()
  //省控线模式
  val school_province_score_Schema = StructType(
    List(
      StructField("id", IntegerType),
      StructField("school_id", IntegerType),
      StructField("student_province_id", IntegerType),
      StructField("student_type",IntegerType),
      StructField("year", IntegerType),
      StructField("max_score", IntegerType),
      StructField("min_score",IntegerType),
      StructField("avg_score", IntegerType),
      StructField("pro_score", IntegerType),
      StructField("min_position", IntegerType),
      StructField("batch_name", StringType)
 
    )
  )
 
  val school_province_score_Df = spark.read.option("header", "false").schema(school_province_score_Schema).csv("hdfs://192.168.227.166:9000/gaokao/ods_school_province_score/school_province_score.csv")
 
 
 
 
  //省份
  val province_Schema = StructType(
    List(
      StructField("id", StringType),
      StructField("province_id", StringType),
      StructField("province_name",StringType),
      StructField("policy", StringType),
      StructField("gknum1", StringType),
      StructField("gknum2",StringType),
      StructField("gknum3", StringType)
 
    )
  )
 
  val province_Df = spark.read.option("header", "false").schema(province_Schema).csv("hdfs://192.168.227.166:9000/gaokao/ods_province/province.csv")
 
 
  //专业线
  val school_special_score_Schema = StructType(
    List(
      StructField("year", StringType),
      StructField("school_id", StringType),
      StructField("special_id",StringType),
      StructField("type", StringType),
      StructField("batch", StringType),
      StructField("zslx",StringType),
      StructField("max", StringType),
      StructField("min", StringType),
      StructField("average", StringType),
      StructField("min_section", StringType),
      StructField("province", StringType),
      StructField("spe_id", StringType),
      StructField("info", StringType),
      StructField("special_group", StringType),
      StructField("first_km", StringType),
      StructField("sp_type", StringType),
      StructField("sp_fxk", StringType),
      StructField("sp_sxk", StringType),
      StructField("sp_info", StringType),
      StructField("sp_xuanke", StringType),
      StructField("level1_name", StringType),
      StructField("level2_name", StringType),
      StructField("level3_name", StringType),
      StructField("level1", StringType),
      StructField("level2", StringType),
      StructField("level3", StringType),
      StructField("spname", StringType),
      StructField("zslx_name", StringType),
      StructField("local_batch_name", StringType),
      StructField("sg_fxk", StringType),
      StructField("sg_sxk", StringType),
      StructField("sg_type", StringType),
      StructField("sg_name", StringType),
      StructField("sg_info", StringType),
      StructField("sg_xuanke", StringType)
 
    )
  )
 
  val school_special_score_Df = spark
                                .read.option("header", "false")
                                 //.option("nanValue",0)
                                .schema(school_special_score_Schema)
                                .csv("hdfs://192.168.227.166:9000/gaokao/ods_school_special_score/school_special_score.csv")
 
 
  //学校
  val school_Schema = StructType(
    List(
      StructField("id", IntegerType),
      StructField("school_id", IntegerType),
      StructField("school_name",StringType),
      StructField("province_id", IntegerType),
      StructField("province_name", StringType, nullable=true),
      StructField("city_id", IntegerType),
      StructField("city_name",StringType),
      StructField("level", StringType),
      StructField("type", StringType),
      StructField("nature", StringType),
      StructField("email", StringType),
      StructField("phone", StringType),
      StructField("site", StringType),
      StructField("address", StringType),
      StructField("status", IntegerType),
      StructField("ad", StringType),
      StructField("hot", IntegerType),
      StructField("img", StringType),
      StructField("oid", IntegerType),
      StructField("label", StringType)
 
    )
  )
 
  val school_Df = spark.read.option("header", "false").schema(school_Schema).csv("hdfs://192.168.227.166:9000/gaokao/ods_school/school.csv")
 
 
  //软科
  val ruanke_rank_Schema = StructType(
    List(
      StructField("school_id", IntegerType),
      StructField("province_name", StringType),
      StructField("type_name",StringType),
      StructField("name", StringType),
      StructField("rank", IntegerType),
      StructField("sort", IntegerType)
 
    )
  )
 
  val ruanke_rank_Df = spark.read.option("header", "false").schema(ruanke_rank_Schema).csv("hdfs://192.168.227.166:9000/gaokao/ods_ruanke_rank/ruanke_rank.csv")
 
  //QS
  val qs_world_Schema = StructType(
    List(
      StructField("school_id", StringType),
      StructField("type_name", StringType),
      StructField("province_name",StringType),
      StructField("rank", StringType),
      StructField("name", StringType),
      StructField("sort", StringType)
 
    )
  )
 
  val qs_world_Df = spark.read.option("header", "false").schema(qs_world_Schema).csv("hdfs://192.168.227.166:9000/gaokao/ods_ruanke_rank/ruanke_rank.csv")
 
 
  //专业
  val special_Schema = StructType(
    List(
      StructField("boy_rate", StringType),
      StructField("degree", StringType),
      StructField("fivesalaryavg",StringType),
      StructField("girl_rate", StringType),
      StructField("hightitle", StringType),
      StructField("id", StringType),
      StructField("level1", StringType),
      StructField("level1_name", StringType),
      StructField("level2", StringType),
      StructField("level2_name", StringType),
      StructField("level3", StringType),
      StructField("level3_name", StringType),
      StructField("limit_year", StringType),
      StructField("name", StringType),
      StructField("rank", StringType),
      StructField("salaryavg", StringType),
      StructField("spcode", StringType),
      StructField("special_id", StringType),
      StructField("view_month", StringType),
      StructField("view_total", StringType),
      StructField("view_week", StringType),
      StructField("detail_link", StringType)
 
    )
  )
 
  val special_Df = spark.read.option("header", "false").schema(special_Schema).csv("hdfs://192.168.227.166:9000/gaokao/ods_special/special.csv")
 
 
  @Test
  def init(): Unit = {
    //school_province_score_Df.show()
    //province_Df.show()
    //school_special_score_Df.show()
    //school_Df.show()
    //ruanke_rank_Df.show()
    qs_world_Df.show()
  }
 
 
 
//  ----剩余使用spark_sql完成
//    ---指标3 录取批次Top10
  @Test
  def tables03(): Unit = {
   school_province_score_Df.createOrReplaceTempView("ods_school_province_score")
    val df2 = spark.sql(
      """
            select batch_name,count(1) num
            from ods_school_province_score
            where year = date_format(current_timestamp(),'yyyy')
            group by batch_name
            order by num desc
            limit 10
    """)
    df2
//      .show(50)
     .coalesce(1)
      .write
      .mode("overwrite")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("user", "root")
      .option("password", "123456")
      .jdbc(
        "jdbc:mysql://bigdata:3306/2412_gaokao?useSSL=false",
        "table03",
        new Properties()
      )
  }
 
 
 
 
//指标4  最受欢迎的专业
 
  @Test
  def tables04(): Unit = {
    school_special_score_Df.createOrReplaceTempView("ods_school_special_score")
    val df2 = spark.sql(
      """
      select spname,count(1) num
      from ods_school_special_score
      group by spname
      order by num desc
      limit 10
    """)
    df2
      //      .show(50)
      .coalesce(1)
      .write
      .mode("overwrite")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("user", "root")
      .option("password", "123456")
      .jdbc(
        "jdbc:mysql://bigdata:3306/2412_gaokao?useSSL=false",
        "table04",
        new Properties()
      )
  }
 
 
  //--指标5：中国地图
  @Test
  def tables05(): Unit = {
    school_Df.createOrReplaceTempView("ods_school")
    val df2 = spark.sql(
      """
      select province_name,count(1) num
      from ods_school
      where address is not null
      group by province_name
    """)
    df2
      //      .show(50)
      .coalesce(1)
      .write
      .mode("overwrite")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("user", "root")
      .option("password", "123456")
      .jdbc(
        "jdbc:mysql://bigdata:3306/2412_gaokao?useSSL=false",
        "table05",
        new Properties()
      )
  }
//---指标6：软科排名Top10高校
  @Test
  def tables06(): Unit = {
    ruanke_rank_Df.createOrReplaceTempView("ods_ruanke_rank")
    val df2 = spark.sql(
      """
      select name,rank
      from ods_ruanke_rank
      order by rank asc
      limit 10
    """)
    df2
   //.show(50)
      .coalesce(1)
      .write
      .mode("overwrite")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("user", "root")
      .option("password", "123456")
      .jdbc(
        "jdbc:mysql://bigdata:3306/2412_gaokao?useSSL=false",
        "table06",
        new Properties()
      )
  }
 
//---指标7特殊原因只能使用hive_sql
 
 
  //指标8 招生类型占比
  @Test
  def tables08(): Unit = {
    school_special_score_Df.createOrReplaceTempView("ods_school_special_score")
    val df2 = spark.sql(
      """
      select zslx_name,count(1) num
      from ods_school_special_score
      where zslx_name is not null
      group by zslx_name
      order by num desc
      limit 10
    """)
    df2
      //.show(50)
      .coalesce(1)
      .write
      .mode("overwrite")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("user", "root")
      .option("password", "123456")
      .jdbc(
        "jdbc:mysql://bigdata:3306/2412_gaokao?useSSL=false",
        "table08",
        new Properties()
      )
  }
 
 
  //指标9 招生类型占比
  @Test
  def tables09(): Unit = {
    special_Df.createOrReplaceTempView("ods_special")
    val df2 = spark.sql(
      """
        select name,view_total
        from ods_special
        order by view_total desc
        limit 10
    """)
    df2
      //.show(50)
      .coalesce(1)
      .write
      .mode("overwrite")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("user", "root")
      .option("password", "123456")
      .jdbc(
        "jdbc:mysql://bigdata:3306/2412_gaokao?useSSL=false",
        "table09",
        new Properties()
      )
  }
 
}