package com.bdqn
import org.apache.spark.SparkContext
import org.apache.spark.sql.{DataFrame, SparkSession}
object LianXi50 {
def main(args: Array[String]): Unit = {
//TODO 创建一个SparkSession对象
val spark: SparkSession = SparkSession.builder()
.master("local[2]")
.appName("test5")
.enableHiveSupport()
.config("hive.metastore.uris", "thrift://cm:9083")
.getOrCreate()
val sc: SparkContext = spark.sparkContext
import spark.implicits._
val stuDF: DataFrame = spark.table("test.Student")
stuDF.printSchema()
val scoresDF: DataFrame = spark.table("test.scores")
scoresDF.printSchema()
val courseDF: DataFrame = spark.table("test.course")
courseDF.printSchema()
val teacherDF: DataFrame = spark.table("test.teacher")
teacherDF.printSchema()
//agg导包
import org.apache.spark.sql.functions._
//查询"01"课程比"02"课程成绩高的学生的信息及课程分数
// ((scoresDF.filter("cid=01").as("a"))
// .join(scoresDF.filter("cid=02").as("b"),$"a.sid"===$"b.sid"&&$"a.score">$"b.score")
// .join (stuDF.as("c"),$"c.stu"===$"a.sid")).select("a.sid","a.cid","a.score","c.sname","c.birthday","c.sex").show()
//查询"01"课程比"02"课程成绩低的学生的信息及课程分数
// ((scoresDF.filter("cid=01").as("a"))
// .join(scoresDF.filter("cid=02").as("b"),$"a.sid"===$"b.sid"&&$"a.score"<$"b.score")
// .join (stuDF.as("c"),$"c.stu"===$"a.sid")).select("a.sid","a.cid","a.score","c.sname","c.birthday","c.sex").show()
//查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
// scoresDF.groupBy(“sid”).agg(“score”->“avg”).filter(
"
a
v
g
(
s
c
o
r
e
)
"
>
=
60
)
.
a
s
(
"
a
"
)
.
j
o
i
n
(
s
t
u
D
F
.
a
s
(
"
c
"
)
,
"avg(score)">=60).as("a").join(stuDF.as("c"),
"avg(score)">=60).as("a").join(stuDF.as("c"),“c.stu”=
"
a
.
s
i
d
"
)
.
s
h
o
w
(
)
/
/
查
询
平
均
成
绩
小
于
60
分
的
同
学
的
学
生
编
号
和
学
生
姓
名
和
平
均
成
绩
/
/
s
c
o
r
e
s
D
F
.
g
r
o
u
p
B
y
(
"
s
i
d
"
)
.
a
g
g
(
"
s
c
o
r
e
"
−
>
"
a
v
g
"
)
.
f
i
l
t
e
r
(
"a.sid").show() //查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 // scoresDF.groupBy("sid").agg("score"->"avg").filter(
"a.sid").show()//查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩//scoresDF.groupBy("sid").agg("score"−>"avg").filter(“avg(score)”<60).as(“a”).join(stuDF.as(“c”),
"
c
.
s
t
u
"
=
=
=
"c.stu"===
"c.stu"===“a.sid”).show()
//查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
// scoresDF.groupBy(“sid”).agg(“score”->“sum”,“cid”->“count”).as(“a”).join(stuDF.as(“c”),
"
c
.
s
t
u
"
=
=
=
"c.stu"===
"c.stu"===“a.sid”).show()
//查询"李"姓老师的数量
// println(teacherDF.filter(
"
t
n
a
m
e
"
l
i
k
e
"
李
/
/
查
询
学
过
"
张
三
"
老
师
授
课
的
同
学
的
信
息
/
/
t
e
a
c
h
e
r
D
F
.
f
i
l
t
e
r
(
"
t
n
a
m
e
=
=
′
张
三
′
"
)
.
a
s
(
"
t
"
)
/
/
.
j
o
i
n
(
c
o
u
r
s
e
D
F
.
a
s
(
"
c
"
)
,
"tname" like "李%").count()) //查询学过"张三"老师授课的同学的信息 // teacherDF.filter("tname=='张三'").as("t") // .join(courseDF.as("c"),
"tname"like"李//查询学过"张三"老师授课的同学的信息//teacherDF.filter("tname==′张三′").as("t")//.join(courseDF.as("c"),“t.tid”=
"
c
.
t
i
d
"
)
/
/
.
j
o
i
n
(
s
c
o
r
e
s
D
F
.
a
s
(
"
s
"
)
,
"c.tid") // .join(scoresDF.as("s"),
"c.tid")//.join(scoresDF.as("s"),“s.cid”=
"
c
.
c
i
d
"
)
.
j
o
i
n
(
s
t
u
D
F
.
a
s
(
"
s
t
"
)
,
"c.cid").join(stuDF.as("st"),
"c.cid").join(stuDF.as("st"),“st.stu”=$“s.sid”).show()
//查询没学过"张三"老师授课的同学的信息
// (teacherDF.filter(“tname==‘张三’”).as(“t”)
// .join(courseDF.as(“c”), $“t.tid” === $“c.tid”)
// .join(scoresDF.as(“s”), $“s.cid” === $“c.cid”)).join(stuDF.as(“st”), $“st.stu” ===
"
s
.
s
i
d
"
)
.
f
i
l
t
e
r
(
"s.sid").filter(
"s.sid").filter("st.stu"isNull).show()
//查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
scoresDF.select(“sid”).filter(
"
c
i
d
"
=
=
=
"
01
"
)
.
i
n
t
e
r
s
e
c
t
(
s
c
o
r
e
s
D
F
.
s
e
l
e
c
t
(
"
s
i
d
"
)
.
f
i
l
t
e
r
(
"cid" === "01") .intersect(scoresDF.select("sid").filter(
"cid"==="01").intersect(scoresDF.select("sid").filter(“cid” === “02”))
.join(stuDF.select(“stu”, “sname”)).filter($“stu” ===
"
s
i
d
"
)
.
s
e
l
e
c
t
(
"
s
n
a
m
e
"
)
.
s
h
o
w
(
)
/
/
查
询
学
过
编
号
为
"
01
"
但
是
没
有
学
过
编
号
为
"
02
"
的
课
程
的
同
学
的
信
息
。
0
s
c
o
r
e
s
D
F
.
s
e
l
e
c
t
(
"
s
i
d
"
)
.
f
i
l
t
e
r
(
"sid") .select("sname").show() //查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。0 scoresDF.select("sid").filter(
"sid").select("sname").show()//查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。0scoresDF.select("sid").filter(“cid” === “01”).as(“s1”)
.join(scoresDF.select(“sid”).filter(
"
c
i
d
"
=
=
=
"
02
"
)
.
a
s
(
"
s
2
"
)
,
"cid" === "02").as("s2"),
"cid"==="02").as("s2"),“s1.sid”=
"
s
2.
s
i
d
"
,
"
l
e
f
t
"
)
.
f
i
l
t
e
r
(
"s2.sid","left").filter(
"s2.sid","left").filter("s2.sid"isNull)
.join(stuDF).filter($“s1.sid” === $“stu”).select(“sname”).show()
//查询没有学全所有课程的同学的信息
// scoresDF.groupBy(“sid”).agg(count(“sid”).as(“cn”)).filter(
//
"
c
n
"
<
c
o
u
r
s
e
D
F
.
s
e
l
e
c
t
(
"
c
n
a
m
e
"
)
.
c
o
u
n
t
(
)
)
/
/
.
j
o
i
n
(
s
t
u
D
F
)
.
f
i
l
t
e
r
(
"cn"<courseDF.select("cname").count()) // .join(stuDF).filter(
"cn"<courseDF.select("cname").count())//.join(stuDF).filter(“sid” ===
"
s
t
u
"
)
.
s
e
l
e
c
t
(
"
s
n
a
m
e
"
)
.
s
h
o
w
(
)
/
/
查
询
至
少
有
一
门
课
与
学
号
为
"
01
"
的
同
学
所
学
相
同
的
同
学
的
信
息
/
/
s
c
o
r
e
s
D
F
.
f
i
l
t
e
r
(
"stu").select("sname").show() //查询至少有一门课与学号为"01"的同学所学相同的同学的信息 // scoresDF.filter(
"stu").select("sname").show()//查询至少有一门课与学号为"01"的同学所学相同的同学的信息//scoresDF.filter(“sid”=“01”).select(
"
c
i
d
"
.
a
s
(
"
c
i
d
1
"
)
)
.
j
o
i
n
(
s
c
o
r
e
s
D
F
.
a
s
(
"
s
c
1
"
)
,
"cid".as("cid1")).join(scoresDF.as("sc1"),
"cid".as("cid1")).join(scoresDF.as("sc1"),“sc1.cid”=
"
c
i
d
1
"
)
/
/
.
j
o
i
n
(
s
t
u
D
F
)
.
f
i
l
t
e
r
(
"cid1") // .join(stuDF).filter(
"cid1")//.join(stuDF).filter(“sc1.sid” ===
"
s
t
u
"
)
.
s
e
l
e
c
t
(
"
s
n
a
m
e
"
)
.
d
i
s
t
i
n
c
t
(
)
.
s
h
o
w
(
)
/
/
查
询
和
"
01
"
号
的
同
学
学
习
的
课
程
完
全
相
同
的
其
他
同
学
的
信
息
使
用
g
r
o
u
p
c
o
n
c
a
t
函
数
和
g
r
o
u
p
b
y
/
/
s
c
o
r
e
s
D
F
.
f
i
l
t
e
r
(
"stu").select("sname").distinct().show() //查询和"01"号的同学学习的课程完全相同的其他同学的信息使用group_concat函数和group by // scoresDF.filter(
"stu").select("sname").distinct().show()//查询和"01"号的同学学习的课程完全相同的其他同学的信息使用groupconcat函数和groupby//scoresDF.filter(“sid” === “01”).as(“sc1”).join(scoresDF.as(“sc2”), $“sc1.cid” ===
"
s
c
2.
c
i
d
"
)
/
/
.
g
r
o
u
p
B
y
(
"
s
c
2.
s
i
d
"
)
.
a
g
g
(
c
o
u
n
t
(
"
s
c
2.
s
i
d
"
)
.
a
s
(
"
c
n
"
)
)
.
f
i
l
t
e
r
(
"sc2.cid") // .groupBy("sc2.sid").agg(count("sc2.sid").as("cn")).filter(
"sc2.cid")//.groupBy("sc2.sid").agg(count("sc2.sid").as("cn")).filter(“cn” < scoresDF.filter(
"
s
i
d
"
=
=
=
"
01
"
)
.
s
e
l
e
c
t
(
"
c
i
d
"
)
/
/
.
c
o
u
n
t
(
)
)
.
s
e
l
e
c
t
(
"sid" === "01").select("cid") // .count()).select(
"sid"==="01").select("cid")//.count()).select(“sid”.as(“sid1”)).join(stuDF).filter($“sid1” ===
"
s
t
u
"
)
.
s
e
l
e
c
t
(
"
s
n
a
m
e
"
)
.
s
h
o
w
(
)
/
/
查
询
没
学
过
"
张
三
"
老
师
讲
授
的
任
一
门
课
程
的
学
生
姓
名
/
/
t
e
a
c
h
e
r
D
F
.
a
s
(
"
t
e
"
)
.
f
i
l
t
e
r
(
"stu").select("sname").show() //查询没学过"张三"老师讲授的任一门课程的学生姓名 // teacherDF.as("te").filter(
"stu").select("sname").show()//查询没学过"张三"老师讲授的任一门课程的学生姓名//teacherDF.as("te").filter(“tname”=“张三”)
// .join(courseDF.as(“co”),
"
c
o
.
t
i
d
"
=
=
=
"co.tid"===
"co.tid"===“te.tid”)
// .join(scoresDF.as(“sc”),
"
s
c
.
c
i
d
"
=
=
=
"sc.cid"===
"sc.cid"===“co.cid”).distinct()
// .join(stuDF,
"
s
t
u
"
=
=
=
"stu"===
"stu"===“sc.sid”,“right”).filter(
"
s
t
u
"
.
i
s
N
u
l
l
)
/
/
.
s
e
l
e
c
t
(
"stu".isNull) // .select(
"stu".isNull)//.select(“sid”,
"
s
n
a
m
e
"
)
.
s
h
o
w
/
/
查
询
两
门
及
其
以
上
不
及
格
课
程
的
同
学
的
学
号
，
姓
名
及
其
平
均
成
绩
/
/
s
c
o
r
e
s
D
F
.
f
i
l
t
e
r
(
"sname").show //查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 // scoresDF.filter(
"sname").show//查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩//scoresDF.filter(“score”<60).groupBy(“sid”).agg(count(“cid”).as(“cn”)).filter(
"
c
n
"
>
=
2
)
.
s
e
l
e
c
t
(
"cn">=2).select(
"cn">=2).select(“sid”.as(“sid1”),
"
c
n
"
)
/
/
.
j
o
i
n
(
s
c
o
r
e
s
D
F
.
g
r
o
u
p
B
y
(
"
s
i
d
"
)
.
a
g
g
(
a
v
g
(
"
s
c
o
r
e
"
)
.
a
s
(
"
a
v
g
"
)
)
.
s
e
l
e
c
t
(
"cn") // .join(scoresDF.groupBy("sid").agg(avg("score").as("avg")).select(
"cn")//.join(scoresDF.groupBy("sid").agg(avg("score").as("avg")).select(“sid”.as(“sid2”),
"
a
v
g
"
)
,
"avg"),
"avg"),“sid1”=
"
s
i
d
2
"
)
/
/
.
j
o
i
n
(
s
t
u
D
F
,
"sid2") // .join(stuDF,
"sid2")//.join(stuDF,“sid1”=
"
s
t
u
"
)
.
s
e
l
e
c
t
(
"
s
n
a
m
e
"
,
"
c
n
"
,
"
a
v
g
"
)
.
s
h
o
w
(
)
/
/
检
索
"
01
"
课
程
分
数
小
于
60
，
按
分
数
降
序
排
列
的
学
生
信
息
/
/
s
c
o
r
e
s
D
F
.
f
i
l
t
e
r
(
"stu").select("sname","cn","avg").show() //检索"01"课程分数小于60，按分数降序排列的学生信息 // scoresDF.filter(
"stu").select("sname","cn","avg").show()//检索"01"课程分数小于60，按分数降序排列的学生信息//scoresDF.filter(“cid”=“01”).filter(
"
s
c
o
r
e
"
<
60
)
.
j
o
i
n
(
s
t
u
D
F
,
"score"<60).join(stuDF,
"score"<60).join(stuDF,“stu”=
"
s
i
d
"
)
/
/
.
s
o
r
t
(
"sid") // .sort(
"sid")//.sort(“score”.desc).select(
"
s
n
a
m
e
"
,
"sname",
"sname",“sid”,
"
s
c
o
r
e
"
)
.
s
h
o
w
(
)
/
/
按
平
均
成
绩
从
高
到
低
显
示
所
有
学
生
的
所
有
课
程
的
成
绩
以
及
平
均
成
绩
/
/
s
c
o
r
e
s
D
F
.
g
r
o
u
p
B
y
(
"
s
i
d
"
)
.
p
i
v
o
t
(
"
c
i
d
"
)
.
s
u
m
(
"
s
c
o
r
e
"
)
/
/
.
j
o
i
n
(
s
c
o
r
e
s
D
F
.
g
r
o
u
p
B
y
(
"
s
i
d
"
)
.
a
g
g
(
a
v
g
(
"
s
c
o
r
e
"
)
.
a
s
(
"
a
v
g
"
)
)
.
s
e
l
e
c
t
(
"score").show() //按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 // scoresDF.groupBy("sid").pivot("cid").sum("score") // .join(scoresDF.groupBy("sid").agg(avg("score").as("avg")).select(
"score").show()//按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩//scoresDF.groupBy("sid").pivot("cid").sum("score")//.join(scoresDF.groupBy("sid").agg(avg("score").as("avg")).select(“sid”.as(“sid1”), $“avg”).as(“s1”),
// $“sid” ===
"
s
i
d
1
"
)
.
s
o
r
t
(
"sid1").sort(
"sid1").sort(“avg”.desc).show()
//查询各科成绩最高分、最低分和平均分：以如下形式显示：课程id，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
// scoresDF.groupBy($"cid").agg(max($"score"),min($"score"),avg($"score")
// ,count(when($"score">"60",$"score"))/count($"score")
// ,count(when($"score">=70.0&&$"score"<80.0,$"score"))/count($"score")
// ,count(when($"score">=80.0&&$"score"<90.0,$"score"))/count($"score")
// ,count(when($"score">=90.0,$"score"))/count($"score")).show()
//按各科成绩进行排序，并显示排名
import org.apache.spark.sql.expressions.Window
val w = Window
// scoresDF.sort($"cid",$"score".desc).withColumn("rank",row_number().over(w.partitionBy($"cid").orderBy($"score".desc))).show()
//查询学生的总成绩并进行排名
// scoresDF.groupBy("sid").agg(sum("score").as("sum")).withColumn("rank",row_number().over(w.orderBy($"sum".desc))).show
//查询不同老师所教不同课程平均分从高到低显示
// scoresDF.as("sc").join(courseDF.as("co"),$"co.cid"===$"sc.cid")
// .join(teacherDF.as("te"),$"te.tid"===$"co.cid").groupBy("te.tname","sc.cid")
// .avg("sc.score").orderBy(avg("sc.score").desc).show()
//查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
// scoresDF.withColumn("rank",row_number().over(w.partitionBy("cid").orderBy($"score".desc))).where($"rank"===2||$"rank"===3)
// .join(stuDF,$"stu"===$"sid").show()
//统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
// scoresDF.groupBy($"cid").agg(
// count(when($"score"<=60,1)) as "[0-60]人数",
// round(count(when($"score"<=60,1))/count($"sid"),2) as "[0-60]%",
// count(when($"score">60&&$"score"<=70,1)) as "[60-70]人数",
// round(count(when($"score">60&&$"score"<=70,1))/count($"sid"),2) as "[60-70]%",
// count(when($"score">70&&$"score"<=85,1)) as "[70-85]人数",
// round(count(when($"score">70&&$"score"<=85,1))/count($"sid"),2) as "[70-85]%",
// count(when($"score">85&&$"score"<=100,1)) as "[85-100]人数",
// round(count(when($"score">85&&$"score"<=100,1))/count($"sid"),2) as "[85-100]%"
// ).join(courseDF.select($"cid" as "cid2",$"cname"),$"cid"===$"cid2")
// .show
//查询学生平均成绩及其名次自己对自己左交，查看比自己分数高的有几个
// scoresDF.groupBy("sid").avg("score").as("s1")
// .join(scoresDF.groupBy("sid").avg("score").as("s2"), $"s1.avg(score)" < $"s2.avg(score)", "left")
// .filter($"s2.sid".isNotNull).groupBy("s1.sid").count().show()
//查询各科成绩前三名的记录
// scoresDF.withColumn("rank",row_number().over(w.partitionBy("cid").orderBy("score"))).where($"rank"<=3).show()
//查询每门课程被选修的学生数
// scoresDF.groupBy("cid").agg(count("sid")).show()
//查询出只有两门课程的全部学生的学号和姓名
// scoresDF.groupBy("sid").agg(count("cid") as "cn").where($"cn"===2).join(stuDF.select($"stu",$"sname"), $"sid"===$"stu").show()
//查询男生、女生人数
// stuDF.groupBy("sex").agg(count("stu")).show()
//查询名字中含有"风"字的学生信息
// stuDF.where($"sname"like("%风%")).show()
//查询同名同性学生名单，并统计同名人数
// stuDF.withColumn("same",count("sname").over(w.partitionBy("sname")))
// .filter($"same"===2).groupBy("sex").count().filter($"count">1).show()
//查询1990年出生的学生名单(注：student表中sage列的类型是datetime)
// stuDF.where($"birthday".contains("1990")).show()
//查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
// scoresDF.groupBy("c").agg(avg("score") as "avg")
// .withColumn("rank", row_number().over(w.orderBy($"avg".desc, $"c"))).show()
//查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
// scoresDF.groupBy("sid").agg(avg("score") as "avg")
// .where($"avg" > 80)
// .join(stuDF, $"stu" === $"sid")
// .select("sid", "sname", "avg")
// .show()
//查询课程名称为"数学"，且分数低于60的学生姓名和分数
// scoresDF.as(“sc”).join(courseDF.as(“co”),
"
c
o
.
c
i
d
"
=
=
=
"co.cid"===
"co.cid"===“sc.cid”)
// .filter(
"
c
o
.
c
n
a
m
e
"
=
=
=
"
数
学
"
)
.
f
i
l
t
e
r
(
"co.cname"==="数学").filter(
"co.cname"==="数学").filter(“sc.score”<60)
// .join(stuDF,
"
s
t
u
"
=
=
=
"stu"===
"stu"===“sid”)
// .select(“sname”,“sc.score”)
// .show()
//查询所有学生的课程及分数情况
// scoresDF.join(stuDF,
"
s
t
u
"
=
=
=
"stu"===
"stu"===“sid”).select(
"
s
i
d
"
,
"sid",
"sid",“sname”,
"
c
i
d
"
,
"cid",
"cid",“score”).orderBy(
"
s
i
d
"
,
"sid",
"sid",“cid”).show()
//查询任何一门课程成绩在70分以上的姓名、课程名称和分数
// scoresDF.as(“sc”).filter(
"
s
c
.
s
c
o
r
e
"
>
70
)
/
/
.
j
o
i
n
(
s
t
u
D
F
.
a
s
(
"
s
t
"
)
,
"sc.score">70) // .join(stuDF.as("st"),
"sc.score">70)//.join(stuDF.as("st"),“st.stu”=
"
s
c
.
s
i
d
"
)
/
/
.
j
o
i
n
(
c
o
u
r
s
e
D
F
.
a
s
(
"
c
o
"
)
,
"sc.sid") // .join(courseDF.as("co"),
"sc.sid")//.join(courseDF.as("co"),“co.cid”=
"
s
c
.
c
i
d
"
)
/
/
.
s
e
l
e
c
t
(
"
s
c
.
s
i
d
"
,
"
s
t
.
s
n
a
m
e
"
,
"
c
o
.
c
n
a
m
e
"
,
"
s
c
.
s
c
o
r
e
"
)
/
/
.
s
h
o
w
(
)
/
/
查
询
不
及
格
的
课
程
/
/
s
c
o
r
e
s
D
F
.
a
s
(
"
s
c
"
)
.
f
i
l
t
e
r
(
"sc.cid") // .select("sc.sid","st.sname","co.cname","sc.score") // .show() //查询不及格的课程 // scoresDF.as("sc").filter(
"sc.cid")//.select("sc.sid","st.sname","co.cname","sc.score")//.show()//查询不及格的课程//scoresDF.as("sc").filter(“sc.score”<60)
// .join(courseDF.as(“ce”),
"
c
e
.
c
i
d
"
=
=
=
"ce.cid"===
"ce.cid"===“sc.cid”)
// .select(“sc.sid”,“ce.cname”,“sc.score”)
// .show()
//查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
// scoresDF.filter(
"
c
i
d
"
=
=
=
"
01
"
)
.
f
i
l
t
e
r
(
"cid"==="01").filter(
"cid"==="01").filter(“score”>=80)
// .join(stuDF,
"
s
i
d
"
=
=
=
"sid"===
"sid"===“stu”)
// .select(“sid”,“sname”,“score”)
// .show()
//求每门课程的学生人数
// scoresDF.groupBy(“cid”).agg(countDistinct(“sid”)).show()
//查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
// scoresDF.as(“sc”).join(courseDF.as(“ce”), $“sc.cid” === $“ce.cid”)
// .join(teacherDF.as(“t”), $“t.tid” === $“ce.tid” && $“t.tname” === “张三”)
// .join(stuDF.as(“st”), $“st.stu” ===
"
s
c
.
s
i
d
"
)
/
/
.
s
e
l
e
c
t
(
"
s
t
.
s
n
a
m
e
"
,
"
s
c
.
s
c
o
r
e
"
)
/
/
.
o
r
d
e
r
B
y
(
"sc.sid") // .select("st.sname", "sc.score") // .orderBy(
"sc.sid")//.select("st.sname","sc.score")//.orderBy(“sc.score”.desc)
// .limit(1).show
//、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
// scoresDF.join(scoresDF.select(
"
s
i
d
"
a
s
"
s
1
"
,
"sid"as "s1",
"sid"as"s1","cid"as “c1”,
"
s
c
o
r
e
"
a
s
"
s
c
o
r
e
1
"
)
,
"score"as "score1"),
"score"as"score1"),“score”===KaTeX parse error: Expected 'EOF', got '&' at position 9: "score1"&̲&“cid”!=
"
c
1
"
)
.
s
h
o
w
(
)
/
/
、
查
询
每
门
功
成
绩
最
好
的
前
两
名
同
19
/
/
s
c
o
r
e
s
D
F
.
w
i
t
h
C
o
l
u
m
n
(
"
r
a
n
k
"
,
r
o
w
n
u
m
b
e
r
(
)
.
o
v
e
r
(
w
.
p
a
r
t
i
t
i
o
n
B
y
(
"
c
i
d
"
)
.
o
r
d
e
r
B
y
(
"c1").show() //、查询每门功成绩最好的前两名 同19 // scoresDF.withColumn("rank",row_number().over(w.partitionBy("cid").orderBy(
"c1").show()//、查询每门功成绩最好的前两名同19//scoresDF.withColumn("rank",rownumber().over(w.partitionBy("cid").orderBy(“score”.desc))).filter(
"
r
a
n
k
"
<
=
2
)
.
s
h
o
w
/
/
统
计
每
门
课
程
的
学
生
选
修
人
数
（
超
过
5
人
的
课
程
才
统
计
）
。
要
求
输
出
课
程
号
和
选
修
人
数
，
查
询
结
果
按
人
数
降
序
排
列
，
若
人
数
相
同
，
按
课
程
号
升
序
排
列
/
/
s
c
o
r
e
s
D
F
.
g
r
o
u
p
B
y
(
"
c
i
d
"
)
.
a
g
g
(
c
o
u
n
t
D
i
s
t
i
n
c
t
(
"
s
i
d
"
)
a
s
"
c
n
"
)
.
f
i
l
t
e
r
(
"rank"<=2).show //统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 // scoresDF.groupBy("cid").agg(countDistinct("sid")as "cn").filter(
"rank"<=2).show//统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列//scoresDF.groupBy("cid").agg(countDistinct("sid")as"cn").filter(“cn”>5)
// .withColumn(“rank”,row_number().over(w.orderBy(
"
c
n
"
.
d
e
s
c
,
"cn".desc,
"cn".desc,“cid”.desc))).show
//检索至少选修两门课程的学生学号
// scoresDF.groupBy(“sid”).agg(countDistinct(“cid”)as “cn”).filter(
"
c
n
"
>
=
2
)
.
s
h
o
w
(
)
/
/
查
询
选
修
了
全
部
课
程
的
学
生
信
息
/
/
s
c
o
r
e
s
D
F
.
g
r
o
u
p
B
y
(
"
s
i
d
"
)
.
a
g
g
(
c
o
u
n
t
D
i
s
t
i
n
c
t
(
"
c
i
d
"
)
a
s
"
c
n
"
)
.
f
i
l
t
e
r
(
"cn">=2).show() //查询选修了全部课程的学生信息 // scoresDF.groupBy("sid").agg(countDistinct("cid")as "cn").filter(
"cn">=2).show()//查询选修了全部课程的学生信息//scoresDF.groupBy("sid").agg(countDistinct("cid")as"cn").filter(“cn”===courseDF.distinct().count()).show()
//查询各学生的年龄
stuDF.withColumn(“age”,year(current_date())-year($“birthday”)).show()
//查询本周过生日的学生
// stuDF.filter(“weekofyear(birthday)==weekofyear(current_date)”).show()
//查询下周过生日的学生
// stuDF.filter(“weekofyear(birthday)==weekofyear(current_date)+1”).show()
//查询本月过生日的学生
// stuDF.filter(“MONTH(birthday)==MONTH(current_date)”).show()
//查询下月过生日的学生
// stuDF.filter(“MONTH(birthday)==MONTH(current_date)+1”).show()
spark.stop()
}
}
5577
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
