group by直接分组会漏掉零留存用户，必须用with构造首日用户集合再left join关联登录行为，结合case when标记和count统计各日留存，避免隐式转换与索引失效。
GROUP BY直接分组会漏掉零留存用户
直接写 GROUP BY DATE_SUB(login_date, INTERVAL first_login DAY) 在 Oracle 19c 里根本跑不通——DATE_SUB 是 MySQL 函数，Oracle 用 login_date - first_login 或 TRUNC(login_date) - TRUNC(first_login)。但更关键的问题是：哪怕你把日期差算对了，单纯靠 GROUP BY day_diff 依然会漏掉「注册后第二天完全没登录」的用户。因为他们在登录日志表里压根没记录，GROUP BY 自然无法生成 day_diff = 1 这一行。
常见错误现象：SELECT COUNT(*) FROM login_log WHERE user_id IN (SELECT user_id FROM register WHERE dt = '2026-09-01') AND TRUNC(login_time) = DATE '2026-09-02' 返回 0，但实际次日留存率不该是 0/新用户数 —— 因为这个查询只查有行为的人，没覆盖沉默用户。
- 必须先构造完整首日用户集合（用
DISTINCT user_id去重） - 再用
LEFT JOIN关联后续日期的登录行为，保证分母不丢人 - Oracle 中
TRUNC(event_time)比TO_DATE(TO_CHAR(...))更高效，避免隐式转换
用 WITH + LEFT JOIN 构建留存宽表才可靠
Oracle 19c 的执行计划对嵌套 JOIN 很敏感，别写七层 LEFT JOIN 算 7 日留存。推荐用 WITH 先固化首日 cohort，再用单次 LEFT JOIN + 条件聚合出各日留存数：
WITH cohort AS (
SELECT DISTINCT user_id
FROM register
WHERE TRUNC(register_time) = DATE '2026-09-01'
),
log_days AS (
SELECT c.user_id,
CASE WHEN TRUNC(l.login_time) = DATE '2026-09-02' THEN 1 END AS d1,
CASE WHEN TRUNC(l.login_time) = DATE '2026-09-03' THEN 1 END AS d2,
CASE WHEN TRUNC(l.login_time) = DATE '2026-09-04' THEN 1 END AS d3
FROM cohort c
LEFT JOIN login_log l ON c.user_id = l.user_id
AND TRUNC(l.login_time) IN (DATE '2026-09-02', DATE '2026-09-03', DATE '2026-09-04')
)
SELECT
COUNT(*) AS cohort_size,
COUNT(d1) AS retained_d1,
COUNT(d2) AS retained_d2,
COUNT(d3) AS retained_d3,
ROUND(COUNT(d1)*100.0/COUNT(*), 2) AS r1_rate
FROM log_days;
-
CASE WHEN ... THEN 1 END生成稀疏标记，COUNT(d1)自动忽略 NULL，等价于「有多少人当天有登录」 - WHERE 条件里用
TRUNC(login_time) IN (...)比写多个 OR 更利于 Oracle 走索引 - 如果表没在
TRUNC(login_time)上建函数索引，这条语句会全表扫描，性能断崖下跌
窗口函数替代 GROUP BY 更适合滚动留存
要算每个注册日对应的次日留存曲线（比如 9 月 1 日注册用户的次日留存、9 月 2 日注册用户的次日留存……），硬写一堆 LEFT JOIN 不现实。这时该用 ROW_NUMBER() 或 LEAD()：
例如统计每个用户首次登录后是否在次日再次登录：
SELECT
TRUNC(first_login) AS cohort_date,
COUNT(*) AS cohort_users,
COUNT(CASE WHEN next_login - first_login = 1 THEN 1 END) AS retained_next_day,
ROUND(COUNT(CASE WHEN next_login - first_login = 1 THEN 1 END) * 100.0 / COUNT(*), 2) AS r1_rate
FROM (
SELECT user_id,
FIRST_VALUE(TRUNC(login_time)) OVER (PARTITION BY user_id ORDER BY login_time) AS first_login,
LEAD(TRUNC(login_time), 1) OVER (PARTITION BY user_id ORDER BY login_time) AS next_login
FROM login_log
WHERE login_time >= DATE '2026-09-01'
) t
WHERE first_login >= DATE '2026-09-01'
GROUP BY TRUNC(first_login);
-
FIRST_VALUE比MIN(TRUNC(login_time))更准——它取的是排序后第一行的原始值，不受时分秒截断影响 -
LEAD(..., 1)获取下一次登录日期，减法结果是数字天数，Oracle 直接支持 - 注意
WHERE条件要放在最外层，否则窗口函数会多算历史数据
容易被忽略的 Oracle 特性坑点
Oracle 对空值、字符集、NLS 设置很敏感，这些地方一错，留存率就偏高或偏低：
- 用户表和日志表的
user_id类型不一致（比如一个VARCHAR2(32)，一个CHAR(32)），JOIN时可能因尾部空格匹配失败，导致大量用户被漏计 -
TO_DATE('2026-09-01', 'YYYY-MM-DD')在不同会话的NLS_DATE_FORMAT下可能解析失败，统一用DATE '2026-09-01'字面量 - 如果登录时间字段是
TIMESTAMP WITH TIME ZONE，TRUNC()仍有效，但TO_CHAR(login_time, 'YYYY-MM-DD')可能受时区影响，别这么干 - 分区表若按
login_time范围分区，确保查询条件能精准命中分区，否则扫全表
真正卡住人的从来不是语法，而是「零留存用户要不要进分母」「同一天多次登录算几次」「跨午夜的会话怎么归因」——这些业务口径一旦没对齐，SQL 写得再漂亮也没用。
