MySQL基础学习
Source
Edit
History

MySQL中如何对留存进行分析？

Catalogue
  1. 1. 留存的基本概念
    1. 1.1. 关于界定/标准
  2. 2. MySQL - 场景模拟
    1. 2.1. 创建数据
    2. 2.2. 思路&答案

摘要
关于留存的基本概念认知，以及在MySQL中如何对留存进行分析的场景模拟…

留存的基本概念

关于界定/标准

  • 新增 = 新 + 增，理论上在处于一个流程内的不同的节点，都可以作为一次新增；例如：
    • 用户通过不同渠道衔接进入到渠道页
    • 下载
    • 安装启动
    • 激活行为：例如注册、购买商品等
  • 新的定义
    • 基于设备：用户第一次下载安装启动，记录设备；再次安装则不记录；另还有不同系统的设备的区分，即用户数 = 访问过服务的设备数
    • 基于帐号关联，即用户数 = 访问过服务的ID数
  • 留存的定义：某段时间内的新增用户，经过一段时间后，仍继续使用应用的，为留存用户；
    • 留存一般是离散的概念，不要求用户在N天内每一天都登录或者使用
  • 统计留存用户的时间粒度：
    • 自然日：先列出每个新用户第一次登录的日期，以及此日期之后仍登录的日期
      • 至于是第几天还是几天后，这个根据不同的业务有不同的定义
      • 次日留存：即第一次登录日期之后，第二天也登录的用户；即登录日期的差值为1天
      • 三日留存：即第一次登录日期之后，第三天也登录的用户；即登录日期的差值为3天
      • 七日留存：即第一次登录日期之后，第七天也登录的用户；即登录日期的差值为1天
    • 自然周
      • 需要注意的是，所谓周留存与七日留存并不是同概念
    • 自然月
  • 留存率
    • 概念：登录用户数 / 新增用户数 × 100%
      • 次日留存率 = (当天新增用户中，第二天还登录的用户数) / 第一天新增的总用户数
      • 3日留存率 = (当天新增用户中，往后的第3天还登录的用户数) / 第一天新增的总用户数
      • 7日留存率 = (当天新增用户中，往后的第7天还登录的用户数) / 第一天新增的总用户数
    • 最主要的是该留存率指标的界定标准，例如：怎么样的用户才算新增用户、用户在第三天登录还是第四天还有登录为3日留存等，这个是根据项目的不同来定义的

MySQL - 场景模拟


[题目]

用户行为信息表存有以下字段：用户id、应用名称、启用时长、启动次数以及登录时间

  • uid：用户的唯一标识
  • app_name：应用的名称
  • duration：某一天中使用了某应用多长时间(分钟)
  • times：某一天中启动了某应用多少次
  • login_date：登录的日期

现需要统计某日活跃用户在后续一周内的留存情况，也就是计算活跃用户数、次日留存用户数、3日留存用户数、7日留存用户数、次日留存率、3日留存率以及7日留存率


创建数据


1
2
3
4
5
6
7
8
9
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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
#创建表
CREATE TABLE userinfo(
    uid varchar(10) not null comment "用户ID",
    app_name varchar(20) comment "应用名称",
    duration int(10) comment "启用时长",
    times int(10) comment "启动次数",
    login_date date comment "登录时间"
);

#查看表
desc userinfo;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| uid        | varchar(10) | NO   |     | NULL    |       |
| app_name   | varchar(20) | YES  |     | NULL    |       |
| duration   | int(10)     | YES  |     | NULL    |       |
| times      | int(10)     | YES  |     | NULL    |       |
| login_date | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

