用户留存率分析 表设计

 

本人日常java开发一枚，由于公司产品需要开始做数据开发，用户留存分析，只有用户的访问日志：

访问时间，访问平台，设备类型，等 其他需要根据用户所有属性（国家、内外部用户，以及用户角色等来进行筛选统计）

 

用户留存 ---（日留存 周留存 月留存 ）

某天注册的用户数，2日留存率，  7日留存率，  10日留存率，  30日留存率（比例）

 2020-10-22                23                       34%                45%              23%  

 2020-10-21                 11                      34%                45%              23%  

 2020-10-22                 11                      34%                45%              23%  

 

刚开始不懂，打算按天去遍历查询汇总查询当前的新用户ids,再作为入参聚合不同查询条件维度的数据后来发现真是傻啊，咨询了厉害的越姐和网上查询的资料 分析得出 如下的表设计模式

1、首先  聚合出用户uid   visitdate 作为唯一主键的date_visit_form_log; 记录每天用户访问记录

--  用户访问天数记录表
drop  table if EXISTS `uat_db`.t_basf_user_rention_form_log;
CREATE EXTERNAL TABLE `uat_db`.t_basf_user_rention_form_log(
uid  bigint,
visit_date           int,
form_visit_date   String
);
insert overwrite table `uat_db`.t_basf_user_rention_form_log
select distinct uid,visit_date,from_unixtime(unix_timestamp(cast(visit_date as string),'yyyymmdd'),'yyyy-mm-dd') form_visit_date from `uat_db`.t_basf_user_data_form_log;

--  用户访问周记录表
drop  table if EXISTS `uat_db`.t_basf_user_rention_form_week_log;
CREATE EXTERNAL TABLE `uat_db`.t_basf_user_rention_form_week_log(
uid  bigint,
visit_weekth           int
);
insert overwrite table `uat_db`.t_basf_user_rention_form_week_log
select distinct uid,visit_weekth from `uat_db`.t_basf_user_data_form_log;

2、基于上面的记录表 来统计1天 3天  7天 30天的用户记录

-- 用户留存数据表 （hive脚本 由于不支持left join的后面的and关键词 导致left join了很多内容，如果有网友看出问题能更优化一下脚本 不胜感激）
select temp.*, h_basf_user.is