Hive经典面试题

环比：比上月同期
sales表
month id sales
202007 1 800
202004 1 900
202005 1 700
202006 1 900
202008 1 1000
202009 1 1100
202007 2 800
202004 2 900
202005 2 700
202006 2 900
202008 2 600
202009 2 1100

select distinct id
from (select a.month,a.id,
lag(a.sales,2,0) over(partition by a.id order by a.month) as prev
from (select month, id,sales,
	case when sales > lag(sales,1) over (partition by id order by month) *1.1 then 1 else 0 end as flag
from sales
)a
where a.flag=1
)b
where b.month= b.prev+2

2、连续登陆最多的天数

select a.user,max(consecutive_day) as max_day
from (select t.user,count(1) as  consecutive_day
from (select user,day, 
dayofyear(from_unixtime(unix_time(day,'yyyyMMdd'),'yyyy-MM-dd')) - row_number() over(partition by user order by day) as num,
from login
)t
group by t.user
)a
group by a.user

3、每年成绩都有提升

select b.student
from (select a.year,a.student
,case when (sum_score-lag(sum_score,1,0) over (partition by student order by year) >0 then 1 else 0 end ) as flag
from (select year,student,sum(score) sum_score
from scores 
group by year,student
)a
)b
group by b.student
having avg(flag) > 1
;

4、分组topN
按照性别进行分组，求分组后最大的两个年龄的人

select *
from (select id,sex,age,name,
row_number() over(partition by sex order by age desc) as r
from people
)t
where t.r<=2

5、lateral view + explode
article表

artid imageurls
1 a,b,c
2 b,c,d
3 c,d,e

select distinct artid,imageurls2
from articles lateral view explode(split(imageurls,','))t as imageurls2

artid imageurls2
1 a
1 b
1 c
2 b
2 c
2 d
3 c
3 d
3 e

select artid,concat_ws(',',collect_set(t2.url_feature))
               from (select distinct artid,imageurls2 from articles lateral view explode(split(imageurls,','))t as imageurls2) t1
               left join (select url,concat_ws(':',url,feature) as url_feature from imagefeatures) t2
               on t1.imageurls2=t2.url
               group by artid;

artid _c1
1 a:123,b:456,c:789
2 b:456,c:789,d:897
3 c:789,d:897

6、行列转换
需求

答案