如何使用MySQL计算2018年8月的日度次日用户留存率

前提说明 #

你提供的log_event表存储用户全量行为日志，三个字段分别对应用户ID、行为发生的Unix时间戳、行为类型，本次计算的Day-1留存规则为：Day0注册的用户，在Day1有打开应用行为，即计入Day0的次日留存用户。

计算逻辑 #

• 提取2018年8月所有注册用户的注册日期，记为注册日（Day0）
• 提取所有用户打开应用的行为日期
• 关联两个数据集，匹配注册用户在注册次日是否有打开行为
• 按注册日聚合，计算每日次日留存率 = 次日留存用户数 / 当日注册总用户数

实现SQL（MySQL版本） #

SELECT 
    reg_date AS '注册日期',
    COUNT(DISTINCT reg_user_id) AS '当日注册用户数',
    COUNT(DISTINCT open_user_id) AS '次日留存用户数',
    ROUND(COUNT(DISTINCT open_user_id) / COUNT(DISTINCT reg_user_id), 4) AS '次日留存率'
FROM (
    -- 提取2018年8月所有注册用户及注册日期
    SELECT 
        user_id AS reg_user_id,
        DATE(FROM_UNIXTIME(event_date_time)) AS reg_date
    FROM log_event
    WHERE event = 'Registered'
    AND DATE(FROM_UNIXTIME(event_date_time)) BETWEEN '2018-08-01' AND '2018-08-31'
) reg_users
LEFT JOIN (
    -- 提取所有用户打开应用的日期
    SELECT 
        user_id AS open_user_id,
        DATE(FROM_UNIXTIME(event_date_time)) AS open_date
    FROM log_event
    WHERE event = 'Opened app'
) open_logs
ON reg_users.reg_user_id = open_logs.open_user_id
AND open_logs.open_date = DATE_ADD(reg_users.reg_date, INTERVAL 1 DAY)
GROUP BY reg_date
ORDER BY reg_date;

兼容说明 #

如果使用PostgreSQL数据库，将代码中所有FROM_UNIXTIME(event_date_time)替换为to_timestamp(event_date_time)即可，核心逻辑完全一致。你提供的样例数据中仅存在1位2018年8月的注册用户，且该用户无次日打开应用行为，对应计算结果中该日期的次日留存率为0。

内容的提问来源于stack exchange，提问作者ishant kaushik