LAG()计算次日留存的核心逻辑是：对每个user_id按event_date升序排列后，用LAG()获取前一日日期，再逐行判断是否为自然日连续（DATEDIFF=1），而非直接求平均；必须先去重、归一化时间字段，再窗口标记，最后分组聚合。
LAG() 计算次日留存的核心逻辑是什么？
次日留存率 = 第二天仍活跃的用户数 ÷ 第一天新增用户数。关键不是“求平均”，而是对每个 user_id 按时间排序后，判断其第二天是否还出现。LAG() 本身不统计，它只帮你把“前一天的登录日期”拉到当前行，从而支持逐行判断是否连续。
必须满足两个前提：
- 数据已按 user_id 分组、按 event_date 升序排列
- 时间字段是 DATE 类型（或可安全比较的格式），避免因时分秒导致 LAG(event_date) 和当前日期差值 ≠ 1
SELECT user_id, event_date, LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_date FROM user_active_log;
为什么直接用 LAG(event_date) = event_date - INTERVAL '1 day' 会漏数据？
PostgreSQL/MySQL 8.0+/Snowflake 中，event_date - INTERVAL '1 day' 是可行的，但 SQLite 不支持 INTERVAL；BigQuery 里得写 DATE_SUB(event_date, INTERVAL 1 DAY)；而 Hive SQL 中甚至要套 date_sub(event_date, 1)。更隐蔽的问题是：如果某用户在同一天有多条记录，LAG() 默认取上一行（可能仍是当天），导致“伪连续”。
稳妥做法是先去重再拉窗：
- 对每个 user_id + event_date 做 DISTINCT 或 GROUP BY
- 再对去重后的结果应用 LAG()
- 最后用 DATEDIFF(event_date, prev_date) = 1 判断（各平台函数名不同，但语义一致）
如何汇总出每日的次日留存率？
不能在窗口函数外直接 GROUP BY，否则 LAG() 会失效。正确顺序是：先算标记，再聚合。
典型两层结构：
- 内层：用 LAG() 标出哪些用户是“次日回归”（is_returned = CASE WHEN DATEDIFF(event_date, prev_date) = 1 THEN 1 ELSE 0 END）
- 外层：按 prev_date（即“第一天”）分组，计算 SUM(is_returned) * 1.0 / COUNT(*)
WITH daily_first AS (
SELECT DISTINCT user_id, event_date FROM user_active_log
),
with_lag AS (
SELECT
user_id,
event_date,
LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_date
FROM daily_first
),
flagged AS (
SELECT
prev_date,
CASE WHEN event_date = DATE_ADD(prev_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END AS is_returned
FROM with_lag
WHERE prev_date IS NOT NULL
)
SELECT
prev_date AS cohort_date,
ROUND(SUM(is_returned) * 1.0 / COUNT(*), 4) AS next_day_retention
FROM flagged
GROUP BY prev_date
ORDER BY prev_date;
容易被忽略的边界情况
常见坑点：
- LAG() 在每个分组第一行返回 NULL，必须加 WHERE prev_date IS NOT NULL 过滤，否则 COUNT(*) 会多算
- 若原始表含测试账号、爬虫 ID，需提前用 WHERE user_id NOT IN (...) 排除，否则留存率被稀释
- 时间字段若为 TIMESTAMP，跨天但未归一化到 DATE，会导致 LAG() 拉出的值带时间戳，和当前行比较时出错（例如 '2024-01-01 23:59:59' 和 '2024-01-02 00:00:01' 差 2 秒，DATEDIFF 却等于 1 —— 看似没问题，但语义已偏离“自然日”）
- BigQuery 中 LAG() 默认返回 NULL，不能设默认值，所以判断逻辑必须容错 prev_date IS NOT NULL
实际跑通的关键，往往卡在时间字段归一化和去重这两步。没做这两步就写 LAG，结果看着像对，其实每天偏差 3%～8%。
