一、问题：留存率计算（SQL）
字段及表说明：
表名：user_log
字段名：
log_day:登录日期
device_id:用户设备id
app_id:用户app的id，其中device_id和app_id确定唯一的用户
1.1计算某日留存率（次日、3日、7日、30日）
--计算次日、3日、7日、30日留存率
select a.log_day 首次登录日期,
count(user_id_d1)/count(user_id_d0) retention_d1,
count(user_id_d3)/count(user_id_d0) retention_d3,
count(user_id_d7)/count(user_id_d0) retention_d7,
count(user_id_d30)/count(user_id_d0) retention_d30 from(
--找出新增
select distinct log_day,device_id||app_id user_id_d0 from user_log a where log_day=log_day'day'
and device_id||app_id not in(select distinct device_id||app_id from user_log b
where b.log_day<a.log_day)
)a
left join (select distinct log_day,device_id||app_id user_id_d1 from user_log where log_day=(log_day'day'+1) b on a.user_id_d0=b.user_id_d1
left join (select distinct log_day,device_id||app_id user_id_d3 from user_log where log_day=(log_day'day'+3) c on a.user_id_d0=c.user_id_d3
left join (select distinct log_day,device_id||app_id user_id_d7 from user_log where log_day=(log_day'day'+7) d on a.user_id_d0=d.user_id_d7
left join (select distinct log_day,device_id||app_id user_id_d30 from user_log where log_day=(log_day'day'+30) e on a.user_id_d0=e.user_id_d30
group by a.log_day
1.2计算每日留存率（次日、3日、7日、30日）
-- 创建留存率储存表
create table user_retention_monitior(
log_day date,retention_d1 number,retention_d3 number,
retention_d7 number,retention_d30 number
);
-- 清空表
truncate table user_retention_monitior;
declare
day date; --变量声明
-- 程序主体
begin
select min(log_day) into day from user_log; --变量赋初始值
loop
insert into user_retention_monitior
-- 计算留存
select a.log_day,
count(b.user_id_d1)/count(a.user_id_d0) retention_d1,
count(c.user_id_d3)/count(a.user_id_d0) retention_d3,
count(d.user_id_d7)/count(a.user_id_d0) retention_d7,
count(e.user_id_d30)/count(a.user_id_d0) retention_d30
from(
select distinct log_day,app_id||device_id user_id_d0 from user_log a where log_day=log_day'day'
and app_id||device_id not in( select distinct pp_id||device_id from user_log
where log_day<log_day'day' )
)a left join(
select distinct log_day,app_id||device_id user_id_d1 from user_log where log_day=(log_day'day'+1)
)b on a.user_id_d0=b.user_id_d1 left join(
select distinct log_day,app_id||device_id user_id_d3 from user_log where log_day=(log_day'day'+3)
)c on a.user_id_d0=c.user_id_d3 left join(
select distinct log_day,app_id||device_id user_id_d7 from user_log where log_day=(log_day'day'+7)
)d on a.user_id_d0=d.user_id_d7 left join(
select distinct log_day,app_id||device_id user_id_d30 from user_log where log_day=(log_day'day'+30)
)e on a.user_id_d0=e.user_id_d30
group by a.log_day;
commit;
day:=day+1; --循环
exit when day>=truc(sysdate); --结束条件
end loop;
end;
select * from user_retention_monitior;
二、问题：连续登陆问题（Hive语法）
基础数据准备：
1. 表结构
表名： user_login
字段名：
user_id string comment '用户id'
login_dt string comment '用户登陆时间'
2. 数据格式
user_id login_dt
1 2020-05-01
1 2020-05-02
1 2020-05-04
3. 格式转换（login_dt由字符串格式转为时间格式）
to_date(from_unixtime(UNIX_TIMESTAMP(login_dt,'yyyy-MM-dd'))) as login_dt
连续登陆计算的两个思路：
①可先找出最近几天的日期，然后计算与当期日期的差值。
相应函数：lead/lag + datediff(或date_sub/date_add均可)
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
②先对用户按登陆日期排序；根据登陆日期与排序差值进行分组统计。
相应函数：row_number() + date_sub
思路1：
比如求连续三天登陆，可以将当天上一条和下一条数据获取到，然后保证now-lag=lead-now=1即可：
//2. 要求now-lag=lead-now=1
select user_id from(
//1. 获取当天前后各一条数据
select user_id,login_dt,
lag(login_dt,1) over(partition by user_id order by login_dt) as lag_dt_01,
lead(login_dt,1) over(partition by user_id order by login_dt) as lead_dt_01
from user_login)temp1
where datediff(login_dt,lag_dt_01)=1 and datediff(lead_dt_01,login_dt)=1
如果连续多天，可以取更多数据，或者全用lag/lead函数。
思路2：
//2. 将用户根据登陆时间和序号的差值进行分组，得到连续登陆的起止日期、连续登陆的天数
select user_id,
min(login_dt) as start_dt,
max(login_dt) as end_dt,
count(1) as continue_day
from(
//1. 将用户按登陆时间排序号
select user_id,login_dt,
row_number() over(partition by user_id order by login_dt) as rn from user_login
)temp1
group by user_id,date_sub(login_dt,rn)
若需统计连续大于N天登陆的用户，可结合Having子句进行筛选。
参考链接： 连续登陆问题
三、TOP-N
基础数据准备：
每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志.
访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop.
请统计每个店铺访问次数top3的访客信息.输出店铺名称、访客id、访问次数
思路及代码：
// 3. 取前三名
select shop,user_id,visit_num from(
// 2. 统计每个页面各用户的访问次数排序
select shop,user_id,visit_num,
row_num() over(partition by shop,user_id order by visit_num desc) as visit_sort
from(
// 1. 统计每个用户每个页面的访问次数
select shop,user_id,count(1) as visit_num from visit
group by shop,user_id
)temp1
)temp2
where visit_sort<=3
四、行列互换
1. 将行中数据（数组），拆分为多行，On-to-many maping问题
基础数据准备：
-----------------------------------------------------
原式数据movie表：
movie category
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼 2》 战争,动作,灾难
-----------------------------------------------------
结果数据
movie category_name
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼 2》 战争
《战狼 2》 动作
《战狼 2》 灾难
思路及代码：UDTF函数(User-Defined Table-Generating Functions)与Lateral view结合使用
select movie,category_name from movie
lateral view explode(split(category),',') tmpTable as category_name
2. "多行"转"一行"问题
基础数据准备：
-----------------------------------------------------
原始数据constellation表：
name constellation blood_type
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
-----------------------------------------------------
结果数据
--把星座和血型一样的人归类到一起。结果如下：
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
思路及代码：使用group_concat函数
2. 根据新字段进行多行合并：group_concat()
select base,
group_concat(name separator '|') as name
from(
1. 将星座和血型整合为新字段
select concat(constellation,‘,’，blood_type) as base,name from constellation
)temp1
group by base
若没有group_concat()函数，可以用concat_ws(separator,collect_set(column)) 代替。
五、学生选课情况
1. 类似进行透视功能，将列数据透视为矩阵数据
需求描述：
原始数据
----------------course表-----------------
id course
1 a
1 b
1 c
1 e
2 a
2 c
2 d
2 f
3 a
3 b
3 c
3 e
预期结果：表中的1表示选修，表中的0表示未选修
----------------结果展示------------------
id a b c d e f
1 1 1 1 0 1 0
2 1 0 1 1 0 1
3 1 1 1 0 1 0
思路及代码：
- 以数组形式整理每个学生选课信息；
- 判断数组数据是否在全量数组中。
collect_set()、array_contains()以及case when实现。
3. 判断选课信息是否可匹配到
select id,
case when array_contains(id_course,course[0]) then 1 else 0 end as a,
case when array_contains(id_course,course[1]) then 1 else 0 end as b,
case when array_contains(id_course,course[2]) then 1 else 0 end as c,
case when array_contains(id_course,course[3]) then 1 else 0 end as d,
case when array_contains(id_course,course[4]) then 1 else 0 end as e,
case when array_contains(id_course,course[5]) then 1 else 0 end as f
from(
4. 以数组形式整理每个学生的选课信息
select id,id_course,course from(
select id,collect_set(course) as id_course from course
group by id)as temp1 cross join(
select sort_array(collect_set(course)) as course from course
)as temp2)
关于collect_set() 和 array_contains()函数用法：
- collect_list():根据某个字段分组后，把分在一组的数据合并在一起，默认分隔符’,’ 。
- collect_set():在collect_list()的基础上去重 另：set聚合无序，可以使用sort_array()函数进行排序。
- array_sort():对数组内数据进行排序，sort_array(e: column, asc: boolean)，默认升序排序。
- array_contains():类似于in的用法，array_contains(数组，值) 判断数组中是否有某值。
参考：Hive面试题
扫一扫
3084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
