大数据——Scala算子练习题

现有学生成绩表、学生信息表、老师信息表和课程信息表，根据要求得到相应的结果

import java.io.File

import scala.collection.mutable
import scala.io.Source

object Test {
  def main(args: Array[String]): Unit = {
    //各表数据
    val course = Source.fromFile(new File("file/course.txt"))
      .getLines().map(line => {
      val ps = line.split(",")
      (ps(0).toInt, ps(1), ps(2).toInt)
    }).toArray

    val student = Source.fromFile(new File("file/student.txt"))
      .getLines().map(line => {
      val ps = line.split(",")
      (ps(0).toInt,ps(1),ps(2),ps(3))
    }).toArray

    val teacher = Source.fromFile(new File("file/teacher.txt"))
      .getLines().map(line => {
      val ps = line.split(",")
      (ps(0).toInt,ps(1))
    }).toArray

    val score = Source.fromFile(new File("file/score.txt"))
      .getLines().map(line => {
      val ps = line.split(",")
      (ps(0).toInt,ps(1).toInt,ps(2).toInt)
    }).toArray
    //学生成绩补全
    val stuIds: Array[Int] = student.map(_._1)
    val t1 = new mutable.HashMap[Int, Int]()
    val t2 = new mutable.HashMap[Int, Int]()
    val t3 = new mutable.HashMap[Int, Int]()

    val tuple = score.filter(x => x._2 == 1 || x._2 == 2).partition(_._2 == 1)
    val class1 = tuple._1.map(x=>(x._1,x._3)).toMap
    val class2 = tuple._2.map(x=>(x._1,x._3)).toMap
    val class3 = score.filter(_._2 == 3).map(x => (x._1, x._3)).toMap
    stuIds.foreach(stuId=>{
      if(class1.contains(stuId)){
        t1.put(stuId,class1(stuId))
      }else{
        t1.put(stuId,0)
      }
      if(class2.contains(stuId)){
        t2.put(stuId,class2(stuId))
      }else{
        t2.put(stuId,0)
      }
      if(class3.contains(stuId)){
        t3.put(stuId,class3(stuId))
      }else{
        t3.put(stuId,0)
      }
    })

    //学生id=>学生信息
    val stu = student.map(x=>(x._1, s"${x._2},${x._3},${x._4}")).toMap

    //课程id=>课程名
    val mapCourse = course.map(x => (x._1, x._2)).toMap

    //练习1：查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数(没有成绩不做比较)
    /*t1.toArray.sortBy(_._1).zip(t2.toArray.sortBy(_._1))
      .map(x=>(x._1._1,x._1._2,x._2._2))
      .filter(x=>x._2>x._3&&x._3!=0)
      .map(x=>stu(x._1)).foreach(println)*/

    //练习2：查询"01"课程比"02"课程成绩低的学生的信息及课程分数(没有成绩不做比较)
    /*t1.toArray.sortBy(_._1).zip(t2.toArray.sortBy(_._1))
      .map(x=>(x._1._1,x._1._2,x._2._2))
      .filter(x=>x._2<x._3&&x._2!=0)
      .map(x=>stu(x._1)).foreach(println)*/

    //练习3：查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩(无成绩不参与平均值计算)
    /*score.map(x=>(x._1,x._3))
      .groupBy(_._1)
      .map(x=>(x._1,x._2.map(y=>y._2).sum*1.0/x._2.length))
      .filter(_._2>=60)
      .map(x=>stu(x._1))
      .foreach(println)*/

    //练习4：查询在 scoreinfo 表存在成绩的学生信息
    /*score.groupBy(_._1)
      .keys.map(stu(_))
      .foreach(println)*/

    //练习5：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
    /*score.groupBy(_._1)
      .map(x=>(x._1,stu(x._1),x._2.length,x._2.map(_._3).sum))
      .toArray.sortBy(-_._4)
      .foreach(println)*/

    //练习6：查询「李」姓老师的数量
    //println(teacher.count(x => x._2.contains("李")))

    //练习7：查询学过「张三」老师授课的同学的信息
    /*val keys = teacher.filter(_._2 == "张三").toMap.keys
    val courseIds = keys.flatMap(key => {
      course.filter(_._3 == key).map(_._1)
    }).toArray

    val stuIds2 = courseIds.flatMap(courseId => {
      score.filter(_._2==courseId).map(_._1)
    })

    stuIds2.map(stuId=>{
      stu(stuId)
    }).foreach(println)*/

    //练习8：查询没有学全所有课程的同学的信息
    /*score.groupBy(_._1).map(x=>(x._1,x._2.length))
      .filter(_._2<course.map(_._1).length)
      .foreach(x=>{
        println(stu(x._1))
      })*/

    //练习9：查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    /*score.filterNot(_._1 == 1)
      .map(x => (x._1, x._2))
      .groupBy(_._1).mapValues(x => x.map(x => {

      if (score.filter(_._1 == 1).map(_._2).contains(x._2)) 1
      else 0
    }).sum
    ).filter(_._2 >= 1).keys
      .foreach(key=>{
        println(stu(key))
      })*/

    //练习10：查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
    /*val src = score.map(x => (x._1, x._2)).partition(_._1 == 1)
    val ints: Array[Int] = src._1.map(_._2)
    src._2.groupBy(_._1).mapValues(x=>x.map(_._2))
      .filter(_._2.sameElements(ints)).keys.foreach(key=> println(stu(key)))*/

    //练习11：查询没学过"张三"老师讲授的任一门课程的学生姓名
    /*val keys = teacher.filter(_._2 == "张三").toMap.keys
    val courseIds: Array[Int] = keys.flatMap(key => {
      course.filter(_._3 == key).map(_._1)
    }).toArray

    score.map(x => (x._1, x._2))
      .groupBy(_._1)
      .map(x => {
        (x._1, x._2.map(_._2).combinations(courseIds.length))
      }).filterNot(_._2.toArray.exists(x => x.sameElements(courseIds)))
      .keys.foreach(x=>println(stu(x)))*/

    //练习12：查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    /*score.filter(_._3<60)
      .map(x=>(x._1,x._3))
      .groupBy(_._1)
      .map(x=>(x._1,x._2.length,x._2.map(_._2).sum/x._2.length))
      .filter(_._2>=2).map(x=>(stu(x._1),x._3)).foreach(println)*/

    //练习13：检索" 01 "课程分数小于 60，按分数降序排列的学生信息
    /*score.filter(x=>x._2==1&&x._3<60)
      .sortBy(-_._3)
      .foreach(x=>println(s"${stu(x._1)}\t${x._3}"))*/

    //练习14：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    /*score.groupBy(_._1)
      .map(x=>(stu(x._1).split(",")(0),
        x._2.map(y=>s"${mapCourse(y._2)}_${y._3}").mkString(","),
        x._2.map(_._3).sum/x._2.length))
      .toArray.sortBy(-_._3)
      .foreach(println)*/

    //练习15：查询各科成绩最高分、最低分和平均分
    /*score.groupBy(_._2).map(x=>(mapCourse(x._1),
      x._2.map(_._3).max,
      x._2.map(_._3).min,
      x._2.map(_._3).sum/x._2.length))
      .foreach(println)*/

    //练习16：要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
    /*score.groupBy(_._2)
      .map(x=>(x._1,x._2.length))
      .toArray
      .sortBy(_._1).sortBy(-_._2)
      .foreach(println)*/

    //练习17：按各科成绩进行排序，并显示排名， Score 重复时并列，排名连续
    /*score.groupBy(_._2).mapValues(_.map(_._3)).toArray
      .sortBy(_._1)
      .foreach(x=>{
        println(x._1)
        val ints = x._2.sortBy(x => -x)
        val map: Map[Int, Int] = ints.groupBy(x => x).toArray.sortBy(-_._1)
          .zipWithIndex.map(y => (y._1._1, y._2 + 1)).toMap
        ints.map(y=>(y,map(y))).sortBy(_._2).foreach(y=>println(s"\t${y._1}\t${y._2}"))
      })*/

    //练习18：按各科成绩进行排序，并显示排名， Score 重复时并列，排名重复有空缺
    /*score.groupBy(_._2).mapValues(_.map(_._3)).toArray
      .sortBy(_._1).foreach(x=>{
      println(x._1)
      val ints = x._2.sortBy(x => -x)
      val intToInt: Map[Int, Int] = ints.groupBy(x => x).mapValues(_.length)
      var i = 0
      var j = -1
      ints.map(elem => {
        i+=1
        if (intToInt(elem)!=1) {
          j+=1
          (elem,i-j)
        }else{
          (elem,i)
        }
      }).foreach(println)
    })*/

    //练习19：查询学生的总成绩，并进行排名，总分重复时保留名次空缺
    /*val tuples = score.groupBy(_._1).mapValues(_.map(_._3).sum)
      .toArray.sortBy(-_._2)
    val intToInt = tuples.groupBy(_._2).map(x => (x._1, x._2.length))
    var i = 0
    var j = -1
    tuples.map(elem =>{
      i += 1
      if (intToInt(elem._2)!=1){
        j += 1
        (elem,i-j)
      }else{
        (elem,i)
      }
    }).foreach(println)*/

    //练习20：查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
    /*val src = score.groupBy(_._1).mapValues(_.map(_._3).sum).toArray.sortBy(-_._2)
    val map = src.groupBy(_._2).keys.toArray.sortBy(-_).zipWithIndex.toMap
    src.map(x=>(x._1,x._2,map(x._2)+1)).foreach(println)*/

    //练习21：查询各科成绩前三名的记录(不考虑并列)
    /*score.groupBy(_._2)
      .map(x=>(x._1,x._2.map(y=>(student.map(y=>(y._1,y._2)).toMap.get(y._1).get,y._3)).sortBy(-_._2).take(3)))
      .toArray.sortBy(_._1)
      .foreach(x=>{
        println(x._1)
        x._2.foreach(println)
      })*/

    //练习22：查询各科成绩前三名的记录(考虑并列)
    /*score.groupBy(_._2)
      .map(x=>(x._1,x._2.map(y=>(student.map(y=>(y._1,y._2)).toMap.get(y._1).get,y._3)).sortBy(-_._2)))
      .toArray.sortBy(_._1)
      .foreach(x=>{
        println(x._1)
        var i = 0
        var j = -1
        val map = x._2.groupBy(_._2).map(y => (y._1, y._2.length))
        x._2.map(y => {
          i += 1
          if (map(y._2) != 1) {
            j += 1
            (y, i - j)
          } else {
            (y, i)
          }
        }).filter(_._2 <= 3).sortBy(_._2).foreach(println)
      })*/

    //练习23：查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
    /*score.filter(x=>x._2==course.filter(_._2=="数学").head._1 && x._3 <60)
      .map(x=>(student.map(y=>(y._1,y._2)).toMap.get(x._1).get,x._3))
      .foreach(println)*/
  }