Hive面试SQL总结
一、连续登陆问题
-- user_id, date, flag
-- data:
1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 0
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1
-- 建表
create table t_login(user_id string, dt string, flag string) row format delimited fields terminated by ' ';
load data local inpath '/opt/workspace/login.dat' into table t_login;
-- 求连续登陆3天的用户的起始时间和结束时间
with tmp as (
select *, date_sub(dt, rn) as start_day
from (
select *, row_number() over(partition by user_id order by dt) as rn
from t_login
where flag = '1'
) t
)
select user_id, start_day, count(1) as cnt, max(dt), min(dt)
from tmp
group by user_id, start_day
having cnt >= 3
;
二、留存分析
-- 计算20211123的留存
with t1 as (
select distinct user_id
from t_marmot_login_event
where dt = '20211123'
), t2 as (
select distinct user_id
from t_marmot_login_event
where dt = '20211124'
)
select count(t1.user_id) as yesterday_cnt, count(t2.user_id) as today_cnt
from t1 left outer join t2
on t1.user_id = t2.user_id
-- 计算留存分析(2) with tmp as ( SELECT distinct view_id, distinct_id, p_dymd from dwd_marmot_click_event where p_dymd <= '${YEAR_MONTH_DAY}' and p_dymd >= date_sub('${YEAR_MONTH_DAY}', 30) order by p_dymd desc ) select tab2.t1, count(distinct case when diff = 1 then tab2.distinct_id else null end ) as one_day, count(distinct case when diff = 2 then tab2.distinct_id else null end ) as two_day from ( select *, datediff(t1, t2) as diff from ( select a.view_id, a.distinct_id, a.p_dymd t1, b.p_dymd t2 from tmp a inner join tmp b on a.view_id = b.view_id and a.distinct_id = b.distinct_id ) tab1 ) tab2
三、求每个科目前三名的同学,分数一样的并列,没有间隔,同时计算分差
--sid, sub, score
1 语文 78
2 语文 89
3 语文 76
4 语文 81
1 数学 67
2 数学 77
3 数学 78
4 数学 71
1 英语 87
2 英语 81
3 英语 91
4 英语 79
-- 建表
create table t_score(uid string, sub string, score int) row format delimited fields terminated by ' ';
load data local inpath '/opt/workspace/score.dat' into table t_score;
-- 计算
with tmp as (
select *
from (
select *, dense_rank() over(partition by sub order by score desc) as rn
from t_score
) t
where rn <= 3
)
select uid, sub, score, rn, nvl(score-lag_value, 0)
from (
select *, lag(score) over(partition by sub order by score desc) as lag_value
from tmp
) t1
四、行列转换
-- 数据使用t_score表
-- 行转列: case when group by
select uid,
sum(case when sub='语文' then score else 0 end) as a,
sum(case when sub='数学' then score else 0 end) as b,
sum(case when sub='英语' then score else 0 end) as c
from t_score
group by uid;
-- 列转行
select uid, concat_ws(",", collect_list(item))
from (
select uid, concat(sub, ":", score) as item
from t_score
) t
group by uid
-- lateral view explode
五、漏斗分析
-- 简单的漏斗: 常理A->B->C-D数量是依次递减的
select
substr(server_time, 1, 10) as biz_day,
count(distinct case when event='A' then uid else null end) as a_cnt,
count(distinct case when event='B' then uid else null end) as b_cnt,
count(distinct case when event='C' then uid else null end) as c_cnt,
count(distinct case when event='D' then uid else null end) as d_cnt
from t_marmor_test
where p_dymd = '20211121'
and substr(server_time, 1, 10) = '2021-11-21'
-- 考虑完整的链路,不同事件之间间隔少于5min,同时不存在重复事件
-- 一般来说每次访问都有一个flow_id或者session_id
with t1 as (
select flow_id, event, ctime
from t_marmot_log
where p_dymd='20211121' and event='A'
), t2 as (
select flow_id, event, ctime
from t_marmot_log
where p_dymd='20211121' and event='B'
), t3 as (
select flow_id, evnet, ctime
from t_marmot_log
where p_dymd='20211121' and event='C'
), t4 as (
select flow_id, event, ctime
from t_marmor_log
where p_dymd=''20211121 and event='D'
)
select
count(t1.flow_id) as step1,
count(t2.flow_id) as step2,
count(t3.flow_id) as step3,
count(t4.flow_id) as step4
from t1 left outer join t2
on t1.flow_id = t2.flow_id and datediff('second', t1.ctime, t2.ctime) < 30 * 60
left outer join t3
on t1.flow_id = t3.flow_id and datediff('second', t2.ctime, t3.ctime) < 30 * 60
left outer join t3
on t1.flow_id = t4.floe_id and datediff('second', t3.ctime, t4.ctime) < 30 * 60