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

SELECT AVG(CASE WHEN t.device_id THEN 1 ELSE 0 END) avg_ret
FROM 
(
SELECT DISTINCT device_id, date
FROM question_practice_detail
) q
LEFT JOIN
(
SELECT DISTINCT device_id, DATE_SUB(date, INTERVAL 1 day) date
FROM question_practice_detail
) t
ON 
q.device_id = t.device_id AND q.date = t.date;

自己做没做出来，参考的是恶作剧之神阿蒙的答案。

自己原来的想法是：

1.计算次日留存，先找到公式，次日留存率=次日活跃用户数/当日活跃用户数。

2.那要先找到用户最先活跃的日期，以及用户是否在第二天活跃。这里我想的是创建一个临时表，找到时间顺序的同一个id的两条记录，这样缩小区间范围为两个日期，之后再判断是否是相邻。

3.然后就发现问题了，自己时间函数不会，同时如果只找两条记录，那么中间隔了一天继续连续两天访问的话，会误判为没有留存。

参考大佬的做法，结合我的现有知识整理一下思路：

1.表也是必须联结

2.想找到第二天的记录，除了直接计算日期差，还可以将日期拉到同一水平线上，虽然也是用日期函数。DATE_SUB(date, INTERVAL 1 day)可以用来解决日期处理的问题。

3.使用了 DISTINCT 关键字和两个字段 device_id 和 date。这意味着这个查询将从 question_practice_detail 表中选择唯一的 device_id 和 date 对的组合。这样就避免了出现同一个用户在一天内连续访问的情况。

4.计算留存率除了直接除，还可以使用均值函数，当存在一个用户在相邻两天登录的情况时，就得1，否则为0，然后分母就是所有情况的次数，分子就是两天内连续登录的次数。

5.采用左外联结，是将没有经过处理日期的表和处理后的表联系起来，这样如果在右表查询到和左表id和日期相同的记录，那么就是命中一条记录。