利用HQL统计: 新增用户数，日活，留存率

1.每天新增用户数怎么实现？ 2.日活怎么 实现 ？ 3.留存率怎么 实现 ？ 用户行为触发的日志上报,已经存放在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表 [SQL]  纯文本查看  复制代码 ? 1 2 3 4 5 6 7 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,如果出现在昨天的日志表中,则说明用户是新增的. 然后将昨天新增的用户数量写入 [SQL]  纯文本查看  复制代码 ? 1 2 3 4 5 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.统计每天活跃用户数量 [SQL]  纯文本查看  复制代码 ? 1 2 3 4 5 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.统计留存率. [SQL]  纯文本查看  复制代码 ? 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 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主要计算昨天使用过的用户,他的首次使用日期,然后计算差值   表示安卓平台,20160425那天首次使用的用户,在8天之后,还使用过的用户数量为20人。 因为计算新增和日活在计算留存之前, cunliu.dt>xinzeng.dt 主要是确定当天新增的用户不计入留存率计算. 统计完成之后,将hive表导入MySQL [Shell]  纯文本查看  复制代码 ? 1 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' 最终通过报表展现