互联网数据分析中总是会遇到产品的留存率问题, 本案例就是一个实际工作中遇到的需求.
一. demand 业务需求
平台: Android
时间周期: 2015.12.25（3.8版本上线）至今
需求数据: 每日的新增用户数目, 次日留存, 第7日留存
需求解读: 获取安卓平台上, 2015-12-25到现在的每一天的新增用户数, 次日留存和第七日留存率三项指标
次日留存数计算方法: first_date = last_date-1
第七日留存数计算方法: first_date = last_date-6
二. raw data 输入数据
1. 表名
mds_wls_sina_weather_activate_retain_user
2. 表的结构与两条样例数据
处理
字段: NA pt_type NA uid last_date first_date time NA NA pv pid location ISP dt pt_type
记录1 : weather activate 天气通 %2BGSN%3A80869018 2014-11-15 2014-11-08 15:44:15 0 1 3.409 P2866 重庆 电信 20151225 activate
记录2: weather activate 天气通 %2BGSN%3A808AB4C6 2014-08-25 2014-07-27 10:02:25 tq 5010 3.329 - 四川 移通 20151225 activate
3 简化后的数据样式
由于表的原始结构存在一些噪音, 因此我们自己可以对其复制一部分的记录, 然后加以简化, 以便自己思考sql语句的写法.
-- 简化的行记录
20151225(dt) 天气通 %2BGSN%3A80869018(uid) 2015-12-25(last_date) 2015-12-25(first_date) 15:44:15 3.409(ver) P2866(source) 重庆 电信 activate(type) #第一次启动
20151226 天气通 %2BGSN%3A80869018 2015-12-25 2015-12-25 15:44:15 3.409 P2866 重庆 电信 activate
20151227 天气通 %2BGSN%3A80869018 2015-12-25 2015-12-25 15:44:15 3.409 P2866 重庆 电信 activate
20151228 天气通 %2BGSN%3A80869018 2015-12-25 2015-12-25 15:44:15 3.409 P2866 重庆 电信 activate
20151229 天气通 %2BGSN%3A80869018 2015-12-29 2015-12-25 15:44:15 3.409 P2866 重庆 电信 activate #第二次启动
三. 知识点
date_add()函数: date_add(first_date, 6) --给定的date加上一定的天数
case条件语句: sum(case when last_date = first_date then 1 else 0 end) as newuser --类似countif(last_date=first_date)
partition分区: Hive中查找数据的时候, 要尽量利用分区来缩小范围, 这样大大提高了效率. 此处, 分区实际上是date, 因此最好一开始就把date给指定了
regexp_replace(string, str1, str2): regexp_replace(last_date,'-','') //使用str2替换string中的所有str1
最重要的: sum函数的特性sum函数
这个网页中, 如果groupby Customer的话, 那就会出现针对Bush, Carter, Adams三个人的三条Sum(OrderPrice)结果.
select custname, sum(orderDeal) from tb_bill group by custnamegroup by可以与聚合函数搭配, 也就是说前面如果有一个sum(OrderPrice) 或者 count(*)这样的对数值加总的函数的话, 后面常常要带group by.
四. 代码
--七天留存
-- pt_type代表portal_type
-- pv代表portalVersion
-- pid: 渠道号
-- uid: userID
-- 本例子找出来的结果是求2015-12-25~2015-12-31这七天, 即2015-12-25新增用户在2015-12-31当天的留存数目
select first_date,pid,pv,count(1) uv from
(select first_date,pid,pv,uid from mds_wls_sina_weather_activate_retain_user
where dt = '20151231' and pt_type = 'activate'
and regexp_replace(last_date,'-','') = dt -- last-date限定等于2015-12-31
and last_date = date_add(first_date,6) --last-date同时限定等于first_data+6
and pid in ('x','y')
and pv in ('x','y')
group by first_date,pid,pv,uid) as t1 --用first_date, pid, pv, uid字段全部上， 代表各个记录不能重复。
group by first_date,pid,pv -- 仍然表示去重
-- 2015-12-25以来指定渠道集合, 指定版本集合的2ndretain和7threttain
-- 记录可能是同一个用户1001在20151228为first_date, 之后一直用到了20160215, 之间20151228~20160215每天都作为last_date生成了七八十行的记录
-- pt平台
hive -e"select first_date, pid, pv,
sum(case when last_date = first_date then 1 else 0 end) as newuser,
sum(case when last_date = date_add(first_date,1) then 1 else 0 end) as 2nd_retain,
sum(case when last_date = date_add(first_date,6) then 1 else 0 end) as 7th_retain
from mds_wls_sina_weather_activate_retain_user
where dt between '20151225' and '20170205' and pt in ('1','5010') -- 限定日期范围
and pv in ('3.809','3.819','3.829','3.839','3.909','3.929','3.939','5.009','5.039','5.059','5.109','5.159','5.209','5.309') --限定版本
and pid in ('s6001','s6008','s6000','s2007','s2012','s2010','s3016','s6005','s7340','s3015','p372','s2009','s3017','free','s6004','s6007','s2004','s6010','s6003','s3011','p303','p331','p400','s3014','s3013','s7261','p306','s2011','s6006','p858','s4000','s3012','s7021','s6002','p888') -- 限定渠道
and pt_type = 'activate' and regexp_replace(last_date,'-','') = dt -- 2/1 2/1 2/1 yes =>2/2 2/1 2/1 no ==> 2/3 2/1 2/1 no, 如果这里不做限制, 到时候算last_date=first_date 可能会有5个记录, 那么这样就会多加了不少了.
and first_date >= '2015-12-25' -- first_date必须在指定日期12/25后
and (last_date = first_date or last_date = date_add(first_date,1) or last_date = date_add(first_date,6)) -- 限定满足新用户, 次日, 七日三个之一
group by first_date, pid, pv">cxl_08.txt -- 对first_date, 渠道, 版本做去重, 准备用于sum, 可以查询上面Group By Customer的例子
--通过first_date, pid, pv确定一个独特的来自某个渠道, 版本, 某天的新增用户,以及它之后的表现
extra: 补充解释
对regexp那行代码含义的解释:
20151225(dt) 天气通 %2BGSN%3A80869018(uid) 2015-12-25(last_date) 2015-12-25(first_date) 15:44:15 3.409(ver) P2866(source) 重庆 电信 activate(type) #第一次启动
20151226 天气通 %2BGSN%3A80869018 2015-12-25 2015-12-25 15:44:15 3.409 P2866 重庆 电信 activate
20151227 天气通 %2BGSN%3A80869018 2015-12-25 2015-12-25 15:44:15 3.409 P2866 重庆 电信 activate
20151228 天气通 %2BGSN%3A80869018 2015-12-25 2015-12-25 15:44:15 3.409 P2866 重庆 电信 activate
20151229 天气通 %2BGSN%3A80869018 2015-12-29 2015-12-25 15:44:15 3.409 P2866 重庆 电信 activate #第二次启动
解释: 如果group by first_date, pv, pid
那么就是如上5条记录落入对2015-12-25, 3.409, P2866这个group by形成的独特组合(我们称作该组合, 其在最后的excel结果中是一条行记录),
因此, sum(case when last_date = first_date then 1 else 0 end) as newuser这个语句, 对该组合来说, %2BGSN%3A80869018这个用户会给这个值贡献了4, 而不是正常情况该有的1(因为他只是一个新增用户).
所以, 我们必须加上regexp_replace(last_date,'-','') = dt这个限定条件
