需求背景
留存率：某日注册的用户，在之后几天是否活跃，一日留存率就是用户注册后第二天仍然活跃，以此类推，三日留存率，七日留存率。
任务：
计算所有用户注册后的一日留存率，三日留存率，七日留存率，14日留存率，30日留存率，60日留存率，90日留存率。
建表
use default; show tables; create table register_tbl( user_id string, register_date string) row format delimited fields terminated by ',' location "/HiveTestDB/retention/re" tblproperties("skip.header.line.count"="1"); create table active_tbl( user_id string, active_date string) row format delimited fields terminated by ',' location "/HiveTestDB/retention/ac" tblproperties("skip.header.line.count"="1"); load data local inpath "/home/hadoop/register_tbl.csv" into table register_tbl; load data local inpath "/home/hadoop/active_tbl.csv" into table avtive_tbl; select * from register_tbl; select * from active_tbl; 建留存率表 ...
思路
说明：
register_tbl表记录了所有用户的注册日期，
active_tbl是用户所有活动日期；
步骤;
register_tbl左关联active_tbl，如果要计算特定时间范围内注册用户的留存率，通过where子句过滤。 按用户和注册日期分组，计算组内活跃日期与注册日期差值分别为1,3,7,14,30,60,90的记录的个数，通过distinct实现有为1，没有为0 由此计算全部用户中留存一天、三天和七天的个数及比例 此留存率需要滚动式进行更新，故需要和原留存率表的数据进行union
sql实现
insert overwrite table ads_user_retention select * from ads_user_retention union select count(*) total, sum(d1) 1d_Retent, sum(d3) 3d_Retent, sum(d7) 7d_Retent, sum(d14) 14d_Retent, sum(d30) 30d_Retent, sum(d60) 60d_Retent, sum(d90) 90d_Retent, sum(d1)/count(*) 1d_Retent_Rate, sum(d3)/count(*) 3d_Retent_Rate, sum(d7)/count(*) 7d_Retent_Rate, sum(d14)/count(*) 14d_Retent_Rate, sum(d30)/count(*) 30d_Retent_Rate, sum(d30)/count(*) 30d_Retent_Rate, sum(d60)/count(*) 60d_Retent_Rate, sum(d90)/count(*) 90d_Retent_Rate, from (select uid,rd, count(distinct if(datediff(ad,rd) = 1, 1, null)) d1, // 留存天数 # 也可以 if(count(datediff(ad,rd) = 1 or null)>0,1,null),避免使用distinct count(distinct if(datediff(ad,rd) =3, 1, null)) d3, count(distinct if(datediff(ad,rd) =7, 1, null)) d7, count(distinct if(datediff(ad,rd) =14, 1, null)) d14, count(distinct if(datediff(ad,rd) =30, 1, null)) d30, count(distinct if(datediff(ad,rd) =60, 1, null)) d60, count(distinct if(datediff(ad,rd) =90, 1, null)) d90 from (select r.user_id uid,from_unixtime(unix_timestamp(r.register_date,'yyyy-MM-dd')) rd, // 留存日期 from_unixtime(unix_timestamp(a.active_date,'yyyy-MM-dd')) ad // 活跃日期 from register_tbl r left join active_tbl a on r.user_id = a.user_id) s1 group by uid,rd ) ads_user_retention
