SQL 查询用户留存率（根据两种不同定义计算）

如何通过简单的两个字段（用户及日期）数据求出用户留存率？

本笔记会以两种不同定义的用户留存率进行计算，一个是针对每日老用户留存率，一个是针对每日新增用户的留存率。

方法一（老用户留存率）：

需求：根据temp_user_act表中的user_id （用户id）,dates （用户活跃日期）计算出，每日的用户数以及次日留存率，二日留存，三日留存率，7日留存率。

第一步我们要明确知道，这里说的用户活跃日期及用户留存率定义是什么，其实根据不同的业务需求背景，可以有不一样的定义，这里首先说的是基于用户活跃日期来计算每天用户的留存率。（肯定有同学问为啥不是算新增用户呢？下面方法二会介绍到）

考虑到手上数据表是截取整体数据的一部分，即里面的用户id未必一定都带有首次注册登录的时间，所以无法判里面的用户id是否新增用户。

所以这里说的是活跃用户的留存率，即理解为：

活跃用户留存率：指当天活跃用户数除以数天后（当天）该批用户数在此天仍活跃的用户数；

例：（2019-11-18有100名活跃用户，此100名用户中有60名在 2019-11-25仍活跃，即7天活跃用户留存率为60/100=60%）；

明晰定义后：

第二步，将上述的temp_user_act表进行自连接，并且用左外连接，定义左表为a表，右表为b表，并设置条件为 b表dates 大于等于a表dates。

此举为求一一列出每用户每天及当天后面有活跃的日期。

左连接效果：左边保留所有数据字段，由于有不等式关系，所以会出现以下情况：

SELECT

a.user_id ,  a.dates ,  b.user_id,  b.dates

FROM

temp_user_act a

LEFT JOIN temp_user_act b ON a.user_id = b.user_id WHERE  b.dates >= a.dates ORDER BY a.user_id, a.dates ;

image

可以从以上查询结果，看到同一个用户id（左表）活跃日期及（右表）相对当天后的活跃日期，对于后面求次日留存、七日留存有很大用处。

第三步，求以左表日期为首日的首日用户数，第二日用户数，第三日用户数，第四日用户数，第八日用户数；

此处主要用的是COUNT(DISTINCT IF(DATEDIFF(dates,日期)=1 ,用户id, NULL )) 。

DATEDIFF(b.dates,a.dates)=1为求出左右表日期差值为1（即次日这个条件），然后判断其是否为真，为真输出其对应的用户id，并去重计算，即为当天用户在次天仍有活跃，并计算符合此情况的用户ID数量。即可求出当天后的一天，仍有多少用户留存。

按照此逻辑，如此类推其他天数。

SELECT

a.dates  AS 日期 ,

COUNT(DISTINCT  a.user_id  )  AS 首日 ,

COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=1 ,a.user_id, NULL )) AS 第二日用户数 ,

COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=2 ,a.user_id, NULL )) AS 第三日用户数 ,

COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=3 ,a.user_id, NULL )) AS 第四日用户数 ,

COUNT(DISTINCT IF(DATEDIFF(b.dates,a.dates)=7 ,a.user_id, NULL )) AS 第八日用户数

FROM 【步骤二的表，建议弄成视图直接引用】 GROUP BY  a.dates

image

第四步，求留存率也就十分简单了

SELECT

a.dates  AS 日期 ,

首日 ,

CONCAT(TRUNCATE ( ( 第二日用户数 / 首日 ) * 100,2) ,"%") AS 次日留存率 ,

CONCAT(TRUNCATE ( ( 第三日用户数 / 首日 ) * 100,2) ,"%") AS 二日留存 ,

CONCAT(TRUNCATE ( ( 第四日用户数 / 首日 ) * 100,2) ,"%") AS 三日留存率 ,

CONCAT(TRUNCATE ( ( 第八日用户数 / 首日 ) * 100,2) ,"%") AS 七日留存率

FROM 【第三步所得表的视图】

查得结果如下：

image

注意：
1、以上方法均为以每步骤查询表后创建视图，再在下一步引用上一步视图，这办法看起来会简洁清晰得多，而方法二将会用嵌套子查询办法，不使用视图引用，将整个代码敲出。
2、左表日期需大于等于右表日期，如果没了等号，会发生什么呢？

image

导致表里面的最后一个日期数据丢失，前一天的次日用户数与当天一样。

即12月18日数据没了，12月17日与其次日数据一样。

因为在只有左表日期>右表日期，情况下最后一天（12月18日）的数据没有比它更大的日期，只能在条件下筛去，而在前面日期，用户只登录一天（一天就没的比大小了）的数据也会被筛去，导致数据有较大误差。

