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

转载 2016年06月01日 13:30:04
用户行为触发的日志上报,已经存放在Hive的外部分区表中.
结构如下:


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


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

先创建一个表,记录用户首次使用的日期.
 
dt是用户首次使用的日期,比如 20160510
cver是版本
pcid是用户机器码,就是原始日志表的mid

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


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;
  1. insert into user_login_history   
  2. select platform,min(dt),channel,cver,mid,1 from log_vvim  
  3. where   
  4. mid not in (select pcid from user_login_history where type=1)  
  5. and mid is not null  
  6. and dt=$dt  
  7. group by platform,channel,cver,mid;  

这个意思就是 原来没有记录在user_grouproom_login_history表中的pcid,java如果出现在昨天的日志表中,则说明用户是新增的.

然后将昨天新增的用户数量写入
  1. insert into report_userinfo   
  2. select platform,dt,channel,cver,type,count(*) num,-1 from user_login_history    
  3. where type=1   
  4. and dt=$dt  
  5. group by platform,dt,channel,cver,type; 

2.统计每天激活用户数量
  1. insert into report_userinfo   
  2. select platform,dt,channel,cver,3,count(distinct mid),-1 from log_vvim   
  3. where   
  4. mid is not null and dt=$dt  
  5. group by dt,platform,channel,cver;  

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

3.统计留存率.
  1. insert into report_userinfo   
  2. select   
  3. xinzeng.platform,  
  4. xinzeng.dt,  
  5. xinzeng.channel,  
  6. xinzeng.cver,  
  7. 2,  
  8. count(distinct cunliu.pcid),  
  9. datediff(  
  10.     from_unixtime(unix_timestamp(cast(cunliu.dt as string),'yyyyMMdd')),  
  11.     from_unixtime(unix_timestamp(cast(xinzeng.dt as string),'yyyyMMdd'))  
  12. )  
  13. from  
  14. (  
  15.     select * from user_login_history where type=1  
  16. ) xinzeng  
  17. inner join   
  18. (  
  19.     select   
  20.     platform,  
  21.     dt,  
  22.     channel,  
  23.     cver,  
  24.     mid pcid   
  25.     from log_vvim   
  26.     where mid is not null and dt=$dt group by platform,dt,channel,cver,mid  
  27. ) cunliu on  
  28. (  
  29.     xinzeng.platform=cunliu.platform and  
  30.     xinzeng.channel=cunliu.channel and  
  31.     xinzeng.cver=cunliu.cver and  
  32.     xinzeng.pcid=cunliu.pcid  
  33. )  
  34. where cunliu.dt>xinzeng.dt   
  35. group by   
  36. xinzeng.platform,xinzeng.dt,xinzeng.channel,xinzeng.cver,  
  37. datediff(  
  38.     from_unixtime(unix_timestamp(cast(cunliu.dt as string),'yyyyMMdd')),  
  39.     from_unixtime(unix_timestamp(cast(xinzeng.dt as string),'yyyyMMdd'))  
  40. );  

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


表示安卓平台,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'

最终通过报表展现

关于hive统计周wau、保留率需求的几种思路

说明:程序有快慢之分,业务却有千变万化之状,不同的需求对症下药,选择合适的解法。 wau:指的是用户一周之内登陆的独立用户id数 wau保留率:指的是后续每周登陆的独立用户id与第一周登陆独立用户...
  • u012914981
  • u012914981
  • 2014年01月09日 20:12
  • 2739

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

用户行为触发的日志上报,已经存放在Hive的外部分区表中. 结构如下: 主要字段内容 dt表示日期,如20160510 platform表示平台,只有两个选项,苹果和安卓 mid是用...
  • sysmedia
  • sysmedia
  • 2017年03月08日 14:55
  • 1014

mysql 查询次日留存 三日留存 七日留存

--sumdate--返回结果的注册日期 --REGDATE--子表注册日期 --LOGINDATE--次日登陆日期 --diff1--次日留存人数 --diff3--三日...
  • z479403374
  • z479403374
  • 2017年07月27日 11:20
  • 508

Spark Streaming: 保存上一个批次数据的计算结果

在Spark Streaming计算模型下,有时候我们对当前 batch 数据的计算需要依赖上一个 batch 的计算结果,如广告系统中检索日志和曝光时间的join拼接。这时可以使用JavaStrea...
  • tracker_w
  • tracker_w
  • 2017年12月08日 19:25
  • 212

游戏玩家的留存率统计实现

玩家在某段时间内注册开始游戏,经过一段时间后,仍然继续游戏的被认作是留存;这部分用户占当时新增用户的比例即是留存率,会按照每隔1单位时间(例日、周、月)来进行统计。顾名思义,留存指的就是“有多少玩家留...
  • jiangguilong2000
  • jiangguilong2000
  • 2013年11月14日 16:50
  • 11318

scala实战之spark用户在线时长和登录次数统计实例

接触spark后就开始学习scala语言了,因为有一点python和java的基础学习起来还行,今天在这里把我工作中应用scala编程统计分析用户行为日志的实例和大家分析一下,我这里主要讲一下用户的在...
  • zfszhangyuan
  • zfszhangyuan
  • 2016年09月14日 15:05
  • 7866

Scala 强大的集合数据操作示例

Scala是数据挖掘算法领域最有力的编程语言之一,语言本身是面向函数,这也符合了数据挖掘算法的常用场景:在原始数据集上应用一系列的变换,语言本身也对集合操作提供了众多强大的函数,本文将以List类型为...
  • pzw_0612
  • pzw_0612
  • 2015年05月23日 18:03
  • 66160

一种计算留存的思路

在一些统计系统中,为了观察用户的粘度,我们会计算一系列的叫做留存的指标——次日注册留存、2日注册留存...N日注册留存,比如昨天注册了1000名用户中,在今天有300名用户又登录了,那么对应于昨天的注...
  • cnweike
  • cnweike
  • 2013年04月13日 18:06
  • 2237

利用Hive进行数据分析

近十年来,随着Hadoop生态系统的不断完善,Hadoop早已成为大数据事实上的行业标准之一。面对当今互联网产生的巨大的TB甚至PB级原始数据,利用基于Hadoop的数据仓库解决方案Hive早已是Ha...
  • wh_springer
  • wh_springer
  • 2016年07月06日 21:30
  • 7695

MySQL 使用存储过程实现留存率

DELIMITER // USE `resourcemanage-dev`// DROP PROCEDURE IF EXISTS `stat_remain_player`// CREATE...
  • u012966918
  • u012966918
  • 2015年09月16日 18:05
  • 1724
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Hive统计新增,日活和留存率
举报原因:
原因补充:

(最多只允许输入30个字)