Hive面试题

题目一

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

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

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

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

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

select s.sno,sname,count(cno) cnt,sum(score) total_score,avg(score) avg_score,
dense_rank() over(order by avg(score) desc)
from student s join sc on s.sno=sc.sno group by s.sno,sname;

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

select student.sno,sname from student where student.sno not in
(select sc.sno from sc
join course c on sc.cno=c.cno join teacher t on t.tno=c.tno
where t.tname='悟空');

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

select * from
(select sc.cno,cname,s.sno,sname,score,dense_rank() over(partition by sc.cno order by score desc) rn
from student s join sc on s.sno=sc.sno join course c on sc.cno=c.cno) a
where rn<=3;

题目二

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

1.求每日新增用户数

select first_time,count(1) from (
select user_id,min(log_time) first_time from t group by user_id) a
group by first_time;

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

方法一：

with r1 as 
(select user_id,count(1) over() cnt from (select user_id,min(log_time) first_time from t group by user_id) a
where a.first_time='2019/5/1'),
r2 as
(select sum(case when log_time='2019/5/2' then 1 else 0 end) day_1,
sum(case when log_time='2019/5/8' then 1 else 0 end) day_7,
sum(case when log_time='2019/5/31' then 1 else 0 end) day_30
from t where t.user_id in (select user_id from r1))
select distinct r1.cnt,round(day_1/r1.cnt,2),round(day_7/r1.cnt,2),round(day_30/r1.cnt,2) from r1,r2;

方法二：（方法一11个job太慢）

with r1 as 
(select user_id from (select user_id,min(log_time) first_time from t group by user_id) a
where a.first_time='2019/5/1'),
r2 as
(select user_id from t where log_time='2019/5/2'),
r3 as
(select user_id from t where log_time='2019/5/8'),
r4 as
(select user_id from t where log_time='2019/5/31')
select count(r1.user_id),round(count(r2.user_id)/count(r1.user_id),2),
round(count(r3.user_id)/count(r1.user_id),2),
round(count(r4.user_id)/count(r1.user_id),2) from r1 
left join r2 on r1.user_id=r2.user_id
left join r3 on r1.user_id=r3.user_id
left join r4 on r1.user_id=r4.user_id;

题目三

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

select name,split(info,':')[0] course,split(info,':')[1] score from tab1
lateral view explode(split(params,'&')) tmp as info 

如果导入的时候是map则

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