使用spark sql算子练习mysql经典50道题

一、mysql经典50道题

学生表 Student

student_id	student_name	birth	sex
1	赵雷	1990-01-01	男
2	钱电	1990-12-21	男
3	孙风	1990-05-20	男
4	李云	1990-08-06	男
5	周梅	1991-12-01	女
6	吴兰	1992-03-01	女
7	郑竹	1989-07-01	女
8	王菊	1990-01-20	女

课程表 Course

course_id	course_name	teacher_id
1	语文	2
2	数学	1
3	英语	3

教师表 Teacher

teacher_id	teacher_name
1	张三
2	李四
3	王五

成绩表 Score

student_id	course_id	score
1	1	80
1	2	90
1	3	99
2	1	70
2	2	60
2	3	80
3	1	80
3	2	80
3	3	80
4	1	50
4	2	30
4	3	20
5	1	76
5	2	87
6	1	31
6	3	34
7	2	89
7	3	98

查询问题：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

28、查询男生、女生人数:

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

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

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

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

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

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

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

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

37、查询课程不及格的学生:

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

39、求每门课程的学生人数:

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

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

42、查询每门课程成绩最好的前三名:

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

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

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

46、查询各学生的年龄(周岁):

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

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

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

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

二、使用spark sql查询50道题

2.1 创建dataFrame

在 spark shell 命令行操作：

学生表：

//创建样例类
case class Student(student_id:Int,student_name:String,birth:String,sex:String)
//创建RDD
val rdd1 = sc.makeRDD(Array(
(1 , "赵雷" , "1990-01-01" , "男"),
(2 , "钱电" , "1990-12-21" , "男"),
(3 , "孙风" , "1990-05-20" , "男"),
(4 , "李云" , "1990-08-06" , "男"),
(5 , "周梅" , "1991-12-01" , "女"),
(6 , "吴兰" , "1992-03-01" , "女"),
(7 , "郑竹" , "1989-07-01" , "女"),
(8 , "王菊" , "1990-01-20" , "女")
))
//创建dataFrame
val studentDF = rdd1.map(x=>Student(x._1,x._2,x._3,x._4)).toDF

课程表：

//创建样例类
case class Course(course_id:Int,course_name:String,teacher_id:Int)
//创建RDD
val rdd2 = sc.makeRDD(Array(
(1 , "语文" , 2),
(2 , "数学" , 1),
(3 , "英语" , 3)
))
//创建dataFrame
val courseDF = rdd2.map(x=>Course(x._1,x._2,x._3)).toDF

教师表：

//创建样例类
case class Teacher(teacher_id:Int,teacher_name:String)
//创建RDD
val rdd3 = sc.makeRDD(Array(
(1 , "张三"),
(2 , "李四"),
(3 , "王五")
))
//创建dataFrame
val teacherDF = rdd3.map(x=>Teacher(x._1,x._2)).toDF

成绩表：

//创建样例类
case class Score(student_id:Int,course_id:Int,score:Int)
//创建RDD
val rdd4 = sc.makeRDD(Array(
(1 , 1 , 80),
(1 , 2 , 90),
(1 , 3 , 99),
(2 , 1 , 70),
(2 , 2 , 60),
(2 , 3 , 80),
(3 , 1 , 80),
(3 , 2 , 80),
(3 , 3 , 80),
(4 , 1 , 50),
(4 , 2 , 30),
(4 , 3 , 20),
(5 , 1 , 76),
(5 , 2 , 87),
(6 , 1 , 31),
(6 , 3 , 34),
(7 , 2 , 89),
(7 , 3 , 98)
))
//创建dataFrame
val scoreDF = rdd4.map(x=>Score(x._1,x._2,x._3)).toDF

2.2 查询

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

scoreDF.as("s1").join(scoreDF.as("s2"),"student_id").filter("s1.course_id = 1 and s2.course_id = 2 and s1.score > s2.score").join(studentDF,"student_id").show

---

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

scoreDF.as("s1").join(scoreDF.as("s2"),"student_id").filter("s1.course_id = 1 and s2.course_id = 2 and s1.score < s2.score").join(studentDF,"student_id").show

---

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

scoreDF.as("s1").groupBy("student_id").avg("score").withColumnRenamed("avg(score)","avg_score").filter("avg_score >= 60").join(studentDF,"student_id").show

或者

scoreDF.as("s1").groupBy("student_id").avg("score").filter($"avg(score)" >=60).join(studentDF,"student_id").show

注： filter（） 中的过滤条件如果是字段名，需要加双引号括起来，如果接聚合函数如avg() 需要加 $ ，且判断条件就不需要在用双引号括起来了

---

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

scoreDF.as("s1").groupBy("student_id").avg("score").withColumnRenamed("avg(score)","avg_score").join(studentDF,Seq("student_id"),"right_outer").where((col("avg_score") < 60) || (col("avg_score").isNull)).show

