students.txt部分数据如下:
1500100001,施笑槐,22,女,文科六班
1500100002,吕金鹏,24,男,文科六班
1500100003,单乐蕊,22,女,理科六班
1500100004,葛德曜,24,男,理科三班
1500100005,宣谷芹,22,女,理科五班
score.txt部分数据如下:
1500100001,语文,98
1500100001,数学,5
1500100001,英语,137
1500100001,政治,29
1500100001,历史,85
cource.txt数据如下:
语文,150
数学,150
英语,150
政治,100
历史,100
地理,100
化学,100
生物,100
物理,100
学生表字段描述:学号,姓名,年龄,性别,班级
分数表字段描述:学号,科目名,分数
科目表字段描述:科目名,总分
使用spark-sql解决问题
分别建表:
create table students (id string,name string,age string,sex string,class string)
row format delimited fields terminated by ',' stored as textfile;
```bash
create table score (id string,sbjectname string,score string)
row format delimited fields terminated by ',' stored as textfile;
create table cource (sbjectname string,totalscore string)
row format delimited fields terminated by ',' stored as textfile;
数据上传到hdfs:
hadoop fs -mkdir /Spark_Data
hadoop fs -put cource.txt /Spark_Data
hadoop fs -put score.txt /Spark_Data
hadoop fs -put students.txt /Spark_Data
从hdfs导入数据:
load data inpath '/Spark_Data/students.txt' into table students;
load data inpath '/Spark_Data/score.txt' into table score;
load data inpath '/Spark_Data/cource.txt' into table cource;
使用Spark统计每个班级学生的人数
select class,count(*) from students group by class;
结果截图:
2、使用Spark sql统计每个班级总分排名前十的学生
select * from
(select class,name,sum(score) as ss,row_number() over(partition by class order by sum(score) desc) as rn
from score,students
where students.id = score.id
group by class,name) a
where a.rn < 11;
结果截图:
3、统计每科都及格的学生
select students.id,name,class,score.sbjectname,score from cource,score,students
where students.id = score.id and score>(totalscore * 0.6);
结果截图: