直接用LAG算次日留存会漏掉静默用户，因其仅检测相邻登录是否间隔1天，而真实留存要求判断用户是否在“首次登录日+1天”存在登录行为，与中间是否断连无关。
为什么直接用 LAG 算次日留存会漏掉静默用户
很多同学一上来就写 LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date)，再比对日期差是否为1——这只能捕获连续活跃的用户，但真实场景里，用户可能在第1天和第3天登录，中间断了一天，仍属于“次日留存”的统计口径（即：注册后第2天有登录行为，不管第2天有没有）。关键不是看「相邻登录」，而是看「注册日 + N 天是否存在登录」。
实操建议：
- 先明确留存定义：是「新增用户在 T+N 天内至少登录一次」，不是「连续登录」
- 用
MIN(login_date)按user_id算出每个用户的首次登录日（即「注册日」），作为基准锚点 - 后续所有留存判断都基于「注册日 + N」这个固定日期，而非上一次登录日
- 避免在同一个
OVER子句里混用多层逻辑（比如一边PARTITION BY user_id，一边又想按注册日分组），容易导致窗口范围错乱
如何用 DATEADD + MAX 窗口函数生成宽表结构
留存宽表本质是把每个用户的一行，扩展成「是否在注册后第1/7/30天登录过」等布尔列。不能靠多次 JOIN，否则笛卡尔爆炸；也不能用 CASE WHEN 套子查询，性能崩得快。核心是用窗口函数提前聚合好各天的存在性，再用条件聚合收口。
实操建议：
- 先构造辅助列：
DATEADD('day', 1, first_login)、DATEADD('day', 7, first_login)等，注意不同数据库语法差异（PostgreSQL 用(first_login + INTERVAL '1 day')，BigQuery 用DATE_ADD(first_login, INTERVAL 1 DAY)） - 用
MAX(CASE WHEN login_date = DATEADD('day', 1, first_login) THEN 1 ELSE 0 END) OVER (PARTITION BY user_id)判断是否存在该日登录——这里必须用MAX（或BOOL_OR），不能用COALESCE或IFNULL，因为单个用户当天可能有多次登录，要的是「存在性」而非「值本身」 - 最终用
GROUP BY user_id, first_login+MAX()聚合，把窗口结果坍缩成一行
ROW_NUMBER() 和 RANK() 在去重时的区别很致命
如果原始日志含重复登录记录（比如埋点重复上报），直接按 user_id + login_date 去重可能误杀——同一用户同一天多次登录是合法行为，不应合并；但同一用户同一秒的重复事件才该去重。这时候窗口函数的排序逻辑就决定结果是否可信。
实操建议：
- 去重优先用
ROW_NUMBER() OVER (PARTITION BY user_id, login_date, event_time ORDER BY received_time)，保留每条记录的时间戳粒度，再过滤rn = 1 - 别用
RANK()或DENSE_RANK()去重，它们会对相同排序键赋予相同序号，导致本该保留的多条合法记录被当成重复删掉 - 如果数据源没有精确到秒的字段，至少加一个唯一ID（如
event_id）进ORDER BY，避免窗口函数因排序不唯一而返回非确定结果
宽表中「0/1」字段为何比「日期」字段更省空间且更易统计
有人习惯存 day1_login_date（值为 NULL 或具体日期），看起来信息更全，但实际带来三个问题：下游计算留存率时要写 COUNT(day1_login_date) / COUNT(*)，可读性差；NULL 值在多数 BI 工具里默认不参与聚合；更重要的是，日期字段占用存储远高于布尔位（尤其当表达百万级用户 × 数十天维度时）。
实操建议：
- 一律用
is_day1_retained INT DEFAULT 0这类命名，值为 0 或 1，语义清晰，聚合直接AVG(is_day1_retained)就是留存率 - 建表时对这类字段加
CHECK (is_day1_retained IN (0,1))约束，防上游脏数据污染指标 - 如果后续要支持「回溯N天」分析（比如看过去30天每天的次日留存），不要硬编码30列，改用
UNPIVOT或应用层展开，宽表只存固定口径（如D1/D7/D30）
真正难的不是写出窗口函数，而是确认「注册日」怎么定义、静默期如何处理、重复事件如何取舍——这些业务规则一旦定错，SQL 写得再漂亮，产出的宽表也是废表。
