用户留存分析是评估产品健康度的核心指标，通过计算新增用户在特定时间周期后仍然活跃的比例，帮助运营团队判断产品对用户的长期价值。SQL窗口函数能够在保留原始数据行的同时，对指定范围的数据进行计算，非常适合处理用户行为时间序列相关的分析场景，避免了传统多表关联带来的性能损耗和代码复杂度。
数据建模准备
首先我们需要构建基础的用户行为数据表，存储用户的注册时间和后续活跃时间，表结构如下：
-- 用户行为基础表
CREATE TABLE user_action (
user_id INT COMMENT '用户ID',
action_date DATE COMMENT '行为日期',
action_type VARCHAR(20) COMMENT '行为类型，register表示注册，active表示活跃'
) COMMENT '用户行为记录表';
表中核心字段说明：
- user_id：唯一标识一个用户
- action_date：用户产生行为的具体日期
- action_type：区分用户是注册行为还是后续活跃行为
窗口函数在留存分析中的核心应用
留存分析的核心逻辑是：先找到每个用户的首次注册日期，再计算后续活跃日期与注册日期的差值，统计不同差值区间的活跃用户数。窗口函数可以帮我们快速关联每个用户的注册时间和后续活跃时间，不需要多次关联原表。
步骤1：标记用户注册日期
使用窗口函数MIN() OVER(PARTITION BY user_id)计算每个用户的最早行为日期作为注册日期：
SELECT
user_id,
action_date,
action_type,
MIN(action_date) OVER(PARTITION BY user_id) AS reg_date
FROM user_action;
步骤2：计算活跃日期与注册日期的差值
基于上一步的结果，过滤出活跃行为，计算活跃日期和注册日期的天数差：
WITH user_reg_info AS (
SELECT
user_id,
action_date,
action_type,
MIN(action_date) OVER(PARTITION BY user_id) AS reg_date
FROM user_action
)
SELECT
user_id,
reg_date,
action_date,
DATEDIFF(action_date, reg_date) AS days_after_reg
FROM user_reg_info
WHERE action_type = 'active';
步骤3：统计各周期留存率
根据天数差统计不同留存周期的留存用户数，再结合新增用户总数计算留存率：
WITH user_reg_info AS (
SELECT
user_id,
action_date,
action_type,
MIN(action_date) OVER(PARTITION BY user_id) AS reg_date
FROM user_action
),
active_days_info AS (
SELECT
user_id,
reg_date,
DATEDIFF(action_date, reg_date) AS days_after_reg
FROM user_reg_info
WHERE action_type = 'active'
),
reg_user_cnt AS (
SELECT
reg_date,
COUNT(DISTINCT user_id) AS total_reg_user
FROM user_reg_info
WHERE action_type = 'register'
GROUP BY reg_date
)
SELECT
r.reg_date,
r.total_reg_user,
COUNT(DISTINCT CASE WHEN a.days_after_reg = 1 THEN a.user_id END) AS day_1_retain_user,
COUNT(DISTINCT CASE WHEN a.days_after_reg = 7 THEN a.user_id END) AS day_7_retain_user,
COUNT(DISTINCT CASE WHEN a.days_after_reg = 30 THEN a.user_id END) AS day_30_retain_user,
ROUND(COUNT(DISTINCT CASE WHEN a.days_after_reg = 1 THEN a.user_id END) / r.total_reg_user * 100, 2) AS day_1_retain_rate,
ROUND(COUNT(DISTINCT CASE WHEN a.days_after_reg = 7 THEN a.user_id END) / r.total_reg_user * 100, 2) AS day_7_retain_rate,
ROUND(COUNT(DISTINCT CASE WHEN a.days_after_reg = 30 THEN a.user_id END) / r.total_reg_user * 100, 2) AS day_30_retain_rate
FROM reg_user_cnt r
LEFT JOIN active_days_info a ON r.reg_date = a.reg_date
GROUP BY r.reg_date, r.total_reg_user
ORDER BY r.reg_date;
结果解读与优化
上述查询输出的结果包含每日新增用户数、次日/7日/30日留存用户数以及对应的留存率，运营团队可以直接基于这些数据判断产品留存表现的变化趋势。如果数据量较大，可以在user_action表的user_id和action_date字段上建立联合索引，提升窗口函数的计算效率。
相比传统的先统计注册用户表，再多次关联活跃表计算不同周期留存的方式，使用窗口函数的方案只需要全表扫描一次，代码逻辑更清晰，执行效率也更高，适合日常高频的留存分析需求。