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)

字段	字段描述	字段类型
s_id	学生ID	int
s_name	学生姓名	String
s_sex	学生性别	String
s_age	学生年龄	int
s_dept	所在系	String

.课程表 (course)

字段	字段描述	字段类型
c_id	课程ID	int
c_name	课程名称	String

3：考试成绩表(sc)

字段	字段描述	字段类型
s_id	学生id	int
c_id	课程id	int
score	成绩	int

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;

---

总结

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