基于MySQL进行留存率的计算

前言

在对用户进行行为分析时，经常需要计算用户在软件当中的留存率的情况，如果用Python实现，是比较容易的，那如何用SQL进行计算呢，本文将完成这个任务，在编写SQL前，我们需要了解什么事留存率，这是如何计算的。百度百科对于留存率是这样定义的：

它的计算方法

如果以某一日期为基准，那么新增用户数就是当天有登录行为的所有用户数，在计算第N日留存数，即应该是该日期有登录行为的用户中在基准日期中也登录过的数量。

实现方法

本文提供两种实现方式，一种为单日期的，一种为多日期的，虽然方法较为类似，但多日期的需要考虑更多。

单日期实现

留存数本身的定义就用户的在当日的登陆数量，所以只要让每一个日期与基准日进行差值处理，过滤掉不在基准日的这些用户数就可以了，根据差值分组计数即为N日留存。

-- member-id为用户唯一标识
-- create_time为用户登陆时间，一个用户可以存在多次登陆记录
-- 以2019-09-01为基准，计算一天的1到30日留存数
SELECT retain_day, COUNT(member_id)
FROM (
-- 用户在当天可以有多次登陆行为，所以需要distinct
	SELECT DISTINCT member_id, DATE_FORMAT(create_time, '%Y-%m-%d') AS date1
		, DATEDIFF(create_time, '2019-09-01') AS retain_day
	FROM bss_memb_active
	WHERE DATE_FORMAT(create_time, '%Y-%m-%d') BETWEEN '2019-09-01' AND '2019-09-30'
-- 需要去除不在基准日的数据
		AND member_id IN (
			SELECT DISTINCT member_id
			FROM bss_memb_active
			WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2019-09-01'
		)
) aa
GROUP BY retain_day

结果

多日期的留存数

多日期的最终的结果呈现更像我们在其他分析软件上看到的那样，如友盟

多日期这里我实现的方式是用了表之间的笛卡尔积，MySQL的笛卡尔积可以直接用join不加条件的连接（不清楚笛卡尔积的连接方式可以去这里），将用户登陆的数据在所有需要展示的日期上重复一次，在去除非基准日的用户时，将外层的基准日期带入in的相关子查询中做一次过滤。

可以这样理解，笛卡尔积产生的每一个基准日的表计算该基准日的N日留存数。

-- sql多次使用了日期，所以这里将日期定义为一个变量
-- row_min_date为结果行中的最小日期
set @row_min_date='2019-09-01';
-- row_min_date为结果行中的最大日期
set @row_max_date='2019-09-10';
SELECT b.date1
-- 日期与基准日的差值即为N日留存数的N，对差值进行基准日的的分组计数，即是结果
	, COUNT(if(DATEDIFF(a.date, b.date1) = 0, a.member_id, NULL)) AS '当天活跃数'
	, COUNT(if(DATEDIFF(a.date, b.date1) = 1, a.member_id, NULL)) AS '第二天活跃数'
	, COUNT(if(DATEDIFF(a.date, b.date1) = 2, a.member_id, NULL)) AS '第三天活跃数'
	, COUNT(if(DATEDIFF(a.date, b.date1) = 3, a.member_id, NULL)) AS '第四天活跃数'
	, COUNT(if(DATEDIFF(a.date, b.date1) = 4, a.member_id, NULL)) AS '第五天活跃数'
	, COUNT(if(DATEDIFF(a.date, b.date1) = 5, a.member_id, NULL)) AS '第六天活跃数'
	, COUNT(if(DATEDIFF(a.date, b.date1) = 6, a.member_id, NULL)) AS '第七天活跃数'
FROM (
	SELECT DISTINCT DATE_FORMAT(create_time, '%Y-%m-%d') AS date, member_id
	FROM bss_memb_active
	WHERE DATE_FORMAT(create_time, '%Y-%m-%d') BETWEEN @row_min_date AND @row_max_date
) a
--笛卡尔积连接
	JOIN (
		SELECT DISTINCT DATE_FORMAT(create_time, '%Y-%m-%d') AS date1
		FROM bss_memb_active
		WHERE DATE_FORMAT(create_time, '%Y-%m-%d') BETWEEN @row_min_date AND @row_max_date
	) b
-- 排除非基准日的用户
WHERE member_id IN (
	SELECT member_id
	FROM bss_memb_active
	WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = b.date1
)
GROUP BY b.date1

结果

尾记

经历了一段时间对于MySQL的使用，发现MySQL的计算相对于SQL Server来说更加灵活，比如在select中对于字段的处理有更加丰富的函数，但是也相应会有不适应的地方，比如MySQL对于Group by的处理不严谨，不需要聚合函数就能使用，或许有更强大的功能等着我去挖掘，学习永无止境。