Hive统计新增,日活和留存率
来源:互联网 发布:php header image 编辑:程序博客网 时间:2024/06/17 04:49
用户行为触发的日志上报,已经存放在Hive的外部分区表中.
结构如下:
![](http://blog.itpub.net/attachment/201605/10/29254281_1462874792KIo0.png)
主要字段内容
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
然后创建一个每天数据的存放表,统计昨天一天的新增,激活和留存.
![](http://blog.itpub.net/attachment/201605/10/29254281_1462875043F67l.png)
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;
这个意思就是 原来没有记录在user_grouproom_login_history表中的pcid,java如果出现在昨天的日志表中,则说明用户是新增的.
然后将昨天新增的用户数量写入
2.统计每天激活用户数量
这个倒是简单,根据原始的日志表,统计今天使用过的pcid,经过去重,java培训中的用户就是今天的日活用户量.
3.统计留存率.
该SQL主要计算昨天使用过的用户,他的首次使用日期,然后计算差值
![](http://blog.itpub.net/attachment/201605/10/29254281_1462876358Pifj.png)
表示安卓平台,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'
最终通过报表展现
结构如下:
![](http://blog.itpub.net/attachment/201605/10/29254281_1462874792KIo0.png)
主要字段内容
dt表示日期,如20160510
platform表示平台,只有两个选项,苹果和安卓
mid是用户机器码,类似于网卡MAC地址什么的
pver是版本
channel是分发渠道
现在产品经理需要统计每天用户的新增,日活和留存率.
其中
留存率的概念是,如果用户在5月1日第一次使用我们的产品。
如果5月2日他还使用了,那么5月1日的“一日留存”加一.
同理5月3日他又使用率,5月1日的“两日留存”加一.
5月1日的“一日留存率”=5月1日“一日留存” / 5月1日新增用户数量.
先创建一个表,记录用户首次使用的日期.
![](http://blog.itpub.net/attachment/201605/10/29254281_1462875097PysH.png)
dt是用户首次使用的日期,比如 20160510
cver是版本
pcid是用户机器码,就是原始日志表的mid
然后创建一个每天数据的存放表,统计昨天一天的新增,激活和留存.
![](http://blog.itpub.net/attachment/201605/10/29254281_1462875043F67l.png)
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,java如果出现在昨天的日志表中,则说明用户是新增的.
然后将昨天新增的用户数量写入
- 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,经过去重,java培训中的用户就是今天的日活用户量.
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主要计算昨天使用过的用户,他的首次使用日期,然后计算差值
![](http://blog.itpub.net/attachment/201605/10/29254281_1462876358Pifj.png)
表示安卓平台,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'
最终通过报表展现
0 0
- Hive统计新增,日活和留存率
- Hive统计新增,日活和留存率
- Hive统计新增,日活和留存率, 使用sqoop导出到MYSql
- 利用HQL统计: 新增用户数,日活,留存率
- Hive留存率统计
- 游戏玩家的留存率统计实现
- 使用Google Analytics统计用户留存率
- MongoDb 用 mapreduce 统计留存率
- 游戏玩家的留存率统计实现
- mysql 查询次日留存 三日留存 七日留存
- mysql 查询次日留存 三日留存 七日留存
- 用户留存(app)统计
- [Hive]Hive分区表新增字段
- mysql统计用户七日留存存储过程
- 用Redis bitmap统计活跃用户、留存
- 用Redis bitmap统计活跃用户、留存
- 用Redis bitmap统计活跃用户、留存
- 用Redis bitmap统计活跃用户、留存
- 如何使用ip代理爬虫
- vm_area_struc 和 vm_struct
- spring mvc 整合 jdbc 实现Web后端数据库
- 长按按钮让EditText中的数字递增
- 视频电商网站视频上传准备:vue+七牛JSSDK集成(2)上传视频时显示进度条
- Hive统计新增,日活和留存率
- 验证码生成工具类:VerifyCodeUtil
- WebSite---前台系统图片验证码心得
- 【OpenGL】OpenGL 2D平移与旋转
- java程序运行突然中断异常
- string类
- ORACLE--死锁
- Spring之IOC容器的实现
- 解决页面跳转 返回刷新还是不刷新问题