Hive -- Hive面试题及答案（4）

1.需求描述

解答

# 通过group by 去重
create table distinct_dayno_uid as 
select dayno, uid from act_user_info group by dayno, uid;

//求周留存
select dayno, resCnt, resCnt1, resCnt3, resCnt7, 
	concat(round(resCnt1/resCnt, 2), '%') resRate1,
	concat(round(resCnt3/resCnt, 2), '%') resRate3,
	concat(round(resCnt7/resCnt, 2), '%') resRate7
from (
	select a.dayno, 
		count(a.uid) resCnt, 
		count(b.uid) resCnt1,
		count(c.uid) resCnt3,
		count(d.uid) resCnt7,
	from distinct_dayno_uid a left join distinct_dayno_uid b
		on a.uid = b.uid and b.dayno = date_add(a.dayno, 1)
	left join distinct_dayno_uid c
		on a.uid = c.uid and c.dayno = date_add(a.dayno, 3)
	left join distinct_dayno_uid d
		on a.uid = d.uid and d.dayno = date_add(a.dayno, 7)
	group by a.dayno
) t

2.需求描述

  求180日内的留存

解答

//求半年留存
select dayno, count(distinct uid) resCnt, 
	count(if(date_flag==7, 1, null)) resCnt7,
	count(if(date_flag==14, 1, null)) resCnt14,
	count(if(date_flag==30, 1, null)) resCnt30,
	count(if(date_flag==90, 1, null)) resCnt90,
	count(if(date_flag==180, 1, null)) resCnt180
from (
	select a.dayno, a.uid, datediff(a.dayno, b.dayno) date_flag from distinct_dayno_uid a 
	left join distinct_dayno_uid b on a.uid = b.uid 
	where b.dayno > a.dayno and b.dayno - a.dayno <= 180
) t group by dayno;

//或
select a.dayno, datediff(a.dayno, b.dayno) n日标志,
	count(distinct a.uid) 活跃用户数, 
	count(datediff(a.dayno, b.dayno)) n日留存用户数 
from distinct_dayno_uid a 
left join distinct_dayno_uid b on a.uid = b.uid 
where b.dayno > a.dayno and datediff(b.dayno - a.dayno) <= 180
group by a.dayno, datediff(a.dayno, b.dayno)

2.需求描述

  求连续180日的活跃用户

解答

select a.uid, 
	count(a.uid) con_days_cnt
from distinct_dayno_uid a 
left join distinct_dayno_uid b on a.uid = b.uid 
where b.dayno > a.dayno and b.dayno - a.dayno <= 180
group by a.uid having con_days_cnt = 180;