spark-sql经典50题

import java.util.Properties

import org.apache.spark.sql.{DataFrame, SparkSession}

object SqlTest {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().appName("sqltest").master("local[*]").getOrCreate();

    import spark.implicits._

    val driver = "com.mysql.jdbc.Driver"
    val url = "jdbc:mysql://192.168.28.70:3306/school"
    val user = "root"
    val password = "ok"

    val prop = new Properties()
    prop.setProperty("user", user)
    prop.setProperty("password", password)
    prop.setProperty("driver", driver)

    val courseTable = "Course"
    val scoreTable = "Score"
    val studentTable = "Student"
    val teacherTable = "Teacher"

    val courseTableDF: DataFrame = spark.read.jdbc(url, courseTable, prop)
    val scoreTableDF = spark.read.jdbc(url, scoreTable, prop)
    val studentTableDF = spark.read.jdbc(url, studentTable, prop)
    val teacherTableDF = spark.read.jdbc(url, teacherTable, prop)

    //  courseTableDF.show()
    // 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
    //    println("1、查询\"01\"课程比\"02\"课程成绩高的学生的信息及课程分数")
    //方法1
//    scoreTableDF.as("s1").join(scoreTableDF.as("s2"),"s_id")
//      .where("s1.c_id=01 and s2.c_id=02 and s1.s_score>s2.s_score")
//      .join(studentTableDF,"s_id").show()
//方法2
//        val frame: DataFrame = scoreTableDF.join(scoreTableDF,Seq("s_id"),"left")
//        val ds1=frame.filter(x => x.get(1).equals("01") && x.get(3).equals("02")
//     && java.lang.Double.parseDouble(x.get(2).toString) > java.lang.Double.parseDouble(x.get(4).toString))
//        val df1: DataFrame = ds1.join(studentTableDF,Seq("s_id"),"left")
//        df1.show()

    // 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
    //    println("2、查询\"01\"课程比\"02\"课程成绩低的学生的信息及课程分数")
    //    val frame2: DataFrame = scoreTableDF.join(scoreTableDF,Seq("s_id"),"left")
    //    val ds2= frame2.filter(x => x.get(1).equals("01") && x.get(3).equals("02")
    //      && java.lang.Double.parseDouble(x.get(2).toString) < java.lang.Double.parseDouble(x.get(4).toString))
    //    val df2: DataFrame = ds2.join(studentTableDF,Seq("s_id"),"left")
    //    df2.show()

    // 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
//          scoreTableDF.groupBy("s_id").avg("s_score")
//      .where("avg(s_score)>=60").join(studentTableDF,"s_id")
//      .select("s_id","s_name","avg(s_score)").show()
//        println("3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩")
//        val frame3: DataFrame = scoreTableDF.groupBy("s_id").avg("s_score").withColumnRenamed("avg(s_score)","avgScore")
//        val ds3= frame3.filter(x=>java.lang.Double.parseDouble(x.get(1).toString)>=60)
//        val df3: DataFrame = ds3.join(studentTableDF,Seq("s_id"),"left").select("s_id","s_name","avgScore")
//        df3.show()
    // 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩-- (包括有成绩的和无成绩的)
//    scoreTableDF.groupBy("s_id").avg("s_score")
//      .where("avg(s_score)<60 or avg(s_score) is null")
//      .join(studentTableDF,"s_id").select("s_id","s_name","avg(s_score)").show()
//        println("4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩")
//        scoreTableDF.groupBy("s_id").avg("s_score").withColumnRenamed("avg(s_score)", "avgScore")
//          .join(studentTableDF, Seq("s_id"), "right").where("avgScore<60 or avgScore is null")
//          .select("s_id","s_name","avgScore").show()
    // 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

//    scoreTableDF.groupBy("s_id").agg(("c_id","count"),("s_score","sum"))
//      .join(studentTableDF,"s_id").select("s_id","s_name","count(c_id)","sum(s_score)").show()

