SQL如何计算用户留存率_ROW_NUMBER标识首次访问时间-SQL-PHP中文网

mysql中datediff(expr1, expr2)函数返回expr1减expr2的整数天数，仅比较日期部分、忽略时分秒，参数为date或可转为date的值，结果正负表示日期先后顺序。

sql如何计算用户留存率_row_number标识首次访问时间

用 `ROW_NUMBER()` 找每个用户的首次访问时间

直接用 MIN(event_time) 虽然能拿到最早时间，但无法关联该次行为的其他字段（比如设备类型、渠道来源）。ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) 才是更安全的做法——它能保留首条完整记录，且天然支持去重逻辑。

常见错误是写成 ORDER BY event_time DESC 或漏掉 PARTITION BY user_id，导致排名全局生效或用户被合并。

必须确保 event_time 是可比较的时间类型（如 DATETIME 或 TIMESTAMP），字符串格式（如 '20240501'）需先转换
若存在毫秒级时间戳，建议先 CAST(event_time AS DATE) 再排序，避免同日多次行为被误判为不同天
测试时可用 WHERE user_id = 'test123' 验证排序结果是否符合预期

`LEFT JOIN` 关联后续活跃日时为什么总少人？

分子（留存用户）必须是「同一组 user_id」在首日之后某天再次出现，不是任意两个日期的交集。用 LEFT JOIN 时最容易踩的坑是没限制右表的日期范围，导致笛卡尔积或重复计数。

正确做法是：左表是首日用户（含 first_day 字段），右表是所有活跃记录，ON 条件里必须同时满足 user_id 相等 + dt = DATE_ADD(first_day, INTERVAL N DAY)。

别用 WHERE dt = ... 做后过滤，会把左表无匹配的用户也踢掉，破坏分母完整性
如果业务要求“自然日对齐”，而原始时间是 UTC，记得先用 CONVERT_TZ(event_time, '+00:00', '+08:00') 调整时区再取 DATE()
Hive/Spark SQL 中 DATE_ADD 不支持负数天数，N=0（即当日新增）要单独处理

计算次日留存时，`datediff` 返回负数怎么办？

datediff(lt, rt) 在 MySQL 里是「后减前」，但如果 lt 是字符串或类型不一致，可能返回 NULL 或意外值；在 Hive 里函数名是 datediff(dt1, dt2)，顺序相反，容易搞反。

最稳妥的方式是统一用 DATE() 截断后做减法：DATE(login_date) - DATE(first_day)（MySQL）或 datediff(to_date(login_date), to_date(first_day))（Hive）。

务必确认两列都是 DATE 类型，STR_TO_DATE 或 to_date() 不可省略
如果数据里有未来时间（如埋点延迟上报），datediff 可能为负，建议加 WHERE datediff(...) >= 0 过滤
别依赖 GROUP BY 自动去重：同一个用户同一天多次登录，COUNT(DISTINCT user_id) 才是唯一可信口径

行转列为留存率报表时，`sum(case when diff = 1 then 1 else 0 end)` 为什么不准？

这个写法统计的是「所有登录记录中 diff=1 的条数」，不是「diff=1 的独立用户数」。如果一个用户在次日登录 5 次，就会被算作 5 人，分母却是 1 人，结果必然失真。

必须套一层 COUNT(DISTINCT user_id)，且 case when 要放在聚合函数内部：

SELECT
  first_day,
  COUNT(DISTINCT user_id) AS t0,
  COUNT(DISTINCT CASE WHEN diff = 1 THEN user_id END) AS t1,
  COUNT(DISTINCT CASE WHEN diff = 7 THEN user_id END) AS t7
FROM cohort_detail
GROUP BY first_day