SQL 计算次日留存率：从概念到实践

---

在数据分析中，留存率是衡量用户粘性的核心指标之一，而次日留存率更是判断产品初期吸引力的 “试金石”。本文将从基础概念出发，带你掌握用 SQL 计算次日留存率的完整思路，并通过实例代码和数据表格帮助

一、什么是次日留存率？

次日留存率的定义很简单：
当天登录的用户中，第二天依然登录的用户占比。
公式可表示为：
次日留存率 = （当天登录且次日也登录的用户数） / （当天登录的用户总数） * 100%

举个例子：1 月 1 日有 100 个用户登录，其中 30 个用户在 1 月 2 日再次登录，则 1 月 1 日的次日留存率为 30%。

二、数据准备：需要哪些字段？

计算次日留存率的核心数据来自用户的登录日志，至少需要两个字段：

user_id：用户唯一标识（区分不同用户）
login_time：登录时间（精确到日期即可，用于判断 “当天” 和 “次日”）
我们用一个示例表user_login来演示，结构如下：

user_id
110
101
102
103
101
102

    
三、计算思路：3 步拆解

计算次日留存率的核心是 “找到当天登录用户，并判断他们是否次日登录”，可拆解为 3 步：

1、去重处理：

同一用户同一天可能多次登录，需先得到 “用户 - 日期” 的唯一记录（避免重复计算）；
2、关联次日登录记录：

将用户当天的登录记录与次日的登录记录关联，标记是否留存；
3、计算留存率：

按日期统计留存用户数和总登录用户数，计算比例。

四、SQL 实现：分步代码

步骤 1：去重得到 “用户 - 日期” 唯一表

首先用DATE(login_time)提取日期，再通过DISTINCT去重，得到每个用户每天的登录记录：

-- 生成用户每日登录的唯一记录
WITH user_login_daily AS (
  SELECT 
    user_id,
    DATE(login_time) AS login_date  -- 提取登录日期（忽略时分秒）
  FROM user_login
  GROUP BY user_id, DATE(login_time)  -- 按用户和日期去重
)
SELECT * FROM user_login_daily;

以上述user_login表为例，输出结果为：

user_id	login_data
101	2023-10-01
102	2023-10-01
103	2023-10-01
101	2023-10-02
102	2023-10-03
104	2023-10-02

步骤 2：关联次日登录记录

通过自连接（将表与自身连接），判断用户是否在次日登录。核心逻辑是：用当天的login_date关联次日的login_date + 1。

WITH user_login_daily AS (
    -- 复用步骤1的去重结果
    SELECT 
        user_id,
        DATE(login_time) AS login_date
    FROM user_login
    GROUP BY user_id, DATE(login_time)
)
-- 关联次日登录记录
SELECT 
    a.login_date AS current_date,  -- 当天日期
    a.user_id,
    CASE 
        WHEN b.user_id IS NOT NULL THEN 1  -- 次日登录：标记为1
        ELSE 0  -- 次日未登录：标记为0
    END AS is_retention  -- 是否留存
FROM user_login_daily a
LEFT JOIN user_login_daily b 
    ON a.user_id = b.user_id  -- 同一用户
    AND b.login_date = a.login_date + INTERVAL 1 DAY;  -- 次日日期

输出结果（以 2023-10-01 为例）：
 

current_date	user_id	is_retention
2023-10-01	101	1
2023-10-01	102	0
2023-10-01	103	0

 

步骤 3：计算次日留存率

基于步骤 2 的结果，按日期分组，统计总用户数和留存用户数，最后计算比例：
sql

-- 步骤1：统计用户每日登录日期（去重）
WITH user_login_daily AS (
    SELECT user_id, DATE(login_time) AS login_date 
    FROM user_login 
    GROUP BY user_id, DATE(login_time)
),
-- 步骤2：标记用户次日是否留存
retention_flag AS (
    SELECT a.login_date AS current_date, a.user_id,
           CASE WHEN b.user_id IS NOT NULL THEN 1 ELSE 0 END AS is_retention
    FROM user_login_daily a
    LEFT JOIN user_login_daily b 
        ON a.user_id = b.user_id AND b.login_date = a.login_date + INTERVAL 1 DAY
)
-- 步骤3：计算每日留存率
SELECT current_date, 
       COUNT(DISTINCT user_id) AS total_users,  -- 当日总用户
       SUM(is_retention) AS retention_users,   -- 次日留存用户
       ROUND(SUM(is_retention)*100.0/COUNT(DISTINCT user_id), 2) AS retention_rate  -- 留存率（%）
FROM retention_flag 
GROUP BY current_date 
ORDER BY current_date;

最终结果（基于示例数据）：
 

current_date	user_id	is_retention
2023-10-01	3	1
2023-10-02	2	0

 

五、关键注意事项

1、去重是前提：

同一用户一天内多次登录，需用GROUP BY或DISTINCT去重，否则会导致 “总用户数” 虚高；

2、日期函数兼容性：

不同数据库的日期偏移函数不同（如 MySQL 用+ INTERVAL 1 DAY，PostgreSQL 用+ 1，SQL Server 用DATEADD(day,1,login_date)），需根据实际数据库调整；

3、空值处理：

若某日期没有次日登录数据（如最后一天），retention_users会为 0，属于正常情况。

六、总结

次日留存率的计算核心是 “用户 - 日期” 的关联分析，通过去重、自连接、分组统计 3 步即可实现。掌握这一方法后，还可以拓展到 7 日留存、30 日留存的计算（只需调整日期偏移量）。
留存率是产品运营的 “晴雨表”，通过 SQL 快速计算并监控这一指标，能帮助我们及时发现用户流失问题，优化产品体验。