正确留存分析需以每个用户首次活跃日为锚点，用MIN(event_time) OVER (PARTITION BY user_id)计算，前置过滤NULL和无效日期，并按分组维度（如channel）联合PARTITION BY确保锚点精准；分母限定首日范围，显式标记零留存用户。
用 MIN() OVER (PARTITION BY user_id) 定义每个用户的首次活跃日
留存分析的起点不是某天所有用户，而是每个用户自己的“第一天”。直接按自然日分组会把新老用户混在一起，结果完全失真。必须先为每个 user_id 算出其最小 event_time，作为后续所有窗口计算的锚点。
常见错误是写成 MIN(event_time) 不加 PARTITION BY，结果得到全表最早时间，所有用户都对齐到同一个日期；或者漏掉 WHERE event_time IS NOT NULL，让脏数据（如 '0000-00-00'）污染 MIN() 结果。
- 正确写法：
MIN(event_time) OVER (PARTITION BY user_id) - 务必前置过滤：
WHERE event_time >= '2026-01-01' AND event_time IS NOT NULL - 如果需要保留首次行为的上下文（如
channel或device_id），改用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time)并取rnum = 1的行
用 LAG() 或 LEAD() 判断“是否次日回归”，但别只看是否非空
LEAD(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time) 能拿到用户下一次活跃时间，但它本身不等于“次日留存”。真正关键的是当前行和 LEAD() 返回值之间的日期差是否严格等于 1 天。
典型错误是写 COUNT(*) WHERE next_login IS NOT NULL——这统计的是“有后续行为”的用户，不是“第二天就回来”的用户。比如用户在第 1 天和第 3 天登录，LEAD() 返回第 3 天，DATE_DIFF(next_login, event_time, DAY) = 2，不能计入次日留存。
- MySQL：用
TIMESTAMPDIFF(DAY, event_time, next_login) = 1 - BigQuery/PostgreSQL：用
DATE_DIFF(next_login, event_time, DAY) = 1 - 必须显式写
PARTITION BY user_id ORDER BY event_time，缺一不可；ORDER BY 后建议加, id防止秒级重复导致排序不稳定
用 DATEDIFF + CASE WHEN 做条件聚合，而不是 GROUP BY 时间差
想看第 7 天留存？不要写 GROUP BY DATEDIFF(event_time, first_active_date)。这种写法会自动丢掉“首日登录后第 2–6 天完全没行为”的用户——他们在日志里不出现，GROUP BY 就无法覆盖这些“零留存”样本。
正确做法是在用户粒度上打标：这个人有没有在 first_active_date + 7 这天出现过？再用外层 COUNT(DISTINCT CASE WHEN ... THEN user_id END) 统计。
- 示例逻辑：
COUNT(DISTINCT CASE WHEN DATEDIFF(event_time, first_active_date) = 7 THEN user_id END) - 分母必须限定为“首日落在观察期内的新用户”，例如：
WHERE first_active_date BETWEEN '2026-05-01' AND '2026-05-07' - Hive 2.x 用户注意：
datediff()跨年计算不准，优先用to_date()+ 字符串转整数或升级到 Hive 3+
分组维度（如 channel）参与 PARTITION BY 才算真正分组留存
如果要算“各渠道的次日留存”，不能先全局算 first_active_date 再按 channel 分组。那样会导致 A 渠道用户被套用 B 渠道用户的首次时间，分母错乱。
根本原则是：每个分组维度（channel、region、device_type）必须和 user_id 一起参与窗口函数的 PARTITION BY，确保“该用户在该渠道内的首次行为”被独立识别。
- 正确：
MIN(event_time) OVER (PARTITION BY channel, user_id) - 错误：
MIN(event_time) OVER (PARTITION BY user_id)后再GROUP BY channel - CTE 分步写更安全：先
GROUP BY channel, user_id算首日，再 LEFT JOIN 行为表匹配回访，兼容性更好