#插入数据
insert into userinfo(uid,app_name,duration,times,login_date)
values
(01,'相机',1,2,'2018-05-01'),
(02,'微信',2,3,'2018-05-02'),
(03,'美团',4,2,'2018-05-03'),
(04,'微信',6,3,'2018-05-01'),
(05,'相机',3,1,'2018-05-03'),
(06,'相机',2,3,'2018-05-01'),
(07,'相机',2,2,'2018-05-02'),
(08,'微信',1,1,'2018-05-01'),
(09,'美团',3,2,'2018-05-02'),
(10,'相机',4,3,'2018-05-03'),
(11,'相机',5,4,'2018-05-02'),
(12,'美团',6,5,'2018-05-01'),
(13,'微信',7,1,'2018-05-02'),
(14,'相机',2,2,'2018-05-03'),
(15,'相机',1,3,'2018-05-01'),
(01,'美团',1,2,'2018-05-01'),
(02,'微信',1,3,'2018-05-04'),
(03,'相机',3,2,'2018-05-03'),
(04,'微信',4,3,'2018-05-01'),
(05,'相机',4,2,'2018-05-03'),
(06,'相机',2,2,'2018-05-04'),
(07,'美团',2,3,'2018-05-04'),
(08,'微信',1,2,'2018-05-04'),
(09,'相机',3,3,'2018-05-04'),
(10,'相机',4,3,'2018-05-03'),
(11,'相机',5,4,'2018-05-01'),
(12,'美团',6,5,'2018-05-02'),
(13,'微信',5,4,'2018-05-03'),
(14,'相机',2,2,'2018-05-02'),
(15,'相机',1,2,'2018-05-01'),
(01,'美团',1,2,'2018-05-05'),
(02,'微信',1,3,'2018-05-06'),
(03,'相机',3,2,'2018-05-04'),
(04,'微信',4,3,'2018-05-01'),
(05,'相机',4,2,'2018-05-04'),
(06,'相机',2,2,'2018-05-04'),
(07,'美团',2,3,'2018-05-05'),
(08,'微信',1,2,'2018-05-06'),
(09,'相机',3,3,'2018-05-06'),
(10,'相机',4,3,'2018-05-05'),
(11,'相机',5,4,'2018-05-04'),
(12,'美团',6,5,'2018-05-02'),
(13,'微信',5,4,'2018-05-05'),
(14,'相机',2,2,'2018-05-06'),
(15,'相机',1,2,'2018-05-06'),
(01,'美团',1,2,'2018-05-08'),
(02,'微信',1,3,'2018-05-06'),
(03,'相机',3,2,'2018-05-04'),
(04,'微信',4,3,'2018-05-10'),
(05,'相机',4,2,'2018-05-08'),
(06,'相机',2,2,'2018-05-07'),
(07,'美团',2,3,'2018-05-09'),
(08,'微信',1,2,'2018-05-09'),
(09,'相机',3,3,'2018-05-09'),
(10,'相机',4,3,'2018-05-05'),
(11,'相机',5,4,'2018-05-05'),
(12,'美团',6,5,'2018-05-10'),
(13,'微信',5,4,'2018-05-09'),
(14,'相机',2,2,'2018-05-06'),
(15,'相机',1,2,'2018-05-06'),
(01,'美团',1,2,'2018-05-08'),
(02,'微信',1,3,'2018-05-06'),
(03,'相机',3,2,'2018-05-04'),
(04,'微信',4,3,'2018-05-10'),
(05,'相机',4,2,'2018-05-10'),
(06,'相机',2,2,'2018-05-07'),
(07,'美团',2,3,'2018-05-09'),
(08,'微信',1,2,'2018-05-08'),
(09,'相机',3,3,'2018-05-09'),
(10,'相机',4,3,'2018-05-10'),
(11,'相机',5,4,'2018-05-05'),
(12,'美团',6,5,'2018-05-10'),
(13,'微信',5,4,'2018-05-08'),
(14,'相机',2,2,'2018-05-07'),
(15,'相机',1,2,'2018-05-07'),
(01,'美团',1,2,'2018-05-08'),
(02,'微信',1,3,'2018-05-06'),
(03,'相机',3,2,'2018-05-10'),
(04,'微信',4,3,'2018-05-10'),
(05,'相机',4,2,'2018-05-10'),
(06,'相机',2,2,'2018-05-07'),
(07,'美团',2,3,'2018-05-09'),
(08,'微信',1,2,'2018-05-08'),
(09,'相机',3,3,'2018-05-10'),
(10,'相机',4,3,'2018-05-10'),
(11,'相机',5,4,'2018-05-05'),
(12,'美团',6,5,'2018-05-10'),
(13,'微信',5,4,'2018-05-08'),
(14,'相机',2,2,'2018-05-07'),
(15,'相机',1,2,'2018-05-07');

#查看数据
select * from userinfo limit 10;
+-----+----------+----------+-------+------------+
| uid | app_name | duration | times | login_date |
+-----+----------+----------+-------+------------+
| 1   | 相机     |        1 |     2 | 2018-05-01 |
| 2   | 微信     |        2 |     3 | 2018-05-02 |
| 3   | 美团     |        4 |     2 | 2018-05-03 |
| 4   | 微信     |        6 |     3 | 2018-05-01 |
| 5   | 相机     |        3 |     1 | 2018-05-03 |
| 6   | 相机     |        2 |     3 | 2018-05-01 |
| 7   | 相机     |        2 |     2 | 2018-05-02 |
| 8   | 微信     |        1 |     1 | 2018-05-01 |
| 9   | 美团     |        3 |     2 | 2018-05-02 |
| 10  | 相机     |        4 |     3 | 2018-05-03 |
+-----+----------+----------+-------+------------+

