Hive练习题
题目一
- 学生表(STUDENT)的字段含义:SNO 代表学号,SNAME 代表学生姓名,SAGE 代表学生年龄,SSEX 代表学生性别
- 课程表(COURSE)的字段含义:CNO 代表课程编号,CNAME 代表课程名字,TNO 代表教师编号,
- 成绩表(SC)的字段含义:SNO 代表学号,CNO 代表课程编号,SCORE 代表成绩
- 教师表(TEACHER)的字段含义:TNO 代表教师编号,TNAME 代表教师姓名
- 查询所有学生的学号、姓名、选课数、总成绩、平均成绩、排名
- 查询没学过“悟空”老师课的学生的学号、姓名
- 求每门课程的成绩前 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;