Hive练习

一、创建表

1.创建students表

创建表

create table students(
    id bigint comment '学生id',
    name string comment '学生姓名',
    age int comment '学生年龄',
    gender string comment '学生性别',
    clazz string comment '学生班级'
) comment '学生信息表'
row format delimited fields terminated by ',';

上传数据到hdfs上

hdfs dfs -put students.txt /user/hive/warehouse/test1.db/students

2.创建score表

创建表

create table score(
    id bigint comment '学生id',
    score_id bigint  comment '科目id',
    score int comment '学生成绩'
) comment '学生成绩表'  
row format delimited fields terminated by ',';

上传数据到hdfs上

hdfs dfs -put score.txt /user/hive/warehouse/test1.db/score

3.创建subject表

创建表

create table subject( 
	subject_id bigint comment '课程id',
	subject_name string comment '课程名'
)comment '课程表' 
row format delimited fields terminated by ',';

上传数据至hdfs上

hdfs dfs -put subject.txt /user/hive/warehouse/test1.db/subject

4.查看三张表数据

二、练习

查询学生分数（输出：学号，姓名，班级，科目id，科目名称，成绩）

select t1.id
      ,t1.name
      ,t1.clazz
      ,t2.score_id
      ,t3.subject_name
      ,t2.score
from students t1
left join score t2
on t1.id=t2.id
left join subject t3
on t2.score_id=t3.subject_id
limit 10;

查询学生总分（输出：学号，姓名，班级，总分）

select t1.id
      ,t1.name
      ,t1.clazz
      ,t2.sum_score
from students t1
left join(
      select id
               ,sum(score)as sum_score
      from score
      group by id
      )t2 on t1.id=t2.id
limit 10;

查询全年级总分排名前三（不分文理科）的学生（输出：学号，姓名，班级，总分）

select t1.id
	   ,t1.name
	   ,t1,clazz
	   ,t2.sum_score
from students t1
left join(
	select id
	      ,sum(score) as sum_score
	from score
	group by id		  
) t2 on t1.id=t2.id
order by t2.sum_score desc
limit 3;

查询文科一班学生总分排名前10的学生（输出：学号，姓名，班级，总分）

select t1.id
      ,t1.name
      ,t1.clazz
      ,t2.sum_score
from students t1
left join(
      select id
      		,sum(score)as sum_score
      from score
      group by id
      ) t2 on t1.id=t2.id
where t1.clazz='文科一班'
 order by t2.sum_score desc
limit 10;

查询每个班级学生总分的平均成绩（输出：班级，平均分）

select tt1.clazz
	   ,avg(tt1.sum_score)as avg_sum_score
from (
	select t1.clazz
			,t2.sum_score
	from students t1
	left join(
    	select id
    		  ,sum(score)as sum_score
    	from score
    	group by id
    ) t2 on t1.id=t2.id
) tt1 group by tt1.clazz;

查询每个班级的最高总分（输出：班级，总分）

select tt1.clazz
		,max(tt1.sum_score) as max_sum_score
from(
	select t1.clazz
			,t2.sum_score
	from student t1
	left join(
   		select id
   			 ,sum(score)as sum_score
   		from score
   		group by id	
   ) t2 on t1.id=t2.id
)tt1 group by tt1.clazz;