## *将以下学生成绩数据，存放在Hdfs上，使用Spark读取完成下面分析**

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);

结果截图：