窗口函数标记用户生命周期需先准确定义阶段（如注册→首次付费→连续3天活跃→流失），用lag()/lead()结合coalesce补全时间差，避免null与跨阶段跳跃；阶段判断优先case when+时间差逻辑，慎用事件类型字段；注意各数据库时间差函数及时区差异；max/min over无法替代row_number()或array_agg[offset]定位第n次事件；须加守门逻辑（如countif）防止漏斗断裂导致虚高时长。
窗口函数怎么分阶段标记用户生命周期状态
直接用 LAG() 或 LEAD() 拿到相邻行为时间差，再结合业务规则打标，比用自连接或子查询快得多。关键不是“算LTV”，而是先准确定义各阶段——比如注册→首次付费→连续3天活跃→流失（30天无行为）。
常见错误是把时间戳直接相减却不处理 NULL 或跨阶段跳跃。比如用户注册后7天才首充，中间没行为，LAG() 会返回 NULL，不能直接参与计算。
- 用
COALESCE(LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time), CURRENT_TIMESTAMP)补全末次行为后的截止时间 - 阶段判断优先用
CASE WHEN+ 时间差逻辑，而不是依赖事件类型字段（有些日志里“激活”和“登录”混用） - 避免在窗口函数里嵌套复杂条件——先用
ROW_NUMBER()标序号，再在外层 CASE 中引用更易读
用 DATE_DIFF 或 EXTRACT 算阶段时长时要注意什么
不同数据库对时间差的单位处理差异很大：DATE_DIFF('day', start_time, end_time)（BigQuery）和 EXTRACT(EPOCH FROM (end_time - start_time)) / 86400（PostgreSQL）结果可能差1天，尤其当起止时间带时分秒。
更麻烦的是时区——如果 event_time 是 UTC 存储，但业务要求按用户本地时区算“活跃天数”，窗口函数本身不处理时区转换，得提前用 AT TIME ZONE 对齐。
- BigQuery：用
DATE_DIFF配合DATE()截断，避免小数天干扰阶段归类 - PostgreSQL：
end_time::date - start_time::date比AGE()更稳定，后者返回 interval 容易误判 - MySQL：
TIMESTAMPDIFF(DAY, start_time, end_time)是唯一靠谱选项，DATEDIFF会丢弃时间部分
为什么 MAX() OVER 和 MIN() OVER 不能直接替代生命周期阶段聚合
因为 LTV 阶段时长不是单点极值问题——比如“首次付费到首次复购”要找第二次付费时间，不是所有付费时间里的最大值。用 MIN() 只能得到第一次，MAX() 得到最后一次，中间过程全丢了。
典型陷阱是写成 MAX(CASE WHEN event_type='pay' THEN event_time END) OVER (PARTITION BY user_id)，这只能拿到最后付款时间，无法定位“第二次付款”这个关键节点。
- 真正需要的是按事件类型排序后取第 N 行：用
ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY event_time) - 或者用
ARRAY_AGG(event_time ORDER BY event_time)[OFFSET(1)]（BigQuery）直接取第二个元素 - MySQL 8.0+ 可用
NTH_VALUE(event_time, 2) OVER (PARTITION BY user_id ORDER BY event_time)，但注意默认是RESPECT NULLS，需显式指定
如何避免窗口函数在用户漏斗断裂时输出错误时长
用户生命周期常有断层：注册了但没激活，激活了但没付费，付费了但没复购。窗口函数默认“按顺序填空”，比如用 LEAD() 计算“注册到激活”时长，若用户根本没激活事件，就会把注册时间连到下一条任意事件（比如客服咨询），导致时长虚高。
必须加阶段守门逻辑：只对满足前置条件的行才计算后续阶段时长。
- 先用
COUNTIF(event_type='activate') OVER (PARTITION BY user_id)判断是否激活过 - 再用
CASE WHEN activate_cnt > 0 THEN DATE_DIFF(...) END控制输出 - 不要依赖
FILTER（如 BigQuery 的ARRAY_AGG(...) FILTER (WHERE ...)），它在窗口内不可用，得用条件聚合替代
阶段定义越细，漏斗断裂越常见；别指望一个窗口函数调用覆盖全部路径，拆成多个 CTE 或子查询反而更稳。