思路&答案


[思路]

  • 读题：现需要统计手机应用中相机的活跃情况，即某日活跃用户在后续一周内的留存情况，也就是计算活跃用户数、次日留存用户数、3日留存用户数、7日留存用户数、次日留存率、3日留存率以及7日留存率
  • 解题：
    • 活跃用户数定义：某日有登录行为记为活跃，首次登录日期记为第一天登录日期
    • 次日留存用户数定义：在第一天登录日期，第二天有登录行为的用户
    • 3日留存用户数定义：在第一天登录日期，第3天有登录行为的用户
    • 7日留存用户数定义：在第一天登录日期，第7天有登录行为的用户
    • 次日留存率定义：次日留存用户数 / 该用户第一天登录日期的活跃用户数
    • 3日留存率定义：3日留存用户数 / 该用户第一天登录日期的活跃用户数
    • 7日留存率定义：7日留存用户数 / 该用户第一天登录日期的活跃用户数
1
2
3
4
5
6
7
8
9
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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
select login_date,count(distinct uid) as "活跃用户数" 
from userinfo 
where app_name = "相机"
group by login_date;

+------------+-----------------+
| login_date | 活跃用户数      |
+------------+-----------------+
| 2018-05-01 |               4 |
| 2018-05-02 |               3 |
| 2018-05-03 |               4 |
| 2018-05-04 |               5 |
| 2018-05-05 |               2 |
| 2018-05-06 |               3 |
| 2018-05-07 |               3 |
| 2018-05-08 |               1 |
| 2018-05-09 |               1 |
| 2018-05-10 |               4 |
+------------+-----------------+

select *,timestampdiff(day,a_date,b_date) as "时间间隔"
from
(select a.uid,a.login_date as a_date,b.login_date as b_date
from userinfo as a join userinfo as b
on a.uid = b.uid
where a.app_name = "相机") as c;

+-----+------------+------------+--------------+
| uid | a_date     | b_date     | 时间间隔     |
+-----+------------+------------+--------------+
| 1   | 2018-05-01 | 2018-05-01 |            0 |
| 3   | 2018-05-03 | 2018-05-03 |            0 |
| 3   | 2018-05-04 | 2018-05-03 |           -1 |
| 3   | 2018-05-04 | 2018-05-03 |           -1 |
| 3   | 2018-05-04 | 2018-05-03 |           -1 |
| 3   | 2018-05-10 | 2018-05-03 |           -7 |
| 5   | 2018-05-03 | 2018-05-03 |            0 |
| 5   | 2018-05-03 | 2018-05-03 |            0 |
| 5   | 2018-05-04 | 2018-05-03 |           -1 |
| 5   | 2018-05-08 | 2018-05-03 |           -5 |
| 5   | 2018-05-10 | 2018-05-03 |           -7 |
| 5   | 2018-05-10 | 2018-05-03 |           -7 |
| 6   | 2018-05-01 | 2018-05-01 |            0 |
| 6   | 2018-05-04 | 2018-05-01 |           -3 |
| 6   | 2018-05-04 | 2018-05-01 |           -3 |


#最终答案
select a_date,count(distinct d.uid) as "活跃用户数",
count(distinct case when 时间间隔 = 1 then d.uid else null end) as "次日留存用户数",
count(distinct case when 时间间隔 = 3 then d.uid else null end) as "3日留存用户数",
count(distinct case when 时间间隔 = 7 then d.uid else null end) as "7日留存用户数",
count(distinct case when 时间间隔 = 1 then d.uid else null end) / count(distinct d.uid) as "次日留存率",
count(distinct case when 时间间隔 = 3 then d.uid else null end) / count(distinct d.uid) as "3日留存率",
count(distinct case when 时间间隔 = 7 then d.uid else null end) / count(distinct d.uid) as "7日留存率"
from 
(select *,timestampdiff(day,a_date,b_date) as "时间间隔"
from
(select a.uid,a.login_date as a_date,b.login_date as b_date
from userinfo as a join userinfo as b
on a.uid = b.uid
where a.app_name = "相机") as c) as d
group by a_date;