hive留存率的统计

留存率：某日注册的用户，在之后几天是否活跃，一日留存率就是用户注册后第二天仍然活跃，以此类推，三日留存率，七日留存率。
任务：
计算所有用户注册后的一日留存率，三日留存率，七日留存率，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_retentionselect * from ads_user_retentionunionselect   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