留存计算实操：步骤拆解与SQL实现

在本案例中，我们将通过以下两张数据表，完成次日留存分析。

1、用户注册表

表1-1 用户注册表(reg)

2、用户行为表

表1-2 用户行为表(act)

结合前面的表结构和分析逻辑，我们分四步进行实操。

第一步，查询所有新增注册用户

这一步的核心目的，是锁定我们要分析的“新增注册用户名单”，我们只需从用户注册表(reg)中，提取出“用户ID”和“注册时间”这两列数据即可。

SELECT
user_id,
register_time
FROM reg;

我们可以这样理解这段SQL：

检索
user_id列的数据,
register_time列的数据
从注册表(reg)中;

执行这段SQL后，会得到如下结果。

第二步：关联用户行为表，查看后续登录情况

用户注册表(reg)只有注册数据，没有登录数据，所以我们需要用LEFT JOIN(左外连接)，将用户行为表(act)与用户注册表(reg)关联起来，这样就能看到每个用户的注册时间和所有登录时间。

SELECT
r.user_id,
r.register_time,
a.login_time
FROM reg r  -- 将reg表简化命名为r
LEFT JOIN act a  -- 将act表简化命名为a
ON r.user_id = a.user_id;

我们可以这样理解这段SQL：

检索
reg表的user_id列的数据,
reg表的register_time列的数据,
act表的login_time列的数据
从注册表(r)中
左外连接行为表(a);
连接条件：用a.user_id匹配r.user_id(两表的用户ID相同);

执行这段SQL后，会得到如下结果：

此时，我们已经能清晰看到每个注册用户的所有登录记录，为后续筛选“次日登录”用户做好准备。

第三步：筛选“注册次日登录”的用户

要计算次日留存，我们需要在用户ID匹配的基础上，保留登录时间恰好等于注册时间第二天”的用户记录即可。

这里我们需要学习一个运营分析中常用的时间计算函数「DATE_ADD」。它就像一个时间计算器，输入一个日期，再告诉有它要往后推多久，它就可以计算出最终的日期。

举个例子，假设今天是2026年1月1日，你的信用卡账单有50天的免息期，你想知道最晚哪天还款不算逾期？

• 开始日期：2026年1月1日
• 间隔时间：50天
• 计算结果：2月20日

这个计算过程，用DATE_ADD就能一步完成，它的标准语法结构非常好记：

DATE_ADD(日期, INTERVAL 数值 时间单位)

简单拆解如下：

• 日期：起始时间（比如用户注册时间）
• INTERVAL：这是一个固定的关键词，表示“间隔”或“时长”
• 数值：要往后推多少时间
• 时间单位：时间的单位是什么（比如天、月、年等）

在运营数据分析中，DATE_ADD最常被用来计算以下四类时间单位：

YEAR年数

问：2026-01-15的2年后是几几年？

DATE_ADD('2026-01-15', INTERVAL 2 YEAR)

答：2028-03-15

MONTH月数

问：2026-01-15的2个月后是几月？

DATE_ADD('2026-01-15', INTERVAL 3 MONTH)

答：2026-03-15

DAY天数

问：2026-01-15的3天后是几号？

DATE_ADD('2026-01-15', INTERVAL 3 DAY)

答：2024-01-18

HOUR小时数

问：2026-01-15下午两点，再过5个小时是几点？

DATE_ADD('2026-01-15 14:00:00', INTERVAL 5 HOUR)

答：2026-01-15 19:00:00

回到案例，由于我们要筛选的“登录时间＝注册时间次日”的记录。因此只需在第二步SQL的基础上，增加一个连接条件（登录时间必须是注册时间加1天）。

SELECT
r.user_id,
r.register_time,
a.login_time
FROM reg r  
LEFT JOIN act a  
ON r.user_id = a.user_id
AND a.login_time = DATE_ADD(r.register_time, INTERVAL 1 DAY);

我们可以这样理解这段SQL：

检索
reg表的user_id列的数据,
reg表的register_time列的数据,
act表的login_time列的数据
从注册表(r)中
左外连接行为表(a);
连接条件:用a.user_id匹配r.user_id(两表的用户ID相同);
同时,只匹配注册后第二天的登录记录

执行这段SQL后，会得到如下结果。

可以看到，用户1002的login_time为NULL（空值），表示该用户注册次日没有登录记录；有具体日期的，则表示注册次日有登录记录。

第四步：统计人数，计算次日留存率

最后一步，我们要用到之前学过的统计函数「COUNT」和去重函数「DISTINCT」，先统计“当日注册用户数”和“次日登录同户数”，再用除法计算出次日留存率。

SELECT
COUNT(DISTINCT r.user_id) AS 当日注册用户数,
COUNT(DISTINCT a.user_id) AS 次日登录用户数,
COUNT(DISTINCT a.user_id) / COUNT(DISTINCT r.user_id) AS 次日留存率
FROM reg r  
LEFT JOIN act a 
ON r.user_id = a.user_id
AND a.login_time = DATE_ADD(r.register_time, INTERVAL 1 DAY);

我们可以这样理解这段SQL：

检索
统计去重后的当日注册用户数 并将统计结果命名为 当日注册用户数;
统计去重后的次日留存用户数 并将统计结果命名为 次日留存用户数;
次日留存用户数/当日注册用户数 并将计算结果命名为次日留存率
从注册表(r)中
左外连接行为表(a);
连接条件:用a.user_id匹配r.user_id(两表的用户ID相同);
同时,只匹配注册后第二天的登录记录

当我们执行这段SQL后，会得到以下数据表。

这样我们就完成了所有用户的次日留存计算。其实无论是次日留存、3 日留存、7 日留存，还是 30 日留存，其分析逻辑和SQL写法都完全通用，只需要调整DATE_ADD函数中的时间，就能快速计算出对应周期的留存率，实现用户留存率的分析。