注：

• 过滤字段where格式： where( (col(“字段名 “) <… ) || (col(” 字段名”).isNull ))
• 如果是内连接 left join 或者 right join，格式为： join( studentDF, Seq(“关联的字段名”) ,“left_outer / right_outer” ) ，Seq的每个元素都会被包装成一个Row

---

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

scoreDF.as("s1").groupBy("student_id").count.withColumnRenamed("count","count_course").show

②查询所有课程总成绩：

scoreDF.as("s1").groupBy("student_id").sum("score").show

③连表查询 学生编号+学生姓名+选课总数+所有课程的总成绩：

studentDF.as("s1").join(scoreDF.as("s2").groupBy("student_id").count.withColumnRenamed("count","count_course"),Seq("student_id"),"left_outer").join(scoreDF.as("s3").groupBy("student_id").sum("score"),Seq("student_id"),"left_outer").show

注：

• count格式：只能使用.count ，不能使用count(“字段名”)
• 因为count之后不能再接聚合函数如 .sum() ，所以要分开写再联表查询
• 因为是要查询所有的学生，但却有一个学生 王菊 什么都没学，成绩表里面查无此人，但是也不能把她丢了，所以此处关联须以学生表为主表，left join 成绩表

---

6、查询"李"姓老师的数量:
查询姓“李”老师的信息：

teacherDF.where("teacher_name like '李%'").show

查询姓“李”老师的数量：

teacherDF.where("teacher_name like '李%'").select("teacher_id").count

---

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

studentDF.join(scoreDF,"student_id").join(courseDF,"course_id").join(teacherDF,"teacher_id").filter("teacher_name = '张三'").show

---

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

studentDF.join(scoreDF.join(courseDF,"course_id").join(teacherDF,"teacher_id"),Seq("student_id"),"left_outer").where("teacher_name != '张三' or teacher_name is null").show

注：

• where查询中字段为空的格式也可以为 where(“teacher_name is null”)
• 左右内连接嵌套子查询的格式为 DF1.join(DF2.join(DF3,"…").join(DF4,"…"),Seq("…"),“left_outer / right_outer”)

---

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

学过"01"课程的学生:

scoreDF.filter("course_id = 1")

学过"02"课程的学生:

scoreDF.filter("course_id = 2")

学过编号为"01"并且也学过编号为"02"的课程的同学的信息:

studentDF.join(scoreDF.filter("course_id = 1"),"student_id").join(scoreDF.filter("course_id = 2"),"student_id").show

---

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

学过课程"01"的学生：

scoreDF.where("course_id == 1").as("s3")show

学过课程"02"的学生：

scoreDF.where("course_id == 2").as("s1")show

没学过"02"课程的学生:
(需关联学生表，以学生表为主表，左连接s1，当course_id为空时，即为该学生没学过"02"课程)

studentDF.join(scoreDF.where("course_id = 2"),Seq("student_id"),"left_outer").as("s2").where("s2.course_id is null").show

没学过"02"课程的学生但是学过"01"的学生：

studentDF.join(scoreDF.where("course_id = 2"),Seq("student_id"),"left_outer").as("s2").where("s2.course_id is null").join(scoreDF.where("course_id = 1"),"student_id").show

---

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

总课程数：

courseDF.select("course_id").count

学生选课数：

scoreDF.groupBy("student_id").count.show

简单的写法，选课数量<3的学生信息(有一个学生没选课，也要算上)：

studentDF.join(scoreDF.groupBy("student_id").count.as("s1"),Seq("student_id"),"left_outer").where("s1.count < 3 or s1.count is null").show

规范的写法，假设不知道一共有几门课：

studentDF.join(scoreDF.groupBy("student_id").count.as("s1"),Seq("student_id"),"left_outer").where(s"s1.count != ${courseDF.select("course_id").count} or s1.count is null").show

注： where语句中再接查询语句，格式： where(s" ${courseDF.select(“course_id”).count} ")

---

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

"01"同学所学课程信息：

scoreDF.select("course_id").where("student_id = 1").show

查询至少有一门课与学号为"01"的同学所学相同的同学的学号：

studentDF.join(scoreDF,"student_id").as("a").join(scoreDF.select("course_id").where("student_id = 1").as("b"),"course_id").as("c").select("student_id").distinct.where("student_id !=1").show

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

studentDF.join(scoreDF,"student_id").as("a").join(scoreDF.select("course_id").where("student_id = 1").as("b"),"course_id").as("c").select("student_id").distinct.where("student_id !=1").join(studentDF,"student_id").show

---

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

查询"01"号同学的课程信息：

scoreDF.select("course_id").where("student_id=1").show

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

筛选条件where(课程名 = 01.课程名 and 课程数 = 01.课程数 and student_id != 1)

studentDF.join(scoreDF,"student_id").as("a").join(scoreDF.where("student_id=1").as("b"),"course_id").groupBy("a.student_id").count.where(s"count = ${scoreDF.where("student_id=1").count} and a.student_id !=1").join(studentDF,"student_id").show

