8.SparkSQL综合作业

# 创建RDD url = "file:///home/hadoop/sc.txt" rdd = sc.textFile(url).map(lambda x:x.split(",")) scm = rdd.map(lambda a:[a[0],a[1],int(a[2])]) scm.cache() scm.take(3) # 并转换得到DataFrame from pyspark.sql import Row df = spark.createDataFrame(rdd.map(lambda x:Row(name=x[0],course=x[1],grade=int(x[2])))) # 观察 df df.show() df.printSchema() # 注册为临时表 df.createOrReplaceTempView("sc") # 观察临时表 spark.sql("select * from sc").show() # 导包 from pyspark.sql.functions import * # 题目 # 1.总共有多少学生？ scm.map(lambda a:a[0]).distinct().count() df.select(df.name).distinct().count() spark.sql("select count(distinct name) from sc").show() # 2.总共开设了哪些课程？ scm.map(lambda a:a[1]).distinct().collect() df.select(df.course).distinct().show() spark.sql("select distinct course from sc").show() # 3.每个学生选修了多少门课？ name = scm.map(lambda a:(a[0],(a[1],a[2]))) name.countByKey() df.groupBy(df.name).count().show() spark.sql("select name, count(course) from sc group by name").show() # 4.每门课程有多少个学生选？ name.values().countByKey() df.groupBy(df.course).count().show() spark.sql("select course, count(name) from sc group by course").show() # 5.每门课程>95分的学生人数 name.values().filter(lambda a:a[1]>95).countByKey() df.filter(df.grade>95).groupBy(df.course).count().show() spark.sql("select course,count(*) from sc where grade>95 group by course").show() # 6.课程'Python'有多少个100分？ scm.filter(lambda a:a[1]=='Python').filter(lambda a:a[2]==100).count() df.filter((df.course=='Python') & (df.grade==100)).count() spark.sql("select count(*) from sc where course='Python' and grade=100").show() # 改题目了，这个是旧题目 # 7.Tom选修了几门课？每门课多少分？ name.lookup('Tom') len(name.lookup('Tom')) df.select(df.course, df.grade).filter(df.name=='Tom').count() df.select(df.course, df.grade).filter(df.name=='Tom').show() spark.sql("select count(*) from sc where name='Tom'").show() spark.sql("select course, grade from sc where name='Tom'").show() # 7.Tom哪几门课不及格？ scm.filter(lambda a:a[0]=='Tom').filter(lambda a:a[2]<60).map(lambda a:a[1]).collect() df.select(df.course).filter((df.name=='Tom')&(df.grade<60)).show() spark.sql("select course from sc where name='Tom' and grade<60").show() # 8.Tom的成绩按分数大小排序。 name.filter(lambda a:a[0]=='Tom').values().sortBy(lambda a:a[1],False).map(lambda a:a[1]).collect() df.select(df.grade).orderBy(df.grade).filter(df.name=='Tom').show() spark.sql("select grade from sc where name='Tom' order by grade asc").show() # 9.Tom选修了哪几门课？ scm.filter(lambda a:a[0] == 'Tom').map(lambda a:a[1]).collect() df.filter(df.name == 'Tom').select(df.course).show() spark.sql('select course from sc where name = "Tom"').show() # 10.Tom的平均分。 import numpy as np np.mean(scm.filter(lambda a:a[0]=='Tom').map(lambda a:a[2]).collect()) df.filter(df.name=='Tom').agg({'grade':'mean'}).show() spark.sql("select avg(grade) from sc where name='Tom'").show() # 11.'OperatingSystem'不及格人数 scm.filter(lambda a:a[1] == 'OperatingSystem' and a[2] < 60).count() df.filter(df['course'] == 'OperatingSystem').filter(df['grade'] < 60).count() spark.sql('select count(grade) from sc where course = "OperatingSystem" and grade < 60').show() # 12.'OperatingSystem'平均分 np.mean(scm.filter(lambda a:a[1] == 'OperatingSystem').map(lambda a:(a[1],a[2])).values().collect()) df.filter(df['course'] == 'OperatingSystem').agg({'grade':'avg'}).show() spark.sql('select AVG(grade) from sc where course = "OperatingSystem"').show() # 13.'OperatingSystem'90分以上人数 scm.filter(lambda a:a[1] == 'OperatingSystem' and a[2] > 90).count() df.filter(df['course'] == 'OperatingSystem').filter(df['grade'] > 90).count() spark.sql('select count("OperatingSystem > 90") from sc where course = "OperatingSystem" and grade >90').show() # 14.'OperatingSystem'前3名 scm.filter(lambda a:a[1] == 'OperatingSystem').sortBy(lambda a:a[2],False).map(lambda a:(a[0])).take(3) df.filter(df['course'] == 'OperatingSystem').sort(-df['grade']).select(df.name).limit(3).show() spark.sql('select name from sc where course = "OperatingSystem" order by grade desc limit 3').show() # 15.每个分数按比例+20平时分。 scm.map(lambda a:((a[0],a[1]),a[2])).take(5) scm.map(lambda a:((a[0],a[1]),a[2])).mapValues(lambda v:v*0.8+20).take(5) df.select(df.name, df.course, df.grade).show() df.select(df.name, df.course, df.grade*0.8+20).show() spark.sql("select name, course, grade from sc").show() spark.sql("select name, course, grade*0.8+20 from sc").show() # 16.求每门课的平均分 rdd.map(lambda a:(a[1], (int(a[2]), 1))).reduceByKey(lambda a,b:[a[0]+b[0], a[1]+b[1]]).map(lambda a:[a[0], a[1][0]/a[1][1]]).collect() df.groupBy(df.course).agg({'grade':'avg'}).show() spark.sql('select course, AVG(grade) from sc group by course').show() # 17.选修了7门课的有多少个学生？ rdd.map(lambda a:(a[0], 1)).reduceByKey(lambda a,b:a+b).filter(lambda a:a[1]==7).count() # df.groupBy('name').count().filter(col('count') == 7).select(count('name')).show() # spark.sql( "select count('name')\ from (select name, count('name') num from sc where group by name)\ where num = 7" ).show() # 18.每门课大于95分的学生数 # 跟第5题一样 # scm.filter(lambda a:a[2]>95).map(lambda a:[a[0], 1]).reduceByKey(lambda a,b:a+b).join(scm.map(lambda a:[a[0], 1]).reduceByKey(lambda a,b:a+b)).filter(lambda a: a[1][0] == a[1][1]).count() # # # df.filter(df.grade > 95).groupBy(df.name).agg({'name':'count'}).select('name', col('count(name)').alias('c1'))\ # .join(df.groupBy(df.name).agg({'name':'count'}).select('name', col('count(name)').alias('c2')),'name','left')\ # .filter(col('c1') == col('c2')).select(count('name'))\ # .show() # # # spark.sql( # "select name, count(name) c2\ # from sc group by name" # ).createOrReplaceTempView("b") # spark.sql( # "select name, count(name) c1\ # from sc where grade>95 group by name" # ).createOrReplaceTempView("a") # spark.sql( # "select count(a.name)\ # from a join b on a.name = b.name where c1 = c2" # ).show() # 19.每门课的选修人数、平均分、不及格人数、通过率 # rdd # 选修人数 rdd.map(lambda a:(a[1], 1)).reduceByKey(lambda a,b:a+b).collect() # 平均分 rdd.map(lambda a:(a[1], (int(a[2]), 1))).reduceByKey(lambda a,b:[a[0]+b[0], a[1]+b[1]]).map(lambda a:[a[0], a[1][0]/a[1][1]]).collect() # 不及格人数 rdd.map(lambda a:(a[1], a[2])).filter(lambda a:int(a[1]) < 60).map(lambda a:(a[0], 1)).reduceByKey(lambda a,b:a+b).collect() # 通过率 rdd.map(lambda a:(a[1], 1)).reduceByKey(lambda a,b:a+b).join( rdd.map(lambda a:(a[1], a[2])).filter(lambda a:int(a[1]) < 60).map(lambda a:(a[0], 1)).reduceByKey(lambda a,b:a+b) ).map(lambda a:(a[0], 1-(a[1][1]/a[1][0]))).collect() # df df1 = df.groupBy(df.course).agg({'grade':'mean'}).select( 'course', bround('avg(grade)',scale=2).alias('平均分') ).join( df.groupBy(df.course).count()\ .select( 'course', col('count').alias('选修人数') ),"course","left" ).join( df.filter(df.grade<60).groupBy(df.course).count()\ .select( 'course', col('count').alias('不及格人数') ),"course","left" ) df1.select( 'course', '平均分', '选修人数', '不及格人数', 1-df1['不及格人数']/df1['选修人数'] ).select( 'course', '平均分', '选修人数', '不及格人数', col("(1 - (不及格人数 / 选修人数))").alias("通过率") ).show() # sql spark.sql( "select course, count(name) as count, round(avg(grade), 2) as avg\ from sc group by course" ).show() spark.sql( "select course, count(name) as failed\ from sc where grade<60 group by course" ).createOrReplaceTempView("b") spark.sql( "select course, count(name) as count\ from sc group by course" ).createOrReplaceTempView("a") spark.sql( "select a.course, b.failed, 1-b.failed/a.count as pass\ from a join b on a.course = b.course" ).show() # 20.优秀、良好、通过和不合格各有多少人？ scm.map(lambda a:a[2]).filter(lambda a:a>=90).count() scm.map(lambda a:a[2]).filter(lambda a:a>=80 and a<90).count() scm.map(lambda a:a[2]).filter(lambda a:a>=60 and a<80).count() scm.map(lambda a:a[2]).filter(lambda a:a<60).count() # df.filter(df.grade>=90).select(count(df.grade)).show() df.filter((df.grade>=80) & (df.grade<90)).select(count(df.grade)).show() df.filter((df.grade>=60) & (df.grade<80)).select(count(df.grade)).show() df.filter(df.grade<60).select(count(df.grade)).show() # spark.sql("select count(name) from sc where grade>=90").show() spark.sql("select count(name) from sc where grade>=80 and grade<90").show() spark.sql("select count(name) from sc where grade>=60 and grade<80").show() spark.sql("select count(name) from sc where grade<60").show() # 21.同时选修了 DataStructure 和 DataBase 的学生 scm.map(lambda a:[a[0], a[1]]).groupByKey().map(lambda x:(x[0], list(x[1]))).filter(lambda a:'DataStructure' in a[1] and 'DataBase' in a[1]).count() # df.select(col('name'), col('course').alias('ac')).join( df.select(col('name'), col('course').alias('bc')),"name","inner" ).filter(col('ac')=='DataBase').filter(col('bc')=='DataStructure')\ .select(col('name')).distinct().select(count(col('name'))).show() # spark.sql( "select count(distinct a.name)\ from sc a join (select name, course from sc) b on a.name = b.name\ where a.course = 'DataStructure' and b.course = 'DataBase'" ).show() # 22.选修了 DataStructure 但没有选修 DataBase 的学生 scm.map(lambda a:[a[0], a[1]]).groupByKey().map(lambda x:(x[0], list(x[1]))).filter(lambda a:'DataStructure' in a[1] and 'DataBase' not in a[1]).count() # df2=df.filter(col('course') == 'DataBase').select(col('name').alias('b')) df1=df.filter(col('course') == 'DataStructure').select(col('name').alias('a')) df1.join(df2,[df1.a==df2.b],'left').filter(col('b').isNull()).select(count(col('a'))).show() # spark.sql( "select distinct name\ from sc where course = 'DataStructure'" ).createOrReplaceTempView("a") spark.sql( "select distinct name\ from sc where course = 'DataBase'" ).createOrReplaceTempView("b") spark.sql( "select count(a.name)\ from a left join b on a.name = b.name\ where b.name is null" ).show() # 23.选修课程数少于3门的同学 rdd.map(lambda a:(a[0], 1)).reduceByKey(lambda a,b:a+b).filter(lambda a:a[1]<3).map(lambda a:a[0]).collect() df.groupBy('name').count().filter(col('count') < 3).select(col('name')).show() # spark.sql( "select name\ from (select name, count('name') num from sc where group by name)\ where num < 3" ).show() # 24.选修6门及以上课程数的同学 rdd.map(lambda a:(a[0], 1)).reduceByKey(lambda a,b:a+b).filter(lambda a:a[1]>=6).map(lambda a:a[0]).collect() df.groupBy('name').count().filter(col('count') > 6).select(col('name')).show() # spark.sql( "select name\ from (select name, count('name') num from sc where group by name)\ where num > 6" ).show() # 25.查询平均成绩大于等于60分的姓名和平均成绩 rdd.map(lambda a:(a[0], (int(a[2]), 1))).reduceByKey(lambda a,b:[a[0]+b[0], a[1]+b[1]]).map(lambda a:[a[0], a[1][0]/a[1][1]]).filter(lambda a:a[1]>=60).collect() # df.groupBy(df.name).agg({'grade':'mean'}).select( 'name', bround('avg(grade)',scale=2).alias('平均分') ).filter(col('平均分')>=60).show() # spark.sql( "select name, round(avg(grade), 2) as avg\ from sc group by name having avg >= 60" ).show() # 26.找出平均分最高的10位同学 rdd.map(lambda a:(a[0], (int(a[2]), 1))).reduceByKey(lambda a,b:[a[0]+b[0], a[1]+b[1]]).map(lambda a:[a[0], a[1][0]/a[1][1]]).sortBy(lambda a:a[1],False).take(10) # df.groupBy(df.name).agg({'grade':'mean'}).select( 'name', bround('avg(grade)',scale=2).alias('平均分') ).sort(-col('平均分')).limit(10).show() # spark.sql( "select name, round(avg(grade), 2) as avg\ from sc group by name\ order by avg desc limit 10" ).show()