计算用户留存率的核心是先确定每个用户的首次访问时间，再统计这些首次访问用户在后续时间段的活跃情况。使用ROW_NUMBER窗口函数可以高效完成首次访问时间的标记，避免复杂的自连接操作。
一、基础表结构设计
首先我们需要用户访问记录表，假设表名为user_visit_log，结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| user_id | bigint | 用户唯一标识 |
| visit_time | datetime | 用户访问时间 |
二、用ROW_NUMBER标识首次访问时间
ROW_NUMBER函数可以对分组内的记录按指定顺序编号，我们按用户分组，按访问时间升序排序，编号为1的记录就是该用户的首次访问记录。
首先生成每个用户的首次访问时间临时表：
-- 生成用户首次访问时间临时表
WITH first_visit AS (
SELECT
user_id,
visit_time AS first_visit_time,
-- 按用户分组，访问时间升序排序，取第一条为首次访问
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_time ASC) AS rn
FROM user_visit_log
)
SELECT
user_id,
first_visit_time
FROM first_visit
WHERE rn = 1
上述语句中，PARTITION BY user_id表示按用户ID分组，ORDER BY visit_time ASC表示组内按访问时间从小到大排序，ROW_NUMBER()会给每个组内的记录生成从1开始的连续编号，编号为1的就是最早的那条访问记录，即首次访问。
三、基于首次访问时间计算留存率
留存率的计算公式为：某周期留存率 = 首次访问后第N天仍访问的用户数 / 首次访问的总用户数 * 100%。我们基于上面的首次访问临时表，关联用户的后续访问记录来计算。
1. 计算次日留存率
次日留存指用户首次访问后的第二天再次访问的比例：
-- 计算次日留存率
WITH first_visit AS (
SELECT
user_id,
visit_time AS first_visit_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_time ASC) AS rn
FROM user_visit_log
),
first_user AS (
SELECT user_id, first_visit_time
FROM first_visit
WHERE rn = 1
)
SELECT
COUNT(DISTINCT fu.user_id) AS total_first_visit_users, -- 首次访问总用户数
COUNT(DISTINCT CASE WHEN DATEDIFF(uvl.visit_time, fu.first_visit_time) = 1 THEN fu.user_id END) AS next_day_retain_users, -- 次日留存用户数
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(uvl.visit_time, fu.first_visit_time) = 1 THEN fu.user_id END) / COUNT(DISTINCT fu.user_id) * 100, 2), '%') AS next_day_retain_rate -- 次日留存率
FROM first_user fu
LEFT JOIN user_visit_log uvl
ON fu.user_id = uvl.user_id
AND uvl.visit_time >= fu.first_visit_time -- 只关联首次访问及之后的记录
2. 计算7日、30日留存率
只需调整DATEDIFF的判断条件即可，7日留存判断DATEDIFF(uvl.visit_time, fu.first_visit_time) BETWEEN 1 AND 7，30日留存判断DATEDIFF(uvl.visit_time, fu.first_visit_time) BETWEEN 1 AND 30：
-- 计算7日、30日留存率
WITH first_visit AS (
SELECT
user_id,
visit_time AS first_visit_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_time ASC) AS rn
FROM user_visit_log
),
first_user AS (
SELECT user_id, first_visit_time
FROM first_visit
WHERE rn = 1
)
SELECT
COUNT(DISTINCT fu.user_id) AS total_first_visit_users,
-- 7日留存用户数
COUNT(DISTINCT CASE WHEN DATEDIFF(uvl.visit_time, fu.first_visit_time) BETWEEN 1 AND 7 THEN fu.user_id END) AS seven_day_retain_users,
-- 7日留存率
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(uvl.visit_time, fu.first_visit_time) BETWEEN 1 AND 7 THEN fu.user_id END) / COUNT(DISTINCT fu.user_id) * 100, 2), '%') AS seven_day_retain_rate,
-- 30日留存用户数
COUNT(DISTINCT CASE WHEN DATEDIFF(uvl.visit_time, fu.first_visit_time) BETWEEN 1 AND 30 THEN fu.user_id END) AS thirty_day_retain_users,
-- 30日留存率
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(uvl.visit_time, fu.first_visit_time) BETWEEN 1 AND 30 THEN fu.user_id END) / COUNT(DISTINCT fu.user_id) * 100, 2), '%') AS thirty_day_retain_rate
FROM first_user fu
LEFT JOIN user_visit_log uvl
ON fu.user_id = uvl.user_id
AND uvl.visit_time >= fu.first_visit_time
四、注意事项
- 如果访问时间包含时分秒，建议先对
visit_time做日期格式化，比如用DATE(visit_time)取日期部分，避免同一天多次访问被重复计算。 - ROW_NUMBER函数生成的编号是连续的，即使有相同访问时间也会生成不同编号，如果需要相同时间取同一条，可以改用
RANK()或DENSE_RANK()函数。 - 如果数据量较大，建议在
user_visit_log表的user_id和visit_time字段上建立联合索引，提升查询效率。
SQLROW_NUMBER用户留存率首次访问时间修改时间：2026-06-25 18:54:34