＜Zhuuu_ZZ＞Hive笔试题

题目一：

1. 学生表(STUDENT)的字段含义：SNO 代表学号，SNAME 代表学生姓名，SAGE 代表学生年龄，SSEX 代表学生性别
   
   

2. 课程表(COURSE)的字段含义：CNO 代表课程编号，CNAME 代表课程名字，TNO 代表教师编号，
   
   

3. 成绩表(SC)的字段含义：SNO 代表学号,CNO 代表课程编号,SCORE 代表成绩
   
   

4. 教师表(TEACHER)的字段含义：TNO 代表教师编号，TNAME 代表教师姓名
   

1. 查询所有学生的学号、姓名、选课数、总成绩、平均成绩、排名

select sc.sno,sname,count(cno) count,sum(score) ,round(avg(score),2) ,
dense_rank() over( order by round(avg(score),2) desc) 
from sc right join student on sc.sno=student.sno group by sc.sno,sname;

2. 查询没学过“悟空”老师课的学生的学号、姓名

select student.sno,sname from student
left join (select sno from sc
join course on sc.cno=course.cno
join teacher on course.tno=teacher.tno
and teacher.tname='悟空') a
on student.sno=sc.sno
where sc.sno is null;

3. 求每门课程的成绩前 3 名学生，并附上课程名、学号、学生姓名、成绩、排名

select a.cno,a.sno,sname,score,dr from (
select cno,sno,score,dense_rank() over(partition by cno order by score desc ) dr from sc) a
join student on a.sno=student.sno
where dr<=3;

题目二：

下表 tab为某网站用户行为表：

1.求每日新增用户数

with 
t1 as (select user_id,min(log_time) first_time from tab group by user_id)
select first_time ,count(user_id) from t1 group by first_time;

2.求 2019-05-01 当天的新增用户数、1 日留存率、7 日留存率、30 日留存率

with
t1 as (select user_id,min(log_time) first_time from tab group by user_id ),
t2 as (select user_id,count(1) over(partition by first_time) c  from t1 
where first_time='2019/5/1'  ),
t3 as (select sum(case when log_time='2019/5/2' then 1 else 0 end) count2 ,
sum(case when log_time='2019/5/8' then 1 else 0 end ) count7,
sum(case when log_time='2019/5/31' then 1 else 0 end)count30
from tab where tab.user_id in (select user_id from t2))
select distinct t2.c,count2/t2.c,count7/t2.c,count30/t2.c from t2,t3;

• 简化版本的

with
t1 as (select user_id,min(log_time) first_time from tab group by user_id ),
t2 as (select user_id from t1 where first_time='2019/5/1'  ),
t3 as (select user_id from tab where log_time='2019/5/2'),
t4 as (select user_id from tab where log_time='2019/5/8'),
t5 as (select user_id from tab where log_time='2019/5/31')
select count(t2.user_id),count(t3.user_id)/count(t2.user_id),count(t4.user_id)/count(t2.user_id),count(t5.user_id)/count(t2.user_id) from t2
left join t3 on t2.user_id=t3.user_id
left join t4 on t2.user_id=t4.user_id
left join t5 on t2.user_id=t5.user_id;

题目三：

现有表 tab1 格式如下左图所示：params 为 map 类型数据，请将 params 解析为右图所示格式

• 虽然params中的值看上去是一个collection集合里面包含着map键值对，但其实在hive中这样的多个map集合就是map类型，只需要在创表时collection items terminated by '&' map keys terminated by ':'就可以通过explode直接切分成key，value两字段。

• 因为params字段是map类型，所以explode(params)可以直接切割为键值对，不需要用split拆分，

然后直接用虚拟表的表名.key，表名.value;

select explode(params) from tab1;

• 但是如果我们想把name放进去得到结果会怎样呢？

select name,explode(params) from tab1;

分析错误，UDTF(一进多出)不支持函数之外其它字段的select 语句==

• 那怎么办呢？所以就需要用到lateral view

Lateral view 其实就是用来和像类似explode这种UDTF函数联用的。lateral view 会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行即每个name进行join 来达到连接UDTF外的select字段的目的。

• 建表

create table if not exists tab1(
name string,
params map<string,int>
)
row format delimited
fields terminated by '\t'
collection items terminated by '&'
map keys terminated by ':'
stored as textfile;

• 查询

select name,e.key course,e.value score from tab1
lateral view explode(params) e;

题目四：

• 同样是列转行然后聚合，可以采用lateral view explode
• 如果是行转列，则采用case when 或者concat
• 本题可以与题目三对比一哈
  需求：使用hive计算haha表中num列的sum值

item	num	sum
A	1,2,3,4
B	2,5,1

Select item,num,sum(e) from haha
Lateral view explode(num) tmp as e
Group by item,num;

• 本题与题目三最明显的区别，就是你可以看到第二行有两个别名，那么分别代表什么呢？

 `tmp是lateral view这个侧视图的别名，其实就是个临时表的表名，
 那么根据sum(e)可以看出e是一个字段名，所以它就是说把这个tmp临时表当作一个字段去聚合`。

• 那为什么本题有两个别名，为什么题目三只有一个别名？

实际上是因为题目三经过lateral view explode后产生的临时表不只一列，
因为原字段是map的关系，经过explode后自动以冒号分割自动产生字段名为key和value的两列，
所以无法把整个表作为一个字段，而这个时候知道字段名，就可以直接通过表名.key，表名.value。
而本题目因为不知道这个临时表产生的字段名是啥，所以我们需要自己主动的as 一个字段名，就可以在查询中调用。