SQL留存分析（次日留存，多日留存）和连续活跃人数的优化方案

1. 指标口径说明

次日留存：第一天使用产品的用户，在第二天依旧使用的用户数

多日留存：第一天使用产品的用户，在多日后依旧使用的用户数

连续活跃人数：连续多少天使用产品的人数

2. 原始方案 - 次日留存，多日留存，连续活跃用户

2.1 表字段说明：

表共有四个字段：uid，action，product，date

uid：用户id，用户的唯一标识

action：用户在某产品中的行为

product：用户使用的某产品id

date：用户使用某产品的日期

2.2 留存计算原始方案 - 左连接

思路：

1. 在uid，date的粒度去重，使用group by uid,date方法
2. 根据id字段和date字段进行左连接，右表date需大于左表date
3. 根据左表id, date分组聚合，计算多日留存人数。如：右表date-左表date=1即该用户在以左表date为基准的基础上，在第二天依旧有活跃，也就是我们所说的次日留存
4. 分别得到需要的多日留存即可

本次讲解将基于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天的用户数，思路：

1. 筛选出近一月内的用户，并根据date - ROW_NUMBER() OVER(...)判断活跃日期是否连续
2. 根据uid和第一步中生成的tag分组，统计每个用户的每一段连续活跃日期的天数
3. 通过HAVING COUNT(1) = 3，连续活跃日期为3的用户
4. 根据该结果进行统计总用户数

第一步：

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日为基准日，计算多日留存，有如下思路：

1. 与之前一样，在uid，date的粒度去重，使用group by uid,date方法
2. 以左表6月18日为基准日，与右表6月19日，6月21日为需要计算的次日留存日和三日留存日左连接
3. 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转换方式实现去重计数。详细过程后续我会再写一篇博文进行阐述，大家记住这一点即可。

文末的最后，希望这篇文章对大家有所帮助，祝大家日日更上一层楼。