Hive架构图以及Hive练习题

Hive架构图以及Hive练习题

一、架构图

二、练习题

在HDFS中有个默认地址：/usr/hive/warehouse/数据库名/表名

该数据库名和表名就是通过shell在Hive创建的

1、模仿建表语句，创建科目subject表，并使用hdfs dfs -put 命令加载数据(上传数据)

create table subject(
  subject_id bigint comment '科目id',
  subject_name string comment '科目名称'
) comment '科目表' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 

2、查询学生分数（输出：学号，姓名，班级，科目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
;

执行结果

1500100001	施笑槐	文科六班	1000001	语文	98
1500100001	施笑槐	文科六班	1000002	数学	5
1500100001	施笑槐	文科六班	1000003	英语	137
1500100001	施笑槐	文科六班	1000004	政治	29
1500100001	施笑槐	文科六班	1000005	历史	85
1500100001	施笑槐	文科六班	1000006	物理	52
1500100002	吕金鹏	文科六班	1000001	语文	139
1500100002	吕金鹏	文科六班	1000002	数学	102
1500100002	吕金鹏	文科六班	1000003	英语	44
1500100002	吕金鹏	文科六班	1000004	政治	18

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

#根据需求，需要学生表和分数表两张表
#方法一
select  t1.id
        ,t1.name
        ,t1.clazz
        ,sum(t2.score) as sum_score
from students t1
left join score t2
on t1.id = t2.id
group by t1.id,t1.name,t1.clazz
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;

执行结果

1500100001	施笑槐	文科六班	406
1500100002	吕金鹏	文科六班	440
1500100003	单乐蕊	理科六班	359
1500100004	葛德曜	理科三班	421
1500100005	宣谷芹	理科五班	395
1500100006	边昂雄	理科二班	314
1500100007	尚孤风	文科六班	418
1500100008	符半双	理科六班	363
1500100009	沈德昌	理科一班	251
1500100010	羿彦昌	理科六班	402

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

#思路:求总分、desc排序、limit3
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;

执行结果

1500100929	满慕易	理科三班	630
1500100080	巫景彰	理科五班	628
1500100308	黄初夏	文科一班	628

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

select  t1.id
        ,t1.name
        ,'文科一班' as clazz
        ,t2.sum_score
from (
    select  id
            ,name
    from students
    where clazz = '文科一班'
) 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 10;

执行结果

1500100308	黄初夏	文科一班	628
1500100875	马向南	文科一班	595
1500100943	许昌黎	文科一班	580
1500100871	贝惜梦	文科一班	569
1500100128	巫鸿哲	文科一班	549
1500100640	纪昌黎	文科一班	521
1500100118	蔺昆宇	文科一班	509
1500100442	郎泽洋	文科一班	486
1500100338	白之玉	文科一班	485
1500100770	屠迎梅	文科一班	471

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

select  t1.clazz
        ,round(avg(t2.sum_score),2) as clazz_avg_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
group by t1.clazz
;

执行结果

文科一班	377.94
文科三班	383.67
文科二班	368.9
文科五班	365.4
文科六班	370.24
文科四班	380.23
理科一班	356.68
理科三班	376.63
理科二班	378.72
理科五班	386.89
理科六班	365.16
理科四班	366.01

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

select  t1.clazz
        ,max(t2.sum_score) as clazz_max_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
group by t1.clazz

执行结果

文科一班	628
文科三班	568
文科二班	611
文科五班	589
文科六班	583
文科四班	612
理科一班	520
理科三班	630
理科二班	586
理科五班	628
理科六班	587
理科四班	534

8、（思考）查询每个班级总分排名前三的学生（输出：学号，姓名，班级，总分）

#窗口函数（开窗函数）：row_number()