Spark_Sql50题（DataFrame）

最新推荐文章于 2024-03-15 15:35:18 发布

serendipityLee

最新推荐文章于 2024-03-15 15:35:18 发布

阅读量1.6w

点赞数 3

分类专栏： Spark 文章标签： spark 大数据

本文链接：https://blog.csdn.net/weixin_46117281/article/details/121917627

版权

Spark 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

表名和字段信息

课程表:
Course
c_id：课程编号
c_name：课程名称
t_id：教师编号

学生表:
Student
s_id：学号
s_name：姓名
s_birth：出生日期
s_sex：性别

教师表:
Teacher
t_id：教师编号
t_name：教师姓名

成绩表:
Score
s_id：学生编号
c_id：课程编号
s_score：分数

建表语句：

CREATE DATABASE sql50;

use sql50;

DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course` (
  `c_id` varchar(20) NOT NULL,
  `c_name` varchar(20) NOT NULL DEFAULT '',
  `t_id` varchar(20) NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into `Course`(`c_id`,`c_name`,`t_id`) values ('01','语文','02'),('02','数学','01'),('03','英语','03');

DROP TABLE IF EXISTS `Score`;
CREATE TABLE `Score` (
  `s_id` varchar(20) NOT NULL,
  `c_id` varchar(20) NOT NULL,
  `s_score` int(3) DEFAULT NULL,
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into `Score`(`s_id`,`c_id`,`s_score`) values ('01','01',80),('01','02',90),('01','03',99),('02','01',70),('02','02',60),('02','03',80),('03','01',80),('03','02',80),('03','03',80),('04','01',50),('04','02',30),('04','03',20),('05','01',76),('05','02',87),('06','01',31),('06','03',34),('07','02',89),('07','03',98);

DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(20) NOT NULL DEFAULT '',
  `s_birth` varchar(20) NOT NULL DEFAULT '',
  `s_sex` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into `Student`(`s_id`,`s_name`,`s_birth`,`s_sex`) values ('01','赵雷','1990-01-01','男'),('02','钱电','1990-12-21','男'),('03','孙风','1990-05-20','男'),('04','李云','1990-08-06','男'),('05','周梅','1991-12-01','女'),('06','吴兰','1992-03-01','女'),('07','郑竹','1989-07-01','女'),('08','王菊','1990-01-20','女');

DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher` (
  `t_id` varchar(20) NOT NULL,
  `t_name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into `Teacher`(`t_id`,`t_name`) values ('01','张三'),('02','李四'),('03','王五');

连接

package SQL50

import java.util.Properties

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

object DF {
  def main(args: Array[String]): Unit = {

    val sparkSession: SparkSession = SparkSession
      .builder()
      .appName("DF_SQL50")
      .master("local[*]")
      .getOrCreate()

    val url="jdbc:mysql://192.168.153.101:3306/sql50"
    val user="root"
    val pwd="okok"
    val driver="com.mysql.jdbc.Driver"
    val properties = new Properties()
    properties.setProperty("user",user)
    properties.setProperty("password",pwd)
    properties.setProperty("driver",driver)

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

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

    import sparkSession.implicits._
    import org.apache.spark.sql.functions._
	}
}

题目

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

val frame: DataFrame = scoreTableDF.join(scoreTableDF,"s_id")
frame.show()
val ds: Dataset[Row] = frame.filter(x => (x.get(1).equals("01")
&& x.get(3).equals("02")
&& (x.get(2).asInstanceOf[Integer] > x.get(4).asInstanceOf[Integer])))
ds.show()
val ds2: DataFrame = ds.join(studentTableDF,"s_id")
ds2.show()

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

val frame2: DataFrame = scoreTableDF.as("s1").join(scoreTableDF.as("s2"),"s_id")
frame2.show()
val frame3: DataFrame = frame2.filter("s1.c_id=01 and s2.c_id=02 and s1.s_score<s2.s_score")
  .join(studentTableDF, "s_id")
frame3.show

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

 val value: Dataset[Row] = scoreTableDF
      .groupBy("s_id")
      .avg("s_score")
      .join(studentTableDF, "s_id")
      .filter($"avg(s_score)" >= 60)
    value.show()

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:(包括有成绩的和无成绩的)

 val unit: Dataset[Row] = studentTableDF.join(scoreTableDF.groupBy("s_id").avg("s_score"), Seq("s_id"), "left_outer")
      .where($"avg(s_score)" < 60 || $"avg(s_score)".isNull)
    unit.show()

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

val frame: DataFrame = studentTableDF.join(scoreTableDF.groupBy("s_id").count(), Seq("s_id"), "left_outer")
  .join(scoreTableDF.groupBy("s_id").sum(), Seq("s_id"), "left_outer")
frame.show()

6、查询"李"姓老师的数量

val frame6: Long = teacherTableDF.where("t_name like '李%'").count()
println(frame6)

7、查询学过"张三"老师授课的同学的信息

val frame7: DataFrame = scoreTableDF.join(courseTableDF, "c_id")
  .join(teacherTableDF, "t_id")
  .where("t_name='张三'")
  .join(studentTableDF, "s_id")
frame7.show()

8、查询没学过"张三"老师授课的同学的信息

 scoreTableDF
      .join(courseTableDF,"c_id")
      .join(teacherTableDF,"t_id")
      .join(studentTableDF,"s_id")
      .createTempView("aa")
    sparkSession.sql("select * from aa where t_name !='张三'").show()

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

 studentTableDF
      .join(scoreTableDF.filter("c_id=01"),"s_id")
      .join(scoreTableDF.filter("c_id=02"),"s_id")
      .show()

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

val frame: DataFrame = studentTableDF
  .join(scoreTableDF.filter("c_id = 01"), "s_id")
  .join(scoreTableDF.filter("c_id != 02"), "s_id")
val unit: Dataset[Row] = frame.filter(x=>(x.get(5) != x.get(7)))
    unit.show()

11、查询没有学全所有课程的同学的信息

studentTableDF
  .join(scoreTableDF,Seq("s_id"),"left_outer")
  .groupBy("s_id")
  .count()
  .where("count != 3")
  .join(studentTableDF,"s_id")
  .show()

.12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

scoreTableDF
  .join(scoreTableDF.select("c_id").where("s_id=01"),"c_id")
  .select("s_id")
  .distinct()
  .where("s_id != 01")
  .join(studentTableDF,"s_id")
  .show()

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

//scoreTableDF.select("c_id").where("s_id=1").show()
scoreTableDF
  .join(scoreTableDF.select("c_id").where("s_id=1"),"c_id")
  .groupBy("s_id")
  .count()
  .where(s"count=${scoreTableDF.where("s_id=1").count()} and s_id !=1")
  .join(studentTableDF,"s_id")
  .show()

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

studentTableDF.join(
    scoreTableDF.join(
      courseTableDF.join(teacherTableDF,"t_id").where("t_name='张三'"),
      "c_id").select("s_id","t_name"),
    Seq("s_id"),"left_outer"
  ).where("t_name is null").show()

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

 scoreTableDF
      .where("s_score<60")
      .groupBy("s_id")
      .count()
      .where("count>=2")
      .join(scoreTableDF,"s_id")
      .groupBy("s_id")
      .avg("s_score")
      .join(studentTableDF,"s_id")
      .show()

16、检索"01"课程分数小于60，按分数降序排列的学生信息

   scoreTableDF
      .where("s_score<60 and c_id=1")
      .join(studentTableDF,"s_id")
      .orderBy(desc("s_score"))
      .show()

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

scoreTableDF
.join(scoreTableDF.groupBy("s_id").avg("s_score"),Seq("s_id"),"left_outer")
  .join(studentTableDF,"s_id")
  .orderBy($"avg(s_score)".desc)
  .show()

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

val jige = scoreTableDF.rdd.map(x=>{if(x.getAs("s_score").toString.toInt > 60) (x(1).toString,1) else (x(1).toString,0)}).reduceByKey(_+_).toDF("c_id","jige")
val zhongdeng = scoreTableDF.rdd.map(x=>{if(x.getAs("s_score").toString.toInt > 70) (x(1).toString,1) else (x(1).toString,0)}).reduceByKey(_+_).toDF("c_id","zhongdeng")
val youliang = scoreTableDF.rdd.map(x=>{if(x.getAs("s_score").toString.toInt > 80) (x(1).toString,1) else (x(1).toString,0)}).reduceByKey(_+_).toDF("c_id","youliang")
val youxiu = scoreTableDF.rdd.map(x=>{if(x.getAs("s_score").toString.toInt > 90) (x(1).toString,1) else (x(1).toString,0)}).reduceByKey(_+_).toDF("c_id","youxiu")
val s1 = scoreTableDF.groupBy("c_id").agg("s_score"->"max","s_score"->"min","s_score"->"avg","s_score"->"count")
val frame18: DataFrame = s1.join(jige,"c_id").join(zhongdeng,"c_id").join(youliang,"c_id").join(youxiu,"c_id").withColumn("jgl",$"jige"/$"count(s_score)").withColumn("zdl",$"zhongdeng"/$"count(s_score)").withColumn("yll",$"youliang"/$"count(s_score)").withColumn("yxl",$"youxiu"/$"count(s_score)").drop("jige","zhongdeng","youliang","youxiu")
frame18.show()

19、按各科成绩进行排序，并显示排名

scoreTableDF
  .join(studentTableDF,"s_id")
  .selectExpr("*","row_number() over(partition by c_id order by s_score desc) rank")
  .show()

20、查询学生的总成绩并进行排名

 scoreTableDF.selectExpr("*","sum(s_score) over(partition by s_id) as sum_score")
      .drop("s_score","c_id")
      .distinct()
      .selectExpr("*","row_number() over(order by sum_score) as rank")
      .show()

21、查询不同老师所教不同课程平均分从高到低显示:

 scoreTableDF
      .groupBy("c_id")
      .avg("s_score")
      .join(
        courseTableDF.join(teacherTableDF,"t_id"),"c_id")
      .show()

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

scoreTableDF
  .selectExpr("*","row_number() over(partition by c_id order by s_score) as rank")
  .filter(x=>x.get(3).asInstanceOf[Integer] == 2 || x.get(3).asInstanceOf[Integer] == 3)
  .join(studentTableDF,"s_id")
  .show()

23.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

 val fenduan: DataFrame = scoreTableDF.rdd.map(x => {
      if (x.getAs("s_score").toString.toInt < 60) (x(1).toString, 1)
      else if (x.getAs("s_score").toString.toInt < 70) (x(1).toString, 2)
      else if (x.getAs("s_score").toString.toInt < 85) (x(1).toString, 3)
      else (x(1).toString, 4)
    }).toDF("c_id", "fenduan")
    fenduan.groupBy("c_id").count.as("f1")
      .join(fenduan.groupBy("c_id","fenduan").count.as("f2"),"c_id")
      .withColumn("rate",$"f2.count"/$"f1.count")
      .drop($"f1.count")
      .join(courseTableDF,"c_id")
      .show()

24、查询学生平均成绩及其名次

  scoreTableDF
      .groupBy("s_id")
      .avg("s_score")
      .selectExpr("*",s"row_number() over(order by 'avg(s_score)')")
      .show()

25、查询各科成绩前三名的记录

 scoreTableDF
      .selectExpr("*","row_number() over(partition by c_id order by s_score desc) num")
      .where("num<=3")
      .show()

26、查询每门课程被选修的学生数

scoreTableDF.groupBy("c_id").count().show()

27、查询出只有两门课程的全部学生的学号和姓名

scoreTableDF
  .groupBy("s_id")
  .count()
  .where("count=2")
  .join(studentTableDF,"s_id")
  .show()

28、查询男生、女生人数

studentTableDF.groupBy("s_sex").count().show()

29、查询名字中含有"风"字的学生信息

 studentTableDF.where("s_name like '%风%'").show()

30、查询同名同姓学生名单，并统计同名人数

studentTableDF.groupBy("s_name").count().where("count>1").show()

31、查询1990年出生的学生名单

studentTableDF.where("year(s_birth)=1990").show()

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

scoreTableDF.groupBy("c_id").avg("s_score").orderBy(desc("avg(s_score)"),asc("c_id")).show()

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:

scoreTableDF
  .groupBy("s_id")
  .avg("s_score")
  .where("avg(s_score) >= 85")
  .join(studentTableDF,"s_id")
  .show()

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数:

scoreTableDF
  .join(courseTableDF,"c_id")
  .where("s_score < 60 and c_name='数学'")
  .join(studentTableDF,"s_id")
  .show()

35、查询所有学生的课程及分数情况:

 scoreTableDF
      .join(studentTableDF,"s_id")
      .join(courseTableDF,"c_id")
      .show()

36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

  scoreTableDF
      .where("s_score>70")
      .join(studentTableDF,"s_id")
      .join(courseTableDF,"c_id")
      .show()

37.查询不及格的课程

scoreTableDF.where("s_score<60").join(studentTableDF,"s_id").show()

38.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

scoreTableDF.where("c_id=1 and s_score>=80").join(studentTableDF,"s_id").show()

39.求每门课程的学生人数

 scoreTableDF.groupBy("c_id").count().show()

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

 scoreTableDF
      .join(courseTableDF.join(teacherTableDF,"t_id"),"c_id")
      .where("t_name='张三'")
      .orderBy("s_score")
      .limit(1)
      .join(studentTableDF,"s_id")
      .show()

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

scoreTableDF.as("s1")
  .join(scoreTableDF.as("s2"),"s_id")
  .where("s1.s_score = s2.s_score and s1.c_id != s2.c_id")
  .show()

42、查询每门功成绩最好的前两名

 scoreTableDF
      .selectExpr("*","row_number() over(partition by c_id order by s_score desc) as rank")
      .where("rank <= 2")
      .join(studentTableDF,"s_id")
      .show()

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

scoreTableDF
  .groupBy("c_id")
  .count()
  .where("count>5")
  .orderBy($"count".desc)
  .orderBy("c_id")
  .show()

44、检索至少选修两门课程的学生学号

scoreTableDF.groupBy("s_id").count().where("count>2").drop("count").show()

45、查询选修了全部课程的学生信息

scoreTableDF
  .groupBy("s_id")
  .count()
  .where(s"count = ${courseTableDF.count()}")
  .join(studentTableDF,"s_id")
  .show()

46、查询各学生的年龄

studentTableDF.selectExpr("*","year(current_date)-year(s_birth)").show()

47、查询本周过生日的学生

unix_timestamp(current_date()) // 当前时间
cast( concat_ws('-',date_format(current_date(),'yyyy'),date_format(s_birth,'MM'),date_format(s_birth,'dd')) as date ),'yyyy-MM-dd') 将s_birth改成当前年份
studentTableDF
  .where(" unix_timestamp( cast( concat_ws('-',date_format(current_date(),'yyyy'),date_format(s_birth,'MM'),date_format(s_birth,'dd') ) as date ),'yyyy-MM-dd') between unix_timestamp(current_date()) and unix_timestamp(date_sub(next_day(current_date(),'MON'),1),'yyyy-MM-dd') ").show()

48、查询下周过生日的学生

 unix_timestamp(date_sub(next_day(current_date(),'MON'),1),'yyyy-MM-dd')   下周一
    unix_timestamp(date_add(next_day(current_date(),'MON'),6),'yyyy-MM-dd')   下周末
    studentTableDF
      .where(" unix_timestamp( cast( concat_ws('-',date_format(current_date(),'yyyy'),date_format(s_birth,'MM'),date_format(s_birth,'dd') ) as date ),'yyyy-MM-dd') between unix_timestamp(date_sub(next_day(current_date(),'MON'),1),'yyyy-MM-dd') and unix_timestamp(date_add(next_day(current_date(),'MON'),6),'yyyy-MM-dd') ").show()

49、查询本月过生日的学生

 studentTableDF.where("month(s_birth)=month( current_date() )").show()

50、查询下月过生日的学生

studentTableDF.where("month(s_birth)=month( current_date() ) +1 ").show()

50、查询12月份过生日的学生

studentTableDF.where("month(s_birth)=12").show()

serendipityLee

关注

3
点赞
踩
20

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录