Hive sql 面试题 涉及开窗函数 nvl date函数 substr not exits 及 各种join 爆破函数 collect_list 等

Hive sql 面试题 涉及开窗函数 nvl date函数 substr not exits 及 各种join 爆破函数 collect_list 等

从朋友那里得了一份面试题，让我帮着做做，闲来无事帮她做了几道，好不容易造了数据，分享一下，有需要的可以上百度网盘拷下数据自己做做，题目比较适合查漏补缺。

• 源数据链接
• 题目图片
• 笔者自己的答案（只有部分）

链接：戳链接获得准备好的数据，直接load之后即可进行练习：建表需要自己建哦~

百度网盘链接在此: https://pan.baidu.com/s/1xD-0NMdsrkdXLy7AZcFyjA
提取码: r89v

题目内容

答案 我自己写的几道题的答案

--item3：
select
a.userid as userid,
a.month as month,
a.visits as month_visit,
max(a.visits) over(partition by a.userid ORDER BY a.month) as max_month_visit,
sum(a.visits) over(partition by a.userid ORDER BY a.month) as total_visit
from
(select 
userid,
month,
sum(visits) as visits
from beatbinge.item3
group by userid,month)a

--item4
select
count(1) as total_number
from
(select
c.uid as uid,
max(c.days) as max_in_row
from
(select
b.uid as uid,
b.dt as dt,
count(b.dt) over(partition by b.dt,b.uid) as days
from
(select
a.uid as uid,
date_sub(dt,rn) as dt
from
(select
uid,
dt,
row_number() over(partition by uid ORDER BY dt) as rn
from item4
where login_status=1)a)b)c
group by c.uid
having max(c.days)>7)d;

--事实证明 开窗函数是作用在where之后的，所以有需要用where过滤的情况下，可以将开窗函数写在同一层

--item6
select
a.class,
a.stu_no,
a.score,
nvl((b.score-a.score),"/") as score_sub,
a.ranking
from
(select
class,
stu_no,
score,
dense_rank() over(partition by class order by score desc) as ranking
from item6)a
left join
(select