方法二（新用户留存率）：

另外根据百度百科对 “用户留存率”的定义，如下

image

这种定义应该较为常用，对于此种定义，我们首先需要找出当天新增的用户数，此时我们就不考虑数据来源只是被截取的一部分，需假设全部数据（包括首次注册登录时间）；

此处其实与方法一整体思路几乎一样，都是用 datediff 函数及count 其符合日期差条件的用户id数。

不同地方在于：
1、首先筛选新增的用户
办法：用min函数求每用户的最小日期（即首次登录日期），再左连接回原表，找出新增用户对应的活跃日期，并计算日期差；
2、此处不用方法一的引用视图办法，而直接将整个代码从头到尾敲出；

【第一步】找出新增用户id

SELECT

user_id , min(dates) as first_day 

FROM

temp_user_act 
GROUP BY user_id

查询结果如下：

image.png

【第二步】第一步得出的表与原表做左连接找出每用户活跃日期，且筛选日期相减为1的数据，并对其count即可得出次日留存用户数。

SELECT

first_day AS 日期 ,
COUNT(DISTINCT a.user_id) AS 新增用户数 ,
COUNT(DISTINCT b.user_id) AS 次日留存用户数
FROM

(
SELECT

user_id , min(dates) as first_day 

FROM

temp_user_act 
GROUP BY user_id
) a 
LEFT JOIN
temp_user_act b  ON a.user_id = b.user_id  and  DATEDIFF(b.dates,a.first_day ) = 1 

GROUP BY a.first_day

查询结果如下：

image.png

【第三步】如此类推，将其他天数留存率加上，并计算比率；

SELECT

first_day AS 日期 ,
COUNT(DISTINCT a.user_id) AS 新增用户数 ,
COUNT(DISTINCT b.user_id) AS 次日留存用户数 ,
COUNT(DISTINCT c.user_id) AS 二日留存用户数,
COUNT(DISTINCT d.user_id) AS 三日留存用户数,
COUNT(DISTINCT e.user_id) AS 七日留存用户数 
FROM

(
SELECT

user_id , min(dates) as first_day 

FROM

temp_user_act 
GROUP BY user_id
) a 
LEFT JOIN temp_user_act b  ON a.user_id = b.user_id  and  DATEDIFF(b.dates,a.first_day ) = 1 
LEFT JOIN temp_user_act c  ON a.user_id = c.user_id  and  DATEDIFF(c.dates,a.first_day ) = 2 
LEFT JOIN temp_user_act d  ON a.user_id = d.user_id  and  DATEDIFF(d.dates,a.first_day ) = 3 
LEFT JOIN temp_user_act e  ON a.user_id = e.user_id  and  DATEDIFF(e.dates,a.first_day ) = 7 

GROUP BY a.first_day

查询结果如下：

image.png

SELECT

日期 ,
新增用户数 ,
CONCAT(TRUNCATE ( ( 次日留存用户数 / 新增用户数 ) * 100,2) ,"%") AS 次日留存率 ,
CONCAT(TRUNCATE ( ( 第二日留存用户数 / 新增用户数 ) * 100,2) ,"%") AS 第二日留存 ,
CONCAT(TRUNCATE ( ( 第三日留存用户数 / 新增用户数 ) * 100,2) ,"%") AS 第三日留存率 ,
CONCAT(TRUNCATE ( ( 第七日留存用户数 / 新增用户数 ) * 100,2) ,"%") AS 第七日留存率

FROM
(
SELECT

first_day AS 日期 ,
COUNT(DISTINCT a.user_id) AS 新增用户数 ,
COUNT(DISTINCT b.user_id) AS 次日留存用户数 ,
COUNT(DISTINCT c.user_id) AS 第二日留存用户数,
COUNT(DISTINCT d.user_id) AS 第三日留存用户数,
COUNT(DISTINCT e.user_id) AS 第七日留存用户数 
FROM

(
SELECT

user_id , min(dates) as first_day 

FROM

temp_user_act 
GROUP BY user_id
) a 
LEFT JOIN temp_user_act b  ON a.user_id = b.user_id  and  DATEDIFF(b.dates,a.first_day ) = 1 

LEFT JOIN temp_user_act c  ON a.user_id = c.user_id  and  DATEDIFF(c.dates,a.first_day ) = 2 
LEFT JOIN temp_user_act d  ON a.user_id = d.user_id  and  DATEDIFF(d.dates,a.first_day ) = 3 
LEFT JOIN temp_user_act e  ON a.user_id = e.user_id  and  DATEDIFF(e.dates,a.first_day ) = 7 

GROUP BY a.first_day 
) P ;

查询结果如下：

image.png