    //    val frame5: DataFrame = scoreTableDF.groupBy("s_id").sum("s_score").withColumnRenamed("sum(s_score)","sumScore")
    //    scoreTableDF.groupBy("s_id").count().withColumnRenamed("count","courseNum")
    //      .join(frame5,Seq("s_id")).join(studentTableDF,Seq("s_id"),"right")
    //      .select("s_id","s_name","courseNum","sumScore").show()
    //    6、查询"李"姓老师的数量
    //      println("6、查询\"李\"姓老师的数量")
    //teacherTableDF.where("t_name like '李%'").select("t_id").count()
    //    println(teacherTableDF.filter("t_name like '李%'").count())
    //    println(teacherTableDF.where("t_name like '李%'").count())
    // 7、查询学过"张三"老师授课的同学的信息
    //    println("7、查询学过\"张三\"老师授课的同学的信息")
    //        teacherTableDF.where("t_name='张三'").select("t_id").join(courseTableDF,Seq("t_id"))
    //          .select("c_id").join(scoreTableDF,Seq("c_id")).select("s_id").join(studentTableDF,"s_id")
    //          .distinct().show()
    // 8、查询没学过"张三"老师授课的同学的信息
    //    println("查询没学过\"张三\"老师授课的同学的信息")
    //    studentTableDF.join(teacherTableDF.where("t_name='张三'").select("t_id").join(courseTableDF,Seq("t_id"))
    //      .select("c_id").join(scoreTableDF,Seq("c_id")).select("s_id").join(studentTableDF,"s_id")
    //      .distinct(),Seq("s_id"),"left_anti").showll
    // 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
    //    println("9、查询学过编号为\"01\"并且也学过编号为\"02\"的课程的同学的信息")
    //    scoreTableDF.join(scoreTableDF,Seq("s_id")).filter(x=>x.get(1).equals("01") && x.get(3).equals("02")).select("s_id")
    //      .join(studentTableDF,"s_id").show()
    // 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
    //    println("10、查询学过编号为\"01\"但是没有学过编号为\"02\"的课程的同学的信息")
    //    studentTableDF.join(scoreTableDF.filter(x=>x.get(1).equals("01")).select("s_id"),"s_id")
    //      .join(scoreTableDF.filter(x=>x.get(1).equals("02")),Seq("s_id"),"left").where("c_id is null")
    //      .select("s_id","s——name","sbirth","sex").show()
    // 11、查询没有学全所有课程的同学的信息
    //    println("11、查询没有学全所有课程的同学的信息")
    //    val courseNum: Long = courseTableDF.count()
    //    scoreTableDF.groupBy("s_id").count()
    //      .filter(x=>java.lang.Integer.parseInt(x.get(1).toString())==courseNum.toInt)
    //      .join(studentTableDF,Seq("s_id"),"right").where("count is null")
    //      .select("s_id","s_name","s_birth","sex").show()
    // 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
    //    println("12、查询至少有一门课与学号为\"01\"的同学所学相同的同学的信息")
    //    scoreTableDF.where("s_id='01'").select("c_id")
    //      .join(scoreTableDF,"c_id").where("c_id is not null")
    //      .select("s_id") .distinct().join(studentTableDF,"s_id").show()
    // 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
    //  println("13、查询和\"01\"号的同学学习的课程完全相同的其他同学的信息")
    // 01的选课数
    //    val course01Num: Long = scoreTableDF.where("s_id='01'").count()// 找到与01选课数相同的学生
    //    val frame13: DataFrame = scoreTableDF.groupBy("s_id").count().filter( d=>java.lang.Integer.parseInt(x.get(1).toString)==course01Num.toInt)
    //    // 找到与01选课有不同的学生
    //    val frame13_2= scoreTableDF.where("s_id='01'").as("s1").join(scoreTableDF.as("s2"), Seq("c_id"), "right")
    //      .where("s1.s_id is null").select("s2.s_id").distinct()
    //    // 选课与01相同，且选课数相同
    //    frame13.as("s").join(frame13_2,Seq("s_id"),"left_anti").select("s_id").join(studentTableDF,"s_id")
    //      .where("s_id!='01'").show()
    // 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
//    studentTableDF.join(studentTableDF,"s_id").join(courseTableDF,"c_id")
//
//    .join(teacherTableDF,"t_id").where("t_name='张三'")
//      .groupBy("c_id").count()
    //    println("查询没学过\"张三\"老师讲授的任一门课程的学生姓名")
    //    studentTableDF.join(teacherTableDF.where("t_name='张三'").select("t_id").join(courseTableDF,Seq("t_id"))
    //      .select("c_id").join(scoreTableDF,Seq("c_id")).select("s_id").join(studentTableDF,"s_id")
    //      .distinct(),Seq("s_id"),"left_anti").show
    // 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    //    scoreTableDF.filter(x=>java.lang.Integer.parseInt(x.get(2).toString)<60).groupBy("s_id").count()
    //      .filter(x=>java.lang.Integer.parseInt(x.get(1).toString)>=2).select("s_id").join(studentTableDF,"s_id").show()
    // 16、检索"01"课程分数小于60，按分数降序排列的学生信息
    //    scoreTableDF.where("c_id='01' and s_score<60 ").join(studentTableDF,"s_id").orderBy($"score".desc)
    //      .show()
    // 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    //    scoreTableDF.groupBy("s_id").avg("s_score")
    //      .join(scoreTableDF.groupBy("s_id").sum(),"s_id").orderBy($"avg(score)".desc).show()
    //18、询各科成绩最高分、最低分和平均分
    //    val jige: DataFrame = scoreTableDF.rdd.map(x => {
    //      if (x(2).toString.toInt >= 60)
    //        (x(1).toString, 1)
    //      else
    //        (x(1).toString, 0)
    //    }).reduceByKey(_ + _).toDF("cid", "及格")
    //    val zhongdeng: DataFrame = scoreTableDF.rdd.map(x => {
    //      if (x(2).toString.toInt >= 70 && x(2).toString.toInt < 80)
    //        (x(1).toString, 1)
    //      else
    //        (x(1).toString, 0)
    //    }).reduceByKey(_ + _).toDF("cid", "中等")
    //    val youliang: DataFrame = scoreTableDF.rdd.map(x => {
    //      if (x(2).toString.toInt >= 80 && x(2).toString.toInt < 90)
    //        (x(1).toString, 1)
    //      else
    //        (x(1).toString, 0)
    //    }).reduceByKey(_ + _).toDF("cid", "优良")
    //    val youxiu: DataFrame = scoreTableDF.rdd.map(x => {
    //      if (x(2).toString.toInt >= 90)
    //        (x(1).toString, 1)
    //      else
    //        (x(1).toString, 0)
    //    }).reduceByKey(_ + _).toDF("c_id", "优秀")
    //
    //    val frame18: DataFrame = scoreTableDF.groupBy("cid").agg(("s_score","max"),("s_score","min"),("s_score","avg"),("s_score","count"))
    //    frame18.join(courseTableDF.select("c_id","c_name"),"c_id").join(jige,"c_id").join(zhongdeng,"c_id").join(youliang,"c_id")
    //      .join(youxiu,"c_id")
    //      .withColumn("及格",jige.col("及格")/frame18.col("count(s_score)"))
    //      .withColumn("中等",zhongdeng.col("中等")/frame18.col("count(s_score)"))
    //      .withColumn("优良",youliang.col("优良")/frame18.col("count(s_score)"))
    //      .withColumn("优秀",youxiu.col("优秀")/frame18.col("count(s_score)"))
    //      .show()
    //19、按各科成绩进行排序，并显示排名
    //scoreTableDF.selectExpr("*","row_number() over(partition by c_id order by s_score desc) rank").show
    //20、查询学生的总成绩并进行排名:
    //scoreTableDF.groupBy("s_id").sum("s_score").orderBy($"sum(s_score)".desc).show()
        scoreTableDF.selectExpr("*","sum(s_score) over(partition by s_id) as sum_score")
          .dropDuplicates("s_id","sum_score")
          .selectExpr("s_id","sum_score","row_number() over(order by sum_score desc) rank").show
    //21、查询不同老师所教不同课程平均分从高到低显示
    //    courseTableDF.join(scoreTableDF,"c_id").join(teacherTableDF,"t_id").groupBy("t_id","c_id")
    //      .avg("s_score").orderBy($"avg(s_score)".desc).show()
    //22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:
//    scoreTableDF.selectExpr("*","row_number() over(partition by c_id order by s_score desc) rank")
//      .where("rank between 2 and 3").join(studentTableDF,"s_id").show
//23.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
    // 24、查询学生平均成绩及其名次
//  scoreTableDF.groupBy("s_id").avg("s_score")
//    .withColumnRenamed("avg(s_score)","avgscore")
//    .selectExpr("*","rank() over(order by avgscore desc) as rand_num").show()
 //   25、查询各科成绩前三名的记录
//      scoreTableDF.selectExpr("*","rank() over(partition by c_id order by s_score desc ) as rank_num")
//        .where("rank_num between 1 and 3").show()
    // 26、查询每门课程被选修的学生数
    //scoreTableDF.groupBy("c_id").count().show()
    // 27、查询出只有两门课程的全部学生的学号和姓名
//    scoreTableDF.groupBy("s_id").count().where("count=2")
//    .join(studentTableDF,"s_id").select("*").show()
    // 28、查询男生、女生人数
    //studentTableDF.groupBy("s_sex").count().show()
    // 29、查询名字中含有"风"字的学生信息
   // scoreTableDF.select("*").where("s_name= like '%风%'").show()
    // 30、查询同名同性学生名单，并统计同名人数
   // studentTableDF.groupBy("s_name").count().where("count>1").show()
    // 31、查询1990年出生的学生名单
    //studentTableDF.select("*").where("year(s_birth)=1990").show()
    // 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
//      scoreTableDF.groupBy("c_id").avg("s_score")
//        .orderBy($"avg(s_score)".desc,$"c_id").show()
    //33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
//    scoreTableDF.groupBy("s_id")
//      .avg("s_score").where("avg(s_score)>85")
//      .join(studentTableDF,"s_id")
//      .select("s_id","s_name","avg(s_score)").show()
    // 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
//      scoreTableDF.where("s_score<60").join(courseTableDF,"c_id")
//    .where("c_name='数学'").join(studentTableDF,"s_id")
//    .select("s_name","s_score").show()
    // 35、查询所有学生的课程及分数情况；
//      courseTableDF.join(scoreTableDF,"c_id").join(studentTableDF,"s_id")
//    .select("*").show()
    // 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
//    scoreTableDF.where("s_score>70").join(courseTableDF,"c_id")
//    .join(studentTableDF,"s_id").select("s_name","c_name","s_score").show()
    //37、查询不及格的课程
//    scoreTableDF.where("s_score<60").join(courseTableDF,"c_id")
//      .join(studentTableDF,"s_id").select("s_name","c_name","s_score").show()
    // 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
//    scoreTableDF.where("s_id=01").where("s_score>80")
//      .join(studentTableDF,"s_id").select("s_id","s_name","s_score").show()
    // 39、求每门课程的学生人数
   // scoreTableDF.groupBy("c_id").count().show()
    // 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
//    teacherTableDF.where("t_name='张三'")
//      .join(courseTableDF,"t_id").join(scoreTableDF,"c_id")
//      .join(studentTableDF,"s_id")
//      .selectExpr("*","rank() over(order by s_score) as rank_num")
//      .where("rank_num=1").show()
     // 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
//      scoreTableDF.as("s1").join(scoreTableDF.as("s2"))
//    .where("s1.s_score=s2.s_score and s1.c_id!=s2.c_id").show()
    //42、查询每门功成绩最好的前两名
//    scoreTableDF.join(studentTableDF,"s_id")
//      .selectExpr("*","rank() over(partition by c_id order by s_score desc) as rank_num")
//      .where("rank_num <=2").show()
    // 43、统计每门课程的学生选修人数（超过5人的课程才统计）。
    // 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
//      scoreTableDF.groupBy("c_id").count().where("count>5")
//      .orderBy($"count".desc,$"c_id").show()
    // 44、检索至少选修两门课程的学生学号
//      scoreTableDF.groupBy("s_id").count().where("count>=2")
//      .join(studentTableDF,"s_id").select("s_id").show()
    // 45、查询选修了全部课程的学生信息
//    scoreTableDF.groupBy("s_id").count().where("count=3")
//      .join(studentTableDF,"s_id").show()
    // 46、查询各学生的年龄
//    studentTableDF.selectExpr("*","year(current_date)-year(s_birth)").show()
    //47、查询本周过生日的学生
//    studentTableDF.where("weekofyear(current_date)=weekofyear(concat(year(current_date),'-',month(s_birth),'-',day(s_birth)))").show()
    // 48、查询下周过生日的学生
   // studentTableDF.where("weekofyear(current_date+1)=weekofyear(concat(year(current_date),'-',month(s_birth),'-',day(s_birth)))").show()

    // 49、查询本月过生日的学生
   // studentTableDF.where("month(current_date)=month(s_birth)").show()
    // 50、查询下月过生日的学生
    //studentTableDF.where("(month(current_date)+1)=month(sbirth)").show()
  }
}