Hive练习题

Hive练习题

题目一

1. 学生表(STUDENT)的字段含义：SNO 代表学号，SNAME 代表学生姓名，SAGE 代表学生年龄，SSEX 代表学生性别
2. 课程表(COURSE)的字段含义：CNO 代表课程编号，CNAME 代表课程名字，TNO 代表教师编号，
3. 成绩表(SC)的字段含义：SNO 代表学号,CNO 代表课程编号,SCORE 代表成绩
4. 教师表(TEACHER)的字段含义：TNO 代表教师编号，TNAME 代表教师姓名
   

1. 查询所有学生的学号、姓名、选课数、总成绩、平均成绩、排名
2. 查询没学过“悟空”老师课的学生的学号、姓名
3. 求每门课程的成绩前 3 名学生，并附上课程名、学号、学生姓名、成绩、排名

代码如下：
1、

select * ,rank() over(order by avg desc) from(
select s.sno,sname,count(sc.cno) cnt,sum(score) total,avg(score) avg 
from student s join sc on s.sno=sc.sno group by s.sno,sname) a order by avg desc;

2、

select distinct st.sno,st.sname from student st join sc s on st.sno=s.sno where s.cno not in
(select cno from course c join teacher t on c.tno=t.tno where t.tname like "悟空")

3、

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

题目二

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

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

代码如下：
1、

select log_timem,count(distinct user_id) from(
select user_id,log_time,
rank() over(partition by user_id order by 
unix_timestamp(log_time,'yyyy/M/d')) rk 
from tab1) a where rk=1 
group by log_time;

2、

with
r1 as(
select user_id from(
select user_id,log_time,rank() over(partition by user_id order by 
unix_timestamp(log_time,'yyyy/M/d')) rk
from tab1) a where rk=1 and log_time='2019-05-01')   --当天新增客户

r2 as (select user_id,log_time from tab1 where unix_timestamp(log_time,'yyyy/M/d')=unix_timestamp(date_add(
r1.log_time,1))                  -- 1日留存用户

r3 as (select user_id,log_time from tab1 where unix_timestamp(log_time,'yyyy/M/d')=unix_timestamp(date_add(
r1.log_time,7))                  -- 7日留存用户

r4 as (select user_id,log_time from tab1 where unix_timestamp(log_time,'yyyy/M/d')=unix_timestamp(date_add(
r1.log_time,30))                  --30日留存用户

题目三

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

代码如下：
建表语句：

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,a.key course,a.value score from tab1 lateral view explode(params) a;