[大数据]2020年安徽省大数据网络赛与现场赛hive习题

?目录

?第一章 2020年安徽省大数据比赛hive习题 （网络赛）??

? ?第二章 2020年安徽省大数据比赛hive习题?（现场赛）

前言

? ?主要记录hive习题

?

一、2020年安徽省大数据比赛hive习题（网络赛）??

第三部分：大数据分析（20分）

?疫情期间各类政府媒体及社交网站，均发布了相关疫情每日统计数据，下面基于数据仓库工具Hive请你统计分析相关疫情数据。

数据字段为：日期、省份、城市、新增确诊、新增出院、新增死亡、消息来源、来源1，来源2，来源3

1. 请在hdfs根目录下中创建一个目录，以你的小组名称英文全拼命名_data，并将以上疫情数据上传到这个目录中，截图成功后结果（2分）。
2. 请你在hive中创建数据库名为: ods_yiqing_data，并切换使用这个数据库，截图成功后结果? ? ? ?create database? ?ods_yiqing_data；? ?use?ods_yiqing_data；
3. 创建一个hive外部表，字段为以上疫情数据全部字段（自行命名字段），数据存储位置为第1小题创建的hdfs目录，请粘贴建表语句及运行成功截图（3分）。

   ?
   ?create ? table ?epis(
   ?data string,
   ?province string,
   ?city string,
   ?definite ?int,
   ?leave ?int,
   ?die ?int,
   ?messageone ?string,
   ?messagetwo ?string,
   ?messagethree ?string?
   ?) row format delimited fields terminated by ',';
   
   ?

4. 统计湖北省各市2月新增确诊病例总数，按照总数降序排列，请提供SQL语句及运行结果截图（4分）

   select * from (
   
   select  
         city,sum(definite) as sumPeople
   from                                 
       epidemic 
   where  province='湖北' and data like "2月%" 
   group by  city   
   )t1
    order by t1.sumPeople desc;

5. 统计文件中安徽省合肥市每月新增确诊病例总数，按照降序排列，请提供SQL语句及运行结果截图（4分）

   
   select  
       substr(data,1,2), sum(definite)  ,rank() over(order by sum(definite) desc)
   from
       epidemic
   where  
      city='合肥市' and province='安徽'
    group by substr(data,1,2)
   
   ;

6. 统计文件中湖北每月新增出院病例总数最多的前2个城市，请提供SQL语句及运行结果截图（5分）

    select t1.datas datas, t1.city city, t1.nums count
   from 
   (select  substr(data,1,2) datas,city,sum(definite) nums,
   row_number() over(partition by  substr(data,1,2) order by sum(definite) desc) rmp
   from epidemic
   where province= "湖北"
   group by  substr(data,1,2),city)t1
   where t1.rmp <= 2;

二、2020年安徽省大数据比赛hive习题（现场赛）? ?

? ?1、?创建一个数据库，以你的组名命名，创建成功后使用use命令切换为该库，并执行set hive.cli.print.current.db=true;截图作为答案（2分）

? ? ? ? ? ? 省略

2、在HIVE中创建需要的表，并写出建表语句，表结构如下：（3分）

? ?(1).学生资料表(student)

.课程表 (course)

3：考试成绩表(sc)

1. 创建hive表?语句截图：

? ? ?2）创建成功，执行show tables；截图；

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?省略

3、将数据加载到表中，写出加载数据的语句(3分)

数据文件名称分别与表名称对应，（字段分隔符为，），注意，linux系统和hive默认不支持中文，要自己设置成utf-8编码才可以支持中文。

1) 导入命令截图；?

2）执行?select * from table_name 并截图（其中table_name 为前面创建的表名）

执行 select * from student;?截图?

执行select * from sc;?截图?

执行select?*?from?course;?截图

? ? ? ? ? ? ? ? ? ? ? ? ? ?省略

4、利用hive进行数据分析查询（12分）。

1）查询各科成绩平均分(1分)

? ?sql语句截图：

??

select c_id,avg(score) avg
from sc
group by c_id;

2）查询选修1号课程的学生最高分数(1分) ?

sql语句截图：

select c_id,score,s_id
from
sc
where c_id =1 
order by c_id desc limit 1
;

3）查询每个学生及其选修课程的情况?(1分)?

sql语句截图：

select name,concat_ws('|',collect_set(c_name))
from
(select t1.s_name as name,t2.c_id as id
from
student as t1 left join sc as t2
on
t1.s_id = t2.s_id)t3,course as t4
where t3.id = t4.c_id
group by t3.name
;

4）查询选修了3门以上（包含3门）的课程的学生学号(4分)?

sql语句截图：

select s_id
from
(select s_id,count(*) sum from
(select a.s_id s_id,b.c_id c_id
from
student as a,sc as b
where a.s_id = b.s_id
)t1
group by s_id
)t2
where sum >3;

5）查询每门课程成绩前两名的学生信息（5分）?

sql语句截图：

select c_id,t3.s_id,t3.s_name,t3.s_age,t3.s_dept,t3.s_sex
from
(select c_id,s_id
from
(select c_id,s_id,rank() over(partition by c_id order by score desc) as t
from
sc)t1
where t<=2)t2,student as t3
where t2.s_id = t3.s_id;

总结

?以上就是今天要讲的内容，本文仅仅简单介绍了做题的过程。