1. 指标口径说明
次日留存:第一天使用产品的用户,在第二天依旧使用的用户数
多日留存:第一天使用产品的用户,在多日后依旧使用的用户数
连续活跃人数:连续多少天使用产品的人数
2. 原始方案 - 次日留存,多日留存,连续活跃用户
2.1 表字段说明:
表共有四个字段:uid,action,product,date
uid:用户id,用户的唯一标识
action:用户在某产品中的行为
product:用户使用的某产品id
date:用户使用某产品的日期
2.2 留存计算原始方案 - 左连接
思路:
- 在uid,date的粒度去重,使用group by uid,date方法
- 根据id字段和date字段进行左连接,右表date需大于左表date
- 根据左表id, date分组聚合,计算多日留存人数。如:右表date-左表date=1即该用户在以左表date为基准的基础上,在第二天依旧有活跃,也就是我们所说的次日留存
- 分别得到需要的多日留存即可
本次讲解将基于mysql的视图功能依次实现
第一步:
CREATE VIEW retention1 AS
SELECT
uid,
date
FROM
temp
GROUP BY
uid,date
第二步:
CREATE VIEW retention2 AS
SELECT
r1.uid AS r1_uid,
r1.date AS r1_date,
r2.date AS r2_date
FROM
retention1 AS r1
LEFT JOIN
retention1 AS r2 ON r1.uid = r2.uid AND r2.date > r1.date
第三步:
CREATE VIEW retention3 AS
SELECT
t1_date AS date
,COUNT(DISTINCT r1_uid) AS day0_count
,COUNT(DISTINCT IF(DATEDIFF(t2_date,t1_date)=1,r1_uid,NULL)) AS day1_count
,COUNT(DISTINCT IF(DATEDIFF(t2_date,t1_date)=3,r1_uid,NULL)) AS day3_count
,COUNT(DISTINCT IF(DATEDIFF(t2_date,t1_date)=7,r1_uid,NULL)) AS day7_count
FROM
retention2
GROUP BY
t1_date
其中,day0_count,day1_count,day3_count,day7_count分别为第一天人数,次日留存人数,3日留存人数,7日留存人数。
缺点:这种实现方案会将完整的表数据做笛卡尔积,数据量级大,时间成本高,占用资源多。
注意事项:这里为什么用左连接呢,原因在于我们将左表作为了计算留存的基准日表,所以说,右表的数据在连接后可以不存在,但是左表的日期是需要留下来的。如果使用的是inner join,那么最终计算出来的留存人数会和首日活跃人数相等,也就是说,留存率=100%,这是不正确的。
2.3 连续活跃人数计算原始方案 - row_number计算tag
2.3.1. 窗口函数row_number() over()统计连续数字讲解
假设有如下数据:
digit | row_number | tag |
1 | 1 | 0 |
2 | 2 | 0 |
3 | 3 | 0 |
4 | 4 | 0 |
6 | 5 | 1 |
7 | 6 | 1 |
9 | 7 | 2 |
其中,tag = digit - row_number
可以发现,digit如果是连续的,那么tag也会是一样的数字。
2.3.2 实现方案
同样的思想我们也可以用在计算连续活跃用户,如果用户连续活跃,那么活跃的日期必然是连续的,这时可以对每个用户date - row_numebr找到tag,再根据tag统计。
假设该表已经根据uid和date去重完毕,本次查找近一月内的连续活跃用户信息
计算一月内连续登陆3天的用户数,思路:
- 筛选出近一月内的用户,并根据date - ROW_NUMBER() OVER(...)判断活跃日期是否连续
- 根据uid和第一步中生成的tag分组,统计每个用户的每一段连续活跃日期的天数
- 通过HAVING COUNT(1) = 3,连续活跃日期为3的用户
- 根据该结果进行统计总用户数
第一步:
SELECT
uid,
date - ROW_NUMBER() OVER(PARTITION BY uid ORDER BY date) as tag
FROM
table
WHERE
date > CUR_DATE() - 30
第二步:
SELECT
DISTINCT uid,
COUNT(1)
FROM
(
SELECT
uid,
date - ROW_NUMBER() OVER(PARTITION BY uid ORDER BY date) as tag
FROM
table
WHERE
date > CUR_DATE() - 30
) t1
GROUP BY
uid,tag
第三步:
--连续三天登录
SELECT
DISTINCT uid,
COUNT(1)
FROM
(
SELECT
uid,
date - ROW_NUMBER() OVER(PARTITION BY uid ORDER BY date) as tag
FROM
table
WHERE
date > CUR_DATE() - 30
) t1
GROUP BY
uid,tag
HAVING COUNT(1) = 3;
第四步:
在第三步的基础上计数即可。
3. 优化方案 - 次日留存,多日留存,连续活跃用户
3.1 留存计算优化方案1 - 基于原始方案的筛选提前
前面说了原始方案的缺点是使用了完整的数据做笛卡尔积,然而正常工作中只需要特定日期的留存,也就是说,我们只需确定某个基准日,按照这个基准日计算多日留存。
假设以6月18日为基准日,计算多日留存,有如下思路:
- 与之前一样,在uid,date的粒度去重,使用group by uid,date方法
- 以左表6月18日为基准日,与右表6月19日,6月21日为需要计算的次日留存日和三日留存日左连接
- count计算留存人数,若右表uid为空则代表该用户以6月18日为基准日,次日未使用产品或三日未使用产品。
第一步如前所示
第二步:
(
SELECT
uid
FROM
retention1
WHERE
date = "2021-06-18"
) AS a
LEFT JOIN
(
SELECT
uid
FROM
retention1
WHERE
date in ("2021-06-19","2021-06-21")
) AS b ON a.uid = b.uid
第三步:
SELECT
a.date AS day0,
COUNT(
IF(b.date = "2021-06-19" AND b.uid IS NOT NULL, a.uid, NULL)
) AS day1_count,
COUNT(
IF(b.date = "2021-06-21" AND b.uid IS NOT NULL, a.uid, NULL)
) AS day3_count
FROM
(
SELECT
uid
date,
FROM
retention1
WHERE
date = "2021-06-18"
) AS a
LEFT JOIN
(
SELECT
uid
date,
FROM
retention1
WHERE
date in ("2021-06-19","2021-06-21")
) AS b ON a.uid = b.uid
通过这种方式实现后,与原始方案相比,显然数据量级的计算资源上小了相当多,因为我们只计算我们需要的日期留存即可。
3.2 留存计算优化方案2 - 基于bitmap的思想
3.2.1 概念解释
不了解bitmap的朋友也无需担心,不影响理解。
如果有想要了解的朋友,也可以参考漫画:什么是Bitmap算法? - 知乎。
首先,需要优化表结构,新增mapset字段,字段类型varchar或text
假设表数据如下:
uid | mapset | date |
1 | 1,1,1,0,1,0,1,0 | 2021/6/18 |
2 | 0,0,1,1,0,1,1,0 | 2021/6/18 |
3 | 1,1,1,1,0,1,1,1 | 2021/6/18 |
date是以当前日(假设今天是7月18日)为基准日的一月前的日期
在mapset字段中,1表示活跃,0表示不活跃。
以uid为1的用户为例,mapset字段值为1,1,1,0,1,0,1分别表示该用户6月18~6月20日是活跃的,在6月21日停止活跃1天,依次类推。(最左边的第一个数字代表6月18日当天是否活跃,依次类推)
3.2.2 留存计算
了解该思想后,我们继续算留存吧。如何算留存6月18日的次日留存和七日留存呢?-- 暂停思考1分钟
想到了吗?只需要mapset的前两个数字都为1,就代表该用户是第二天留存用户,然后再count(uid)就可以了。
实现如下:
第一步:分别找出第一天活跃且在次日或七日有活跃的用户
SELECT
uid,
bigint(split(mapset,",")[0]) as active0,
bigint(split(mapset,",")[1]) as active1,
bigint(split(mapset,",")[7]) as active7,
FROM
temp
WHERE
date = "2021/06/18" and bigint(split(mapset,",")[0])=1
GROUP BY
uid,mapset
第二步:通过sum()分别对active0,active1,active7求和,得到基准日活跃人数,三日留存人数,七日留存人数。
SELECT
SUM(active0),
SUM(active1),
SUM(active7)
FROM
(
SELECT
uid,
bigint(split(mapset,",")[0]) as active0,
bigint(split(mapset,",")[1]) as active1,
bigint(split(mapset,",")[7]) as active7,
FROM
temp
WHERE
date = "2021/06/18" and bigint(split(mapset,",")[0])=1
GROUP BY
uid,mapset
) AS t1
30日留存人数大家可以参考这个例子,想想如何计算? -- 暂停1分钟
3.3 连续多日活跃人数优化方案 - 基于bitmap的思想
3.3.1 连续多日以上活跃用户
连续多日活跃人数又该如何计算呢?-- 暂停思考3分钟
好了,我们继续。答案很简单,还记得常用的模糊匹配like吗?
如果需要计算一月内有连续活跃3次以上的用户,我们只需 like "%1,1,1%"即可
方式如下:
SELECT
COUNT(DISTINCT uid) AS consecutive_3days_count
FROM
temp
WHERE
date = "2020/06/18"
AND mapset like "%1,1,1%"
如果是月内连续活跃7天以上的用户,也可依次类推得到。
3.3.2 连续多日活跃用户
引申出来的另一个问题是,如果我们需要计算的仅仅是这个月内只活跃了3天的用户呢?或只活跃了7天的用户呢?我们又该如何计算呢? -- 暂停思考3分钟
想到了吗?这里还需要回到我们刚才的例子 - 连续多日以上活跃用户数
假设我们需要计算的仅仅是只活跃了3天的用户,那么我们只需要[3,30]-[4,30](分别带别连续3日以上活跃用户和连续4日以上活跃用户,30代表区间最高值),通过区间的相减,就会得到30天内只活跃了3天的用户了。
方式如下:
SELECT
(
SELECT
COUNT(DISTINCT uid)
FROM
temp
WHERE
date = "2020/06/18"
AND mapset like "%1,1,1%"
-
SELECT
COUNT(DISTINCT uid)
FROM
temp
WHERE
date = "2020/06/18"
AND mapset like "%1,1,1,1%"
)
4. 其它说明
1. 在计算留存时,要和领导确认好计算口径,比如同一款公司有两款产品,用户两天分别使用不同的产品,这样的用户算用户留存吗?或者某用户第一天在广东省使用公司产品,第二题在海南省使用公司产品,这样的用户算用户留存吗?
2. 通常sql中的count(distinct xxx)容易引起数据倾斜,需要用group by转换方式实现去重计数。详细过程后续我会再写一篇博文进行阐述,大家记住这一点即可。
文末的最后,希望这篇文章对大家有所帮助,祝大家日日更上一层楼。