窗口函数计算周/月留存需先用DATE_TRUNC或等效函数将日期对齐到周期起点（如周一或每月1号），再按周期分组去重和匹配；直接LAG/LEAD或ROW_NUMBER()会因日期边界不一致导致错误。
窗口函数怎么配合日期截断算周/月留存
直接用 LAG() 或 LEAD() 算留存不行，因为用户行为日期和“自然周/月”边界不一致。必须先将原始日期归到对应周期起点（比如周一或每月1号），再按这个周期分组做用户去重和跨周期匹配。
关键不是窗口函数本身，而是周期对齐——DATE_TRUNC('week', event_time)（PostgreSQL）或 DATE_SUB(event_time, INTERVAL WEEKDAY(event_time) DAY)（MySQL）这类操作得先做，否则窗口内数据是散的。
- PostgreSQL 推荐用
DATE_TRUNC('week', event_time)，它默认以周一为周起始，结果可直接用于PARTITION BY - MySQL 8.0+ 没有
DATE_TRUNC，得手动算：用STR_TO_DATE(DATE_FORMAT(event_time, '%Y-%u'), '%Y-%u %w')或更稳的DATE_SUB(event_time, INTERVAL WEEKDAY(event_time) DAY)得到周一 - 注意时区：如果
event_time是 UTC，但业务看的是北京时间，得先AT TIME ZONE 'Asia/Shanghai'（PostgreSQL）或用CONVERT_TZ()（MySQL）再截断
为什么不能直接用 ROW_NUMBER() 算首次登录周
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) 只能标出每个用户的第一次行为时间点，但“首次登录周”必须是该用户在所有行为中最早出现的那个周（不是某条记录的时间），否则后续和“第N周”的对比会错位。
正确做法是：先用子查询或 CTE 求出每个用户的 first_active_week = DATE_TRUNC('week', MIN(event_time))，再和主表 JOIN，把每条记录打上这个基准周标签。
- 错误写法：
ROW_NUMBER() = 1的那条记录的DATE_TRUNC('week', event_time)当作首周 —— 如果用户当周有多条记录，且第一条不是最早时间，就偏了 - 正确逻辑：先聚合求
MIN(event_time)，再截断，确保首周定义唯一、稳定 - 性能注意：这一步涉及全量用户聚合，数据量大时建议在
user_id, event_time上建复合索引
滚动留存的窗口定义容易漏掉“跨周期用户去重”
留存率 = 第N周活跃用户数 / 首周活跃用户数。分子必须是“在首周出现、且在第N周也出现的用户”，不是简单用 COUNT(DISTINCT user_id) 套窗口函数就能出来。
典型错误是写成：COUNT(DISTINCT user_id) OVER (PARTITION BY week_start ORDER BY week_start ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) —— 这算的是连续7周的累计去重用户，不是留存。
- 正确结构：用 CTE 先生成“用户-首周-各周是否活跃”的宽表，再用条件聚合，例如：
COUNT(DISTINCT CASE WHEN week_start = first_week THEN user_id END)作分母，COUNT(DISTINCT CASE WHEN week_start = first_week + INTERVAL '2 week' THEN user_id END)作分子 - MySQL 用户注意：
INTERVAL表达式在CASE中不能直接用，得提前用变量或派生列算好目标周 - 别忘了过滤：分子分母都只取首周用户子集，否则分母会混入中途加入的用户，导致留存率虚高
按月滚动留存的边界问题比按周更隐蔽
“按月”看似简单，但 DATE_TRUNC('month', event_time) 截的是自然月（1号到当月最后一天），而用户可能在1月31日登录、2月1日又来——这两者被分到不同分区，但实际间隔仅1天。这种“跨月但未跨30天”的情况，在滚动留存里会被误判为流失。
所以严格意义上的“30天滚动留存”，不该用自然月，而要用用户首活跃日推算的“相对月”（如首日是1月15日，则第1月是1月15日–2月14日）。但业务报表常妥协为自然月，此时得明确告知下游：这不是真实滚动，是月度快照叠加。
- 若真要相对月：用
GENERATE_SERIES(first_active_date, NOW(), '1 month'::INTERVAL)（PG）或递归 CTE（MySQL）构造每个用户的活跃窗口，再 JOIN 行为表判断是否命中 - 自然月方案下，务必在报表标题注明“自然月留存”，避免和“D1/D7滚动留存”混淆
- 月份跨度越大，误差越明显：比如首周在2月29日（闰年），次月就是3月29日，但
DATE_TRUNC('month', ...)会把3月1–31日全算进“3月”，掩盖了真实时间差
DATE_TRUNC 和 MIN() 聚合的顺序、CTE 是否物化、JOIN 条件有没有走索引——这些细节比窗口函数语法本身更容易卡住整个分析链路。 
