Hive统计新增,日活和留存率

原文

用户行为触发的日志上报,已经存放在Hive的外部分区表中.
结构如下:

image

主要字段内容
dt表示日期,如20160510
platform表示平台,只有两个选项,苹果和安卓
mid是用户机器码,类似于网卡MAC地址什么的
pver是版本
channel是分发渠道

现在产品经理需要统计每天用户的新增,日活和留存率.
其中
留存率的概念是,如果用户在5月1日第一次使用我们的产品。
如果5月2日他还使用了,那么5月1日的“一日留存”加一.
同理5月3日他又使用率，5月1日的“两日留存”加一.
5月1日的“一日留存率”=5月1日“一日留存” / 5月1日新增用户数量.

先创建一个表,记录用户首次使用的日期.

image

dt是用户首次使用的日期,比如 20160510
cver是版本
pcid是用户机器码,就是原始日志表的mid

然后创建一个每天数据的存放表,统计昨天一天的新增,激活和留存.

image

dt是日期
**type 1:新增 2:留存 3:日活**
num 是用户数量,
dtdiff仅仅用于计算留存,说明用户使用和首次使用的日期间隔多少天.

1.Hive统计每天新增用户
$dt是shell传入的变量
dt=$(date -d last-day +%Y%m%d)
该脚本每天凌晨执行,统计昨天的数据.
每次执行,先清空report_userinfo表
truncate table report_userinfo;

insert into user_login_history   
select platform,min(dt),channel,cver,mid,1 from log_vvim  
where   mid not in (select pcid from user_login_history where type=1)  
and mid is not null  
and dt=$dt  
group by platform,channel,cver,mid; 
这个意思就是 原来没有记录在user_grouproom_login_history表中的pcid,如果出现在昨天的日志表中,则说明用户是新增的.

然后将昨天新增的用户数量写入

insert into report_userinfo   
select platform,dt,channel,cver,type,count(*) num,-1 from user_login_history    
where type=1   
and dt=$dt  
group by platform,dt,channel,cver,type; 

2.统计每天激活用户数量

insert into report_userinfo   
select platform,dt,channel,cver,3,count(distinct mid),-1 from log_vvim   
where   
mid is not null and dt=$dt  
group by dt,platform,channel,cver;  

这个倒是简单,根据原始的日志表,统计今天使用过的pcid,经过去重,就是今天的日活用户量.

3.统计留存率.

INSERT INTO report_userinfo   
SELECT   
xinzeng.platform,  
xinzeng.dt,  
xinzeng.channel,  
xinzeng.cver,  
2,  
COUNT(DISTINCT cunliu.pcid),  
DATEDIFF(  
    FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(cunliu.dt AS STRING),'yyyyMMdd')),  
    FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(xinzeng.dt AS STRING),'yyyyMMdd'))  
)  
FROM  
(  
    SELECT * FROM user_login_history WHERE TYPE=1  
) xinzeng  
INNER JOIN   
(  
    SELECT   
    platform,  
    dt,  
    channel,  
    cver,  
    MID pcid   
    FROM log_vvim   
    WHERE MID IS NOT NULL AND dt=$dt GROUP BY platform,dt,channel,cver,MID  
) cunliu ON  
(  
    xinzeng.platform=cunliu.platform AND  
    xinzeng.channel=cunliu.channel AND  
    xinzeng.cver=cunliu.cver AND  
    xinzeng.pcid=cunliu.pcid  
)  
WHERE cunliu.dt>xinzeng.dt   
GROUP BY   
xinzeng.platform,xinzeng.dt,xinzeng.channel,xinzeng.cver,  
DATEDIFF(  
    FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(cunliu.dt AS STRING),'yyyyMMdd')),  
    FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(xinzeng.dt AS STRING),'yyyyMMdd'))  
);  

该SQL主要计算昨天使用过的用户,他的首次使用日期,然后计算差值

image

表示安卓平台,20160425那天首次使用的用户,在8天之后,还使用过的用户数量为20人。

因为计算新增和日活在计算留存之前,
cunliu.dt>xinzeng.dt
主要是确定当天新增的用户不计入留存率计算.

统计完成之后,将hive表导入MySQL
sqoop export --connect jdbc:mysql://IP:端口/report --username uname --password "pwd" --table report_userinfo --export-dir '/user/hive/warehouse/logs.db/report_userinfo' --fields-terminated-by '\001'

最终通过报表展现