cohort留存队列是按用户首次行为时间分组并追踪其后续活跃情况的用户群；不能直接用count()，因其会丢失用户粒度时序关系，必须先用min() over(partition by user_id)计算每位用户的first_login_date，再基于该锚点判断其在第n天是否留存。
什么是 Cohort 留存队列，为什么不能直接用 COUNT()？
用户留存 Cohort 不是简单统计某天新增人数或某天活跃人数，而是要按“首次行为日期”分组，再追踪这些用户在后续第 N 天是否再次出现。直接用 COUNT() 或 SUM() 会丢失用户粒度的时序关系——你得先锁定每个用户的 first_login_date，再判断其是否在 first_login_date + N 天后仍有行为。
关键陷阱：别在聚合前就 GROUP BY login_date，否则无法区分“新用户”和“回访用户”。必须先做用户级宽表（每人一行，带首次登录日 + 各日是否活跃标记），再按首次日和间隔天数交叉聚合。
如何用窗口函数生成每位用户的 first_login_date？
这是 Cohort 分析的起点。你得从原始日志中为每个 user_id 提取最早一次行为时间，不能靠 MIN(login_time) GROUP BY user_id 后再 JOIN——那样容易因 JOIN 条件不严谨导致重复或丢失。
推荐写法是用 MIN() OVER (PARTITION BY user_id) 直接计算：
SELECT user_id, DATE(login_time) AS login_date, MIN(DATE(login_time)) OVER (PARTITION BY user_id) AS first_login_date FROM user_log WHERE login_time >= '2024-01-01';
- 务必用
DATE()统一截断到日级，避免时间戳精度干扰分组 - 如果表里有多个行为类型（如注册、登录、下单），需先过滤出“可定义为首次行为”的事件（例如只取
event_type = 'login'） - 注意
MIN() OVER对 NULL 敏感；若部分user_id没有有效时间，结果会是 NULL，后续GROUP BY会丢弃整行
如何构造“首日 + 第 N 日是否留存”的二维结构？
核心是把用户行为拉平成“每位用户 × 每个观察天数”的标记矩阵，再用条件聚合转置。常见错误是试图用多个 LEFT JOIN 关联不同日期，性能差且难以扩展。
更稳的方式是用 CASE WHEN + MAX() 做布尔聚合：
WITH user_cohort AS (
SELECT
user_id,
MIN(DATE(login_time)) AS first_login_date
FROM user_log
GROUP BY user_id
),
cohort_daily AS (
SELECT
uc.first_login_date,
DATEDIFF(DATE(ul.login_time), uc.first_login_date) AS day_offset,
uc.user_id
FROM user_cohort uc
INNER JOIN user_log ul ON uc.user_id = ul.user_id
AND DATE(ul.login_time) >= uc.first_login_date
)
SELECT
first_login_date,
COUNT(*) AS cohort_size,
COUNT(CASE WHEN day_offset = 0 THEN 1 END) AS d0,
COUNT(CASE WHEN day_offset = 1 THEN 1 END) AS d1,
COUNT(CASE WHEN day_offset = 7 THEN 1 END) AS d7,
COUNT(CASE WHEN day_offset = 30 THEN 1 END) AS d30
FROM cohort_daily
GROUP BY first_login_date
ORDER BY first_login_date;
-
day_offset必须是非负整数，所以INNER JOIN条件里加了DATE(ul.login_time) >= uc.first_login_date - 如果只想看“次日留存率”，
d1 / d0就是结果；但注意d0是 cohort_size，不是当日总登录数 - MySQL 的
DATEDIFF(a,b)返回 a−b 的天数，PostgreSQL 要写(a::date - b::date)，语法差异直接影响day_offset计算
为什么留存率常比预期低？检查这三点
实际跑出来 d1/d0 接近 0？大概率不是 SQL 写错了，而是数据语义没对齐：
- 确认
first_login_date是否真代表“首次可归因行为”——比如用户用微信授权登录，但数据库里第一次记录是 3 天后补全手机号，那first_login_date就偏晚 - 检查时间范围：若
user_log表只保留最近 90 天数据，那 2024-01-01 的 cohort 到第 30 天（1 月 31 日）还能查到，但 2024-02-01 的 cohort 到第 30 天（3 月 2 日）可能已超出保留期，d30就是 0 - 去重逻辑是否一致：
COUNT(DISTINCT user_id)在cohort_daily层面做，还是在最终GROUP BY后做？前者防重复计数，后者可能高估（同一用户某天多次登录被算多次）
最易被忽略的是：Cohort 分析默认假设用户身份稳定。如果 user_id 会重置（如游客 ID、设备 ID 变更）、或存在账号合并，那 first_login_date 就失去意义——这时候得先做用户 ID 映射对齐，SQL 层解决不了。
