在互联网行业中,用户在某段时间内开始使用应用,经过一段时间后,仍然继续使用该应用的用户,被认作是留存用户,这部分用户占当时新增用户的比例即是留存率,会按照每隔1单位时间(例日、周、月)来进行统计。顾名思义,留存指的就是“有多少用户留下来了”。留存用户和留存率体现了应用的质量和保留用户的能力,那么我们怎么样计算留存率呢?看下面这个例子:
如上示例所示,我们如何通过Hive Sql 或者 Spark Sql 解决这个问题呢
我们将问题拆开,先求某一天的数据,比如我们求20180501 这一天的的用户活跃数,次日留存用户数,三日留存用户数,七日留存用户数:SQL如下:
select a.dayno 日期,
count(distinct a.uid) 活跃用户数,
count(distinct b.uid) 次日留存用户数,
count(distinct c.uid) 三日留存用户数,
count(distinct d.uid) 七日留存用户数
from act_user_info a
left join act_user_info b on a.uid = b.uid and b.dayno = a.dayno + 1
left join act_user_info c on a.uid = c.uid and c.dayno = a.dayno + 3
left join act_user_info d on a.uid = d.uid and d.dayno = a.dayno + 7
group by a.dayno
我们知道: 留存率= 某日新增的用户数,过后几日还登录的用户数 / 某日新增的用户数*100% ,由此我们可以得出
select aa.dayno 日期,
aa.活跃用户数,
aa.次日留存用户数,
aa.三日留存用户数,
aa.七日留存用户数,
concat(round(100 * 次日留存用户数/活跃用户数, 2), '%') 次日留存率,
concat(round(100 * 三日留存用户数/活跃用户数, 2), '%') 三日留存率,
concat(round(100 * 七日留存用户数/活跃用户数, 2), '%') 七日留存率
from (
select a.dayno 日期,
count(distinct a.uid) as 活跃用户数,
count(distinct b.uid) as 次日留存用户数,
count(distinct c.uid) as 三日留存用户数,
count(distinct d.uid) as 七日留存用户数
from act_user_info a
left join act_user_info b on a.uid = b.uid and b.dayno = a.dayno + 1
left join act_user_info c on a.uid = c.uid and c.dayno = a.dayno + 3
left join act_user_info d on a.uid = d.uid and d.dayno = a.dayno + 7
group by a.dayno
) aa;
至此我们求出了答案,我们对此问题升级:比如需要求如下的值:
针对这两个问题,我们又该怎么解决。
首先我们理解下题目,7日内的留存,也就是从次日一直到底七日的留存,当然我们也可以利用上面的方式计算,但是上面的方式要是计算180日内的留存就很麻烦了,那针对于这种连续日求留存我们又应该怎么求呢,同理我们先将问题拆分
首先我们需求求出连续7日内的留存数,如下:
select a.dayno 日期,
(b.dayno - a.dayno) as days,
count(distinct a.uid) as 活跃用户数,
count(distinct b.uid) as n日留存用户数,
from act_user_info a
left join act_user_info b on a.uid = b.uid and b.dayno > a.dayno and b.dayno - a.dayno >=180
group by a.dayno,days
order by days asc
进而我们求出SQL 如下:
select aa.日期,
aa.活跃用户数,
concat(aa.days, '日用户留存'),
aa.n日留存用户数,
concat(round(100 * aa.n日留存用户数/aa.活跃用户数, 2), '%') as n日留存用户数
from (
select a.dayno 日期,
(b.dayno - a.dayno) as days,
count(distinct a.uid) as 活跃用户数,
count(distinct b.uid) as n日留存用户数,
from act_user_info a
left join act_user_info b on a.uid = b.uid and b.dayno > a.dayno and b.dayno - a.dayno >=180
group by a.dayno,days
order by days asc) as aa
再进一步,我们如何求出连续180日的活跃用户呢,我们先分析这个问题,连续180日的活跃用户 也就是这个用户出现在180日内的每一天,具体sql如下:
select bb.uid, count(1) as num from
(select
distinct b.dayno,
b.uid,
from act_user_info a
inner join act_user_info b on a.uid = b.uid and b.dayno > a.dayno
and b.dayno - a.dayno >=180) as bb
group by bb.uid
where count(1) >=180
至此我们对留存率和留存用户数据的计算到此结束