---

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

查询张三老师教授的课程：

teacherDF.where("teacher_name='张三'").join(courseDF,"teacher_id").show

查询所有学生的课程信息：

studentDF.join(scoreDF,"student_id").join(courseDF,"course_id").show

查询以学过张三老师课程的学生信息为主表，筛选出学过张三老师课的学生，在 right join完整的学生表，当左边的学生表为空时，此时就能找到没学过张三老师课的学生：

studentDF.join(scoreDF,"student_id").join(courseDF,"course_id").join(teacherDF.where("teacher_name='张三'"),"teacher_id").as("a").select("student_id").join(studentDF.as("b"),Seq("student_id"),"right_outer").where("a.student_id is null").select("student_name").show

---

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

思路：成绩表查询不及格的学生，不及格数>=2，找到学号信息，在join一次成绩表，可以查询成绩，平均成绩，在join学生表，查询学生姓名

scoreDF.where("score<60").groupBy("student_id").count.where("count>=2").join(scoreDF,"student_id").groupBy("student_id").avg("score").join(studentDF,"student_id").show

注：groupBy两个字段的写法：groupBy(“student_id”,“course_id”)

---

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

思路：查询"01"课程的学生信息，right join 学生表，关联条件为 成绩<60或者成绩为空

scoreDF.where("course_id = 1").join(studentDF,Seq("student_id"),"right_outer").where("score < 60 or score is null").orderBy($"score".desc).show

---

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

studentDF.join(scoreDF,Seq("student_id"),"left_outer").groupBy("student_id").avg("score").join(studentDF.join(scoreDF,"student_id"),Seq("student_id"),"left_outer").orderBy($"avg(score)".desc).show

---

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

查询及格率：

val jige = scoreDF.rdd.map(x=>{if(x.getAs("score").toString.toInt > 60) (x(1).toString,1) else (x(1).toString,0)}).reduceByKey(_+_).toDF("course_id","jige")

查询中等率：

val zhongdeng = scoreDF.rdd.map(x=>{if(x.getAs("score").toString.toInt > 70) (x(1).toString,1) else (x(1).toString,0)}).reduceByKey(_+_).toDF("course_id","zhongdeng")

查询优良率：

val youliang = scoreDF.rdd.map(x=>{if(x.getAs("score").toString.toInt > 80) (x(1).toString,1) else (x(1).toString,0)}).reduceByKey(_+_).toDF("course_id","youliang")

查询优秀率：

val youxiu = scoreDF.rdd.map(x=>{if(x.getAs("score").toString.toInt > 90) (x(1).toString,1) else (x(1).toString,0)}).reduceByKey(_+_).toDF("course_id","youxiu")

查询最高分、最低分和平均分：

val s1 = scoreDF.groupBy("course_id").agg("score"->"max","score"->"min","score"->"avg","score"->"count")

联表：

s1.join(jige,"course_id").join(zhongdeng,"course_id").join(youliang,"course_id").join(youxiu,"course_id").withColumn("jgl",col("jige")/col("count(score)")).withColumn("zdl",col("zhongdeng")/col("count(score)")).withColumn("yll",col("youliang")/col("count(score)")).withColumn("yxl",col("youxiu")/col("count(score)")).drop("jige","zhongdeng","youliang","youxiu").show

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

20、查询学生的总成绩并进行排名:
简单的写法：

studentDF.join(scoreDF,"student_id").groupBy("student_id").sum("score").orderBy($"sum(score)".desc).show

显示排名：

scoreDF.selectExpr("*","sum(score) over(partition by student_id) sum_score").dropDuplicates("student_id","sum_score").selectExpr("*","row_number() over(order by sum_score desc) rank").show

21、查询不同老师所教不同课程平均分从高到低显示:
22、查询所有课程的成绩第2 名到第3 名的学生信息及该课程成绩:
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所
占百分比
24、查询学生平均成绩及其名次:
25、查询各科成绩前三名的记录
26、查询每门课程被选修的学生数:
27、查询出只有两门课程的全部学生的学号和姓名:
28、查询男生、女生人数:
29、查询名字中含有"风"字的学生信息:
30、查询同名同性学生名单，并统计同名人数:

31、查询1990年出生的学生名单:
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数:
35、查询所有学生的课程及分数情况:
36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
37、查询课程不及格的学生:
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
39、求每门课程的学生人数:
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩:
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
42、查询每门课程成绩最好的前三名:
43、统计每门课程的学生选修人数（超过5人的课程才统计）要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
44、检索至少选修两门课程的学生学号:
45、查询选修了全部课程的学生信息:
46、查询各学生的年龄(周岁):
47、查询本周过生日的学生:
48、查询下周过生日的学生:
49、查询本月过生日的学生:
50、查询12月份过生日的学生: