题解 | 计算用户的平均次日留存率

计算过程

首先明确计算公式：连续两天来的用户量 / 总的用户访问量

连续两天来的用户量：是不去重的，只看行为，如果有同一个人连续三天都来了，则算他连续来了两次，所以根据表中数据，总共的连续刷题用户量是3，2135两次，3214一次；

总的用户访问量：就是总的用户访问次数，2135连续三天都来，就计为3次，也是不用去重的；

接着看表中数据，表中有部分记录是重复的，比如2135的连续三次访问记录就都重复了，这个是需要去重的，不管这个人在一天里登录多少次，只要他在这一天里登录过，就算一次就可以了，用户在同一天内多次登录的记录不用重复计入公式中去计算。通过如下代码就可以看到去重后的表：

SELECT DISTINCT device_id, date FROM question_practice_detail；

从该表中可以看到不重复的记录一共是十条，我们要做的就是找出这十条中连续两天登录的用户的占比，可以看到2135连续登录两次，3214连续登录一次，所以结果是(2+1) /10 = 0.3

代码实现

理解了计算方式以后就要用代码去实现这个过程了，实现的过程中需要解决几个问题，第一个就是表中记录的去重，通过我们上面的代码已经实现，之后就使用去重后的表作为派生表来进行查询，第二个就是间隔一天的判断，第三个是公式的分子分母怎么计算出来：

间隔一天的判断：使用常规的方法肯定只能对一条记录操作，不可能跨行去比较数据，所以要把不同的记录放到一行了才能进行判定，所以这里必须使用自身连接，连接条件是device_id相等，这样就把同一个device_id的记录依次两两连接到一起形成新的连接表，两张表分别命名为p1、p2，这样同一条记录里就有分别来自p1、p2两个date，可以通过日期处理函数对p1.date和p2.date进行比较，使用 p2.date = DATE_ADD(p1.date, INTERVAL 1DAY) 或者 DATEDIFF(p2.date, p1.date) = 1 都行；

分子分母的计算：这是本题的关键，它主要取决于我们怎么去进行自身连接，使用内连接或者外连接都可以实现，假如连接条件写为 p1.device_id = p2.device_id AND p2.date = DATE_ADD(p1.date, INTERVAL 1DAY) ，即限定连接那些连续两天都来的用户，通过如下代码可以看到这个连接的结果是什么样的：

SELECT *
FROM
	(SELECT DISTINCT device_id, date FROM question_practice_detail) AS p1
JOIN
	(SELECT DISTINCT device_id, date FROM question_practice_detail) AS p2
ON
	p1.device_id = p2.device_id AND p2.date = DATE_ADD(p1.date, INTERVAL 1 DAY);

可以看到结果就是符合我们要求的分子的三条记录，可以通过聚合函数计数为3，但是还需要计算分母，把其他不符合连续登录的用户登录记录也算进来，因此需要使用到外连接，比如左外连接，就可以把左表中剩余的七条未能匹配连接条件的记录也放进结果集中，可以看到左外连接的结果是十条记录，未能匹配连接条件的记录p2的属性值为NULL，能匹配连接条件的记录p1和p2的属性都有值，通过这个区别就可以分别进行条件计数，求出最终的结果。这个计数的方式就有很多种了，分母的计数就用 COUNT(*) 计数总行数即可，分子的计数用 COUNT(p2.device_id) 即可，它只会计数有值的记录，自动忽略NULL，当然还有其他写法，如 SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END) 通过求和函数计数，也是一样的意思，写法不同而已，所以最后的代码就是：

SELECT
    ROUND(COUNT(p2.device_id) / COUNT(*), 4) AS avg_ret
FROM
	(SELECT DISTINCT device_id, date FROM question_practice_detail) AS p1
LEFT JOIN
	(SELECT DISTINCT device_id, date FROM question_practice_detail) AS p2
ON
	p1.device_id = p2.device_id AND p2.date = DATE_ADD(p1.date, INTERVAL 1 DAY);

如果对外连接不熟悉，使用内连接也可以实现，将连接条件置为 p1.device_id = p2.device_id，把每个用户自己的记录全部连接起来，接着在计数的时候使用条件计数分别求出分子分母即可，此时连接条件里不能再加入 p2.date = DATE_ADD(p1.date, INTERVAL 1 DAY)，这样的话连接以后只有三条记录，我们就没法获取分母了。通过 device_id 内连接，此时连接的结果里有所有用户的记录，其中还有一些相互重复的记录，此时对其中一个表进行去重计数即可，如 COUNT(DISTINCT p1.device_id, p1.date)，所有 p1.device_id, p1.date 都相同的记录计数一次即可，这个结果就是10，对于分子采用 CASE WHEN 条件计数，只计数 p2.date 比 p1.date 多一天的记录，比如 COUNT(CASE WHEN DATEDIFF(p2.date, p1.date) = 1 THEN 1 END)，这个结果就是3，所以最后的代码就是：

SELECT
	ROUND(
	  COUNT(CASE WHEN DATEDIFF(p2.date, p1.date) = 1 THEN 1 END) 
	  / COUNT(DISTINCT p1.device_id, p1.date), 4) AS avg_ret
FROM
	(SELECT DISTINCT device_id, date FROM question_practice_detail) AS p1
JOIN
	(SELECT DISTINCT device_id, date FROM question_practice_detail) AS p2
ON
	p1.device_id = p2.device_id;