如何用SQL中的LAG()函数高效计算用户次日留存率？-SQL-PHP中文网

LAG()计算次日留存的核心逻辑是：对每个user_id按event_date升序排列后，用LAG()获取前一日日期，再逐行判断是否为自然日连续（DATEDIFF=1），而非直接求平均；必须先去重、归一化时间字段，再窗口标记，最后分组聚合。

如何用sql中的lag()函数高效计算用户次日留存率？

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%。