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

思路分析

对于这道题，由于题目没有给公式，题意也比较模糊，我们需要先猜出计算式，突破点是题目的示例数据，以及示例答案0.3。

题目是这样描述的:

现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率

我们可以用用频率（次数）代替概率，我们先猜测这个平均概率的计算式为

$avg\mathrm{\_}ret={\displaystyle \frac{\mathrm{第}\mathrm{二}\mathrm{天}\mathrm{仍}\mathrm{然}\mathrm{上}\mathrm{线}\mathrm{天}\mathrm{数}\mathrm{的}\mathrm{次}\mathrm{数}}{\mathrm{所}\mathrm{有}\mathrm{人}\mathrm{总}\mathrm{共}\mathrm{出}\mathrm{现}\mathrm{的}\mathrm{天}\mathrm{数}\mathrm{的}\mathrm{和}}}avg\backslash \_ret=\backslash dfrac\{第二天仍然上线天数的次数\}\{所有人总共出现的天数的和\}$

用形式化的语言表示即为

$avg\mathrm{\_}ret={\displaystyle \frac{{\displaystyle \sum _{i=0}^n\sum _{j=0}^n{A}_i\mathrm{用}\mathrm{户}\mathrm{在}\mathrm{第}{B}_j\text{天出现过}\cap A_i\text{用户在第}{B}_j+1\text{天出现过}}}{{\displaystyle \sum _{i=0}^n{A}_i\text{用户出现过的总天数}}}}avg\backslash \_ret=\backslash dfrac\{\backslash displaystyle\backslash sum\_\{i=0\}^n\backslash sum\_\{j=0\}^n\; A\_i\{用户在第\}B\_j\backslash text\{天出现过\}\backslash cap\; A\_i\backslash text\{用户在第\}B\_j+1\backslash text\{天出现过\}\}\{\backslash displaystyle\backslash sum\_\{i=0\}^n\; A\_i\backslash text\{用户出现过的总天数\}\}$

根据这个思路，我们如何手动做这道题呢？

观察算式，很容易发现结果和日期和当时出现过的人有关，很容易想到用二维表辅助分析。我们可以先把每一个人在哪一天出现过标记出来。为了方便我们作表，我们可以对原始表按照日期排序。

--看一下整张表的结构
SELECT * FROM question_practice_detail
ORDER BY date,device_id;

排序后的表

最终我们画出下面的表：

中间分析表

注：红色单元格代表当天出现，第二天仍然上线；蓝色代表当天出现，第二天没有上线

注：虽然5.9和6.15在中间分析表中黏在一起，但他们不是相邻的两天

红色出现过的那一天代表该用户第二天留了下来，数一下我们发现第二天仍然上线的次数为3次（红色块的数量），所有人总共出现的天数的和为10次（颜色块的数量），结果即为0.3，那么我们的想法大概是八九不离十了。

将思路转为sql代码

接下来我们可以将我们的思路逐步转为sql代码，再整合一下就能得到答案了。

求分母：所有人总共出现的天数的和

很明显，求用户出现的总天数，也就是分母部分会比较简单，我们先从这里下手。

第一步，每日客户去重。如果当天一个客户访问了多次网站，那么只计算为一次有效访问次数。比如5.9那天3214用户的记录出现了两次，但是应该计数为1。

我们先按日期分组，然后求某一天去重之后的人数(COUNT DISTINCT)，再把每一天去重后的人数加起来(SUM)。即：

-- 求所有人总共出现的天数的和
SELECT SUM(each_date) AS visitors_num FROM
(
    SELECT COUNT(DISTINCT device_id) AS each_date FROM question_practice_detail
    GROUP BY date
)a

输出：所有人总共出现的天数的和

求分子：第二天仍然上线天数的次数

这里的难点在于，如何表示出一个用户相邻相邻的两天上线这个特性，并数出来一个用户相邻相邻的两天上线个数。

首先考虑如何得到一张一个用户上线过的日期的记录表。通过对日期和时间聚合达到这一目的。

-- 求用户上线过的日期的记录表
SELECT 
    date AS day_one, 
    device_id 
FROM question_practice_detail
GROUP BY date, device_id

输出：用户上线过的日期的记录表

如果我们手工做的话，我们的思路相当于遍历 用户上线过的日期的记录表的每一个条目，然后看这个条目日期的第二条，该用户有没有上线。比如我们以8.13日的2315那条为例子，我们又从第一条开始匹配，匹配最后一条，发现8.14日的2315也上线了，那么就把这条选出来。

想象的两份表

思维稍微转换一下，我们可以通过用户上线过的日期的记录表创造一张假想第二天上线表,将用户上线过的日期的记录表的时间都推迟一天，我们遍历 假想第二天上线表的每一个条目，如果用户上线过的日期的记录表中存在一条一样的条目，那么就计数。

-- 创造假想的表
SELECT 
    DATE_ADD(date, INTERVAL 1 DAY) AS day_two, 
    device_id 
FROM question_practice_detail
GROUP BY date, device_id;

通过假想的表来求

我们的遍历过程相当于求一次两张表的笛卡尔积，然后可以用WHERE筛选出我们想要的条目计数即可。

-- 求分子
SELECT retention_num
FROM
    (
        SELECT COUNT(*) AS retention_num FROM
        (
            SELECT 
                date AS day_one, 
                device_id 
            FROM question_practice_detail
            GROUP BY date, device_id
        ) day_one_table,
        (
            SELECT 
                DATE_ADD(date, INTERVAL 1 DAY) AS day_two, 
                device_id 
            FROM question_practice_detail
            GROUP BY date, device_id
        ) day_two_table
        WHERE day_one_table.day_one=day_two_table.day_two 
          AND day_one_table.device_id=day_two_table.device_id
    ) retention

完整代码

接下来，分子除分母即可。

SELECT retention_num/visitors.visitors_num AS avg_ret
FROM
    (
        SELECT SUM(each_date) AS visitors_num FROM
        (
            SELECT COUNT(DISTINCT device_id) AS each_date FROM question_practice_detail
            GROUP BY date
        )a
    ) visitors,
    (
        SELECT COUNT(*) AS retention_num FROM
        (
            SELECT 
                date AS day_one, 
                device_id 
            FROM question_practice_detail
            GROUP BY date, device_id
        ) day_one_table,
        (
            SELECT 
                DATE_ADD(date, INTERVAL 1 DAY) AS day_two, 
                device_id 
            FROM question_practice_detail
            GROUP BY date, device_id
        ) day_two_table
        WHERE day_one_table.day_one=day_two_table.day_two 
            AND day_one_table.device_id=day_two_table.device_id
    ) retention