SQL如何通过嵌套查询计算用户留存率_多步关联逻辑解析-SQL-PHP中文网

用户留存率是某日新增用户中在后续第n天仍活跃的比例，需以每个用户的首次登录日期为基准计算，不能直接按日期group by统计，否则分母重复计数导致失真。

sql如何通过嵌套查询计算用户留存率_多步关联逻辑解析

什么是用户留存率，为什么不能直接用 GROUP BY 算

用户留存率不是简单统计「第 N 天还登录的人数 / 首次登录总人数」，关键在于：必须锁定每个用户的「首次登录日期」作为分母基准，再看他们在后续某天是否活跃。如果直接 GROUP BY 日期再算比例，会把同一个人在不同日期的登录当成独立样本，分母被重复计算，结果严重失真。

用子查询先固定每个用户的 first_login_date

核心是先用子查询为每个用户打上「首次登录时间」标签，后续所有留存判断都基于这个锚点。常见错误是试图在一个 SELECT 里同时聚合又引用窗口函数，导致语法报错或逻辑错位。

正确做法：外层查用户和登录日期，内层子查询用 MIN(login_time) 按 user_id 分组，返回每个用户的 first_login_date
注意 login_time 类型：如果是带时分秒的 TIMESTAMP，记得用 DATE(login_time) 截断，否则同一天多次登录可能被误判为不同日期

示例片段：

SELECT u.user_id, DATE(u.login_time) AS login_date,
       (SELECT MIN(DATE(login_time)) 
        FROM user_logins u2 
        WHERE u2.user_id = u.user_id) AS first_login_date
FROM user_logins u

用 DATE_DIFF 或日期运算判断是否“次日留存”“7日留存”

MySQL、PostgreSQL、BigQuery 对日期差的函数名不同，但逻辑一致：用当前登录日期减去 first_login_date，看差值是否等于目标天数（如 1 表示次日留存，7 表示 7 日留存）。容易忽略的是时区和日期截断一致性。

MySQL：用 DATEDIFF(login_date, first_login_date)
PostgreSQL：用 login_date - first_login_date（返回整数天）
BigQuery：用 DATE_DIFF(login_date, first_login_date, DAY)
务必确保两个字段都是 DATE 类型，避免 TIMESTAMP 直接相减导致精度误差
别在 WHERE 里写 DATEDIFF(...) = 1 后再 GROUP BY —— 这只能查出「有次日行为的用户」，漏掉首日注册但次日没登录的用户，分母就错了

按首日分组 + 条件聚合，一次性算出多日留存率

最终要的是「某日新增用户中，多少人在第 N 天回来」，所以分母必须是按 first_login_date 分组后的用户数，分子是该组中满足 login_date = first_login_date + N 的用户数。用条件聚合（CASE WHEN）比多次 JOIN 更清晰、更高效。

示例（MySQL）：

SELECT first_login_date,
       COUNT(DISTINCT user_id) AS new_users,
       COUNT(DISTINCT CASE WHEN DATEDIFF(login_date, first_login_date) = 1 THEN user_id END) / COUNT(DISTINCT user_id) AS retention_d1,
       COUNT(DISTINCT CASE WHEN DATEDIFF(login_date, first_login_date) = 7 THEN user_id END) / COUNT(DISTINCT user_id) AS retention_d7
FROM (
  SELECT u.user_id, DATE(u.login_time) AS login_date,
         (SELECT MIN(DATE(login_time)) FROM user_logins u2 WHERE u2.user_id = u.user_id) AS first_login_date
  FROM user_logins u
) t
GROUP BY first_login_date

性能提示：子查询中的 SELECT MIN(...) FROM ... WHERE user_id = ? 在大数据量下可能慢，可提前物化为临时表或用窗口函数替代（如 MIN(login_date) OVER (PARTITION BY user_id)），但要注意窗口函数在 MySQL 8.0+ 才稳定支持

最常被绕过的细节是：留存率分母必须严格限定在「该首日新增用户池」，而不是全量用户；且分子必须去重计数，否则一个用户当天多次登录会被重复计入。