lag()找上次活跃时间、lead()找下次活跃时间，结合时间差判断流失或回流；关键在严格按user_id分组、event_time升序排序，避免跨用户错位、null干扰及日期计算陷阱。
直接说结论：用 LAG() 找“上次活跃时间”，用 LEAD() 找“下次活跃时间”，再结合时间差判断流失或回流——但关键不在函数本身，而在怎么定义“流失”和“回流”的业务口径，以及如何避免跨用户错位、NULL 干扰和日期计算陷阱。
为什么 LAG/LEAD 是识别流失/回流的最小可行工具
用户行为是时间序列，流失本质是“该来没来”，回流是“走了又来”。LAG() 和 LEAD() 能在每个用户内部按时间拉出相邻行为点，不需要自连接或大量 JOIN。但前提是：必须严格按 user_id 分组、按 event_time 排序，否则 A 用户的上一行可能被误算成 B 用户的数据。
常见错误现象：
- 漏写
PARTITION BY user_id，导致全表排序，LAG(event_time)拿到的是其他用户的前一行时间 -
ORDER BY event_time没加ASC，某些数据库默认DESC，LAG()实际取的是“后一行” - 原始数据里有重复
event_time（如毫秒级日志），未加二级排序（如ORDER BY event_time, event_id），窗口顺序不稳定
计算用户级时间间隔：用 LAG() 得到“距上次活跃天数”
核心逻辑是：对每个用户，把当前行为时间减去上一次行为时间，得到空窗期。这个差值决定了是否流失。
实操建议：
- MySQL 用
TIMESTAMPDIFF(DAY, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time), event_time) - PostgreSQL/Redshift 用
event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)（返回 INTERVAL，需EXTRACT(DAY FROM ...)） - 显式处理
LAG()返回 NULL：首行无“上次”，差值为 NULL，别让它参与后续WHERE或CASE判断，先用子查询或 CTE 过滤掉 - 避免用
DATE_SUB(event_time, INTERVAL 1 DAY)这类硬编码偏移——流失阈值是业务定义的（比如 7 天、30 天），不是固定值
识别回流节点：用 LEAD() 定位“中断后再次出现”
回流不是“任意两次登录”，而是“中断 ≥ N 天后重新活跃”。只靠 LAG() 只能知道“从哪断的”，LEAD() 才能确认“什么时候续上的”。
典型写法：
SELECT user_id, event_time, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time, LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time, TIMESTAMPDIFF(DAY, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time), event_time) AS gap_from_prev, TIMESTAMPDIFF(DAY, event_time, LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time)) AS gap_to_next FROM user_events WHERE event_time >= '2025-01-01'
关键点：
- 回流节点 = 当前行满足：
gap_from_prev >= 30且gap_to_next IS NOT NULL（说明确实有下一次行为） - 不能只查
gap_from_prev >= 30就标为“回流”，那只是“流失起点”；必须配合LEAD()确认后续真实发生了行为 - 如果用户最后一次行为后至今没再登录，
LEAD()返回 NULL，gap_to_next为 NULL，这种“未回流”状态要保留在结果里，不能被WHERE过滤掉
最容易被忽略的细节：NULL 值和边界行为
很多人卡在最后一步：明明写了 LAG()，结果 WHERE gap_from_prev > 30 查不到数据。问题往往出在 NULL 上——LAG() 首行返回 NULL，而任何与 NULL 的比较（> 30、= NULL）结果都是 UNKNOWN，被 WHERE 当作 FALSE 过滤掉了。
正确做法只有两个：
- 在 WHERE 条件中显式写
gap_from_prev IS NOT NULL AND gap_from_prev > 30 - 或者更稳妥：用 CTE 先算出所有 gap 字段，再在外部查询中过滤，避免窗口函数和 WHERE 的执行顺序冲突
另外，不同数据库对 LAG() 的默认值处理不一致：PostgreSQL 默认返回 NULL，MySQL 8.0+ 也是，但旧版 Hive 可能返回 0 或报错。上线前务必在目标环境验证 NULL 行为。
