SQL 数据分析 · 指南 · 2026年10月29日 · 12 分钟阅读

用 SQL 做留存分析：次日与 7 日留存、周留存矩阵和滚动留存

同期群留存只回答一个问题：在某个时间段开始使用的人里，后来有多少比例回来了？同期群（cohort）是起点相同的一组用户，比如首次访问、首次购买或注册在同一天或同一周。留存率是同期群中在 N 天或 N 周之后再次活跃的比例。把同期群作为行、时间段作为列排开，就是大家熟悉的三角形留存矩阵。

用 SQL 做留存分析，每个查询都是同样的三步：

1. 把行为日志压缩成每个用户每天一行；
2. 找出每个用户的第一天（即同期群）；
3. 把两者 JOIN 起来，统计每个同期群在每个偏移量上有多少用户活跃。

数字出错往往出在细节上：谁算「新用户」、整数除法怎样舍入、还没发生的时间段该显示什么。本文基于一家教学用航空公司的 App 日志（app_events，2026 年 7 月 1 日至 8 月 11 日，快照时间为 8 月 11 日 18:00）一步步搭建分析。每个查询都在这份数据上运行过，每张表都是真实输出，你可以在 Nomto 的 SQL 练习场里复现每一步。

第一步：每个用户每天一行

这里的用户键是 client_id，代表一次 App 安装或一个浏览器。活跃的定义是「当天有任何事件」：

SELECT DISTINCT client_id, CAST(event_time AS DATE) AS active_day
FROM app_events;

DISTINCT 在这里起了两个作用：一个用户一天有二十个事件，也只算一个活跃日；同一事件被重复上报（这份日志已知的一个问题）也一并消除了。

第二步：同期群就是首个活跃日

WITH client_days AS (
  SELECT DISTINCT client_id, CAST(event_time AS DATE) AS active_day
  FROM app_events
)
SELECT client_id, MIN(active_day) AS first_day
FROM client_days
GROUP BY client_id;

这里的「首个」只是相对于你手上的数据而言。日志从 7 月 1 日开始，所以那天活跃的每个人看起来都像新用户，其中包括已经用了好几年的老用户。任何日志的头几天都会被这样「污染」，不应该拿来当同期群。在这份数据里，每天「新」客户的流入量要过了第一周才稳定下来，所以下面的例子都从 7 月中旬开始划分同期群。

如果你有真实的注册日期或安装日期，就用它。这份数据里会员计划有 joined_at，但很多会员是在机场柜台或网站上加入的，从来没打开过 App，所以就 App 留存而言，首个事件是更合适的起点。

第 N 日留存：次日留存和 7 日留存

经典的第 N 日留存问的是：用户在首日之后的恰好第 N 天有没有活跃？计算方法是把每个活跃日 JOIN 回用户，并为每个用户设置一个 0/1 标记：

WITH client_days AS (
  SELECT DISTINCT client_id, CAST(event_time AS DATE) AS active_day
  FROM app_events
),
first_days AS (
  SELECT client_id, MIN(active_day) AS first_day
  FROM client_days
  GROUP BY client_id
),
flags AS (
  SELECT f.client_id, f.first_day,
    MAX(CASE WHEN cd.active_day = f.first_day + 1 THEN 1 ELSE 0 END) AS d1,
    MAX(CASE WHEN cd.active_day = f.first_day + 7 THEN 1 ELSE 0 END) AS d7,
    MAX(CASE WHEN cd.active_day BETWEEN f.first_day + 1 AND f.first_day + 7
             THEN 1 ELSE 0 END) AS within7
  FROM first_days f
  JOIN client_days cd ON cd.client_id = f.client_id
  GROUP BY f.client_id, f.first_day
)
SELECT first_day, COUNT(*) AS clients,
  ROUND(100.0 * SUM(d1) / COUNT(*), 1) AS d1_pct,
  CASE WHEN first_day + 7 <= DATE '2026-08-10'
       THEN ROUND(100.0 * SUM(d7) / COUNT(*), 1) END AS d7_pct,
  CASE WHEN first_day + 7 <= DATE '2026-08-10'
       THEN ROUND(100.0 * SUM(within7) / COUNT(*), 1) END AS within7_pct
FROM flags
WHERE first_day BETWEEN '2026-07-28' AND '2026-08-04'
GROUP BY first_day
ORDER BY first_day;

这个查询里有几处是刻意这样写的。

每个用户一个 MAX(CASE ...)。 JOIN 之后，每个用户的每个活跃日各占一行。0/1 标记取 MAX，只要任何一行满足条件，结果就是 1。然后在用户层面用 SUM(flag) / COUNT(*) 得到同期群的比例。

用 100.0，不用 100。 在 PostgreSQL 里，整数的 SUM 除以 COUNT(*) 是整数除法，小数部分会被悄悄丢掉。以 100.0 开头，整个表达式就变成了小数运算。

未成熟的单元格用 NULL。 数据中最后一个完整的日子是 8 月 10 日（8 月 11 日在 18:00 被截断）。8 月 4 日首次出现的客户，第 7 天是 8 月 11 日，这一天并不完整。他们的 D7 标记是 0，不是因为没回来，而是因为这一天还没被完整观察到。不带 ELSE 的 CASE 会对这些同期群返回 NULL，这才是诚实的值；如果写成 0，看起来就像留存崩盘了。

三种定义，三个不同的数字。 次日留存大约 7%–9%，7 日留存不到 2%，但每个同期群都有大约 18%–20% 的人在第一周内至少回来过一次（within7）。这三个数字都没有错，它们衡量的是不同的东西。对于订机票这种人们不会天天使用的产品，精确到某一天的留存太严格，噪音也大；「N 天内回访过」（有时称为区间留存，或带固定期限的无界留存）往往更有用。无论用哪个数字，都要说明它采用的是哪种定义。

滚动留存需要固定期限

滚动留存统计的是在第 N 天或之后回来的用户。如果没有上限，它就取决于同期群开始后过了多长时间：早期的同期群有好几周的时间回来，最近的只有几天。结果看起来像是新同期群的留存更差，其实它们只是更「年轻」。上面的 within7 列把期限固定为七天，并且只显示七天都已完整的同期群，从而避免了这个问题。

周留存矩阵

在仪表盘上，按周划分的同期群比按天的更容易读。同期群是首日所在的自然周，列是用户在多少个自然周之后活跃：

WITH client_days AS (
  SELECT DISTINCT client_id, CAST(event_time AS DATE) AS active_day
  FROM app_events
),
first_days AS (
  SELECT client_id, MIN(active_day) AS first_day
  FROM client_days
  GROUP BY client_id
),
weeks AS (
  SELECT f.client_id,
    CAST(date_trunc('week', f.first_day) AS DATE) AS cohort_week,
    CAST((CAST(date_trunc('week', cd.active_day) AS DATE)
        - CAST(date_trunc('week', f.first_day) AS DATE)) / 7 AS INTEGER) AS week_n
  FROM first_days f
  JOIN client_days cd ON cd.client_id = f.client_id
),
cells AS (
  SELECT cohort_week, week_n, COUNT(DISTINCT client_id) AS active
  FROM weeks
  GROUP BY cohort_week, week_n
),
pct AS (
  SELECT c.cohort_week, c.week_n, s.active AS clients,
    CASE WHEN c.cohort_week + 7 * c.week_n + 6 <= DATE '2026-08-09'
         THEN ROUND(100.0 * c.active / s.active, 1) END AS retention
  FROM cells c
  JOIN cells s ON s.cohort_week = c.cohort_week AND s.week_n = 0
)
SELECT cohort_week, clients,
  MAX(CASE WHEN week_n = 1 THEN retention END) AS w1,
  MAX(CASE WHEN week_n = 2 THEN retention END) AS w2,
  MAX(CASE WHEN week_n = 3 THEN retention END) AS w3,
  MAX(CASE WHEN week_n = 4 THEN retention END) AS w4
FROM pct
WHERE cohort_week BETWEEN DATE '2026-07-13' AND DATE '2026-08-03'
GROUP BY cohort_week, clients
ORDER BY cohort_week;

各部分是怎样配合的：

• date_trunc('week', ...) 返回所在周的星期一。两个星期一之差除以 7 就是周序号：同期群自己那周是 0，下一周是 1，依此类推。
• 同期群规模是第 0 周活跃的客户数，按定义就是该同期群的全部客户。把 cells 和自己按 week_n = 0 做 JOIN，就能把这个规模放到每个单元格旁边。
• 从长格式（同期群、周、值）转成列，用的是条件聚合：MAX(CASE WHEN week_n = 1 THEN retention END)。
• 成熟度规则：同期群的第 N 周结束于 cohort_week + 7 * N + 6。只有在 8 月 9 日（星期日，最后一个完整周的最后一天）之前结束的周才给出数值。没有这条规则的话，7 月 20 日同期群的第 3 周会显示 3.3%，但那只是 8 月 10 日那一周被观察到的两天，并不是真的下跌。

矩阵可以横着读（一个同期群怎样衰减），也可以竖着读（第 1 周留存在各同期群之间是变好还是变差）。这里第 1 周留存从 13.2% 略降到 11.9%；只有三个数据点，这值得继续观察，还不能下结论。周留存也比 7 日留存高得多，因为「第 1 周的任意一天」比「恰好第 7 天」的窗口宽得多。

用购买而不是访问来衡量留存

留存不一定指打开 App。对航空公司来说，「又订了一次票」更接近收入。会员是唯一能识别回头客的地方（ticket_members 把机票和 member_id 关联起来），所以这里的同期群是会员首次订票所在的周，事件是 14 天内的再次订票：

WITH member_bookings AS (
  SELECT DISTINCT tm.member_id, b.book_ref, b.book_date
  FROM ticket_members tm
  JOIN tickets t ON t.ticket_no = tm.ticket_no
  JOIN bookings b ON b.book_ref = t.book_ref
),
firsts AS (
  SELECT member_id, MIN(book_date) AS first_booking
  FROM member_bookings
  GROUP BY member_id
),
flags AS (
  SELECT f.member_id,
    CAST(date_trunc('week', f.first_booking) AS DATE) AS cohort_week,
    MAX(CASE WHEN mb.book_date > f.first_booking
              AND mb.book_date <= f.first_booking + INTERVAL '14 days'
             THEN 1 ELSE 0 END) AS rebooked_14d
  FROM firsts f
  JOIN member_bookings mb ON mb.member_id = f.member_id
  GROUP BY f.member_id, f.first_booking
)
SELECT cohort_week, COUNT(*) AS members,
  ROUND(100.0 * SUM(rebooked_14d) / COUNT(*), 1) AS rebooked_14d_pct
FROM flags
WHERE cohort_week BETWEEN DATE '2026-06-29' AND DATE '2026-07-20'
GROUP BY cohort_week
ORDER BY cohort_week;

第一个 CTE 里的 DISTINCT 很关键：一笔订单里如果有三张会员机票，不去重的话这笔订单会出现三次。显示的最后一个同期群是 7 月 20 日那周，因为它最晚的首次订票（7 月 26 日）往后 14 天是 8 月 9 日，仍在快照之前。

6 月 29 日同期群以 27.1% 格外突出，原因和 App 日志一样：订单数据从 6 月 17 日开始，所以最早的那批「首次订票」里混进了在数据开始之前就订过票的常旅客。常旅客复购更频繁，于是早期同期群被抬高了。在为某个同期群高兴或担心之前，先看看它是不是落在数据的边缘。

SQL 留存分析的常见错误

• 把日志的头几天当作同期群。 那时每个人看起来都是新用户。跳过这几天，或者使用真实的注册日期。
• 数事件而不是数用户。 留存是用户的比例；先压缩到「用户-日」粒度，再数不同的用户。
• 整数除法。 在 PostgreSQL 里，100 * SUM(flag) / COUNT(*) 会向下取整成整数。要用 100.0。
• 还没发生的时间段填了 0。 未成熟的单元格必须是 NULL，否则最新的同期群看起来像一场灾难。
• 没有期限的滚动留存。 老同期群有更多时间回来，所以看起来总是更好。
• 混用定义。 7 日留存、「7 天内」和「第 1 周」是三个不同的数字；要标明你展示的是哪一个。
• 设备不等于人。 client_id 代表一次安装或一个浏览器 cookie；一个人有手机和笔记本电脑就是两个客户，清除 cookie 又会产生一个「新」客户。如果问题是关于人的，就用登录后的用户 ID。

本文所有表格都来自 Nomto SQL 课程的数据集。在 SQL 练习场里，你可以把期限从 7 天改成 14 天，把同期群从按周改成按天，或者只针对一个平台重建矩阵，自己核对数字。留存查询离不开窗口和条件聚合，如果这部分还不熟，可以先看 SQL 窗口函数 和 GROUP BY 和 HAVING 的区别；面试中常见的 30 天复购题，见 SQL 面试题 第 20 题。

常见问题

什么是同期群留存分析？

它按用户开始使用的时间把用户分组（同期群），然后衡量每组在 N 天或 N 周之后再次活跃的比例。比较不同的同期群，就能看出产品留住新用户的能力是在变好还是变差。

怎样用 SQL 计算 7 日留存？

找出每个用户的首个活跃日，JOIN 上他们所有的活跃日，标记在 first_day + 7 当天有活动的用户，再用被标记的用户数除以同期群规模。对于第 7 天还没有完整观察到的同期群，显示 NULL。

经典留存和滚动留存有什么区别？

经典（第 N 日）留存要求恰好在第 N 天有活动。滚动留存则统计在第 N 天或之后任何时间有活动的人。滚动留存总是更高，而且取决于你观察同期群的时间长短，所以比较同期群时要固定一个上限。

怎样用 SQL 构建留存矩阵？

为每个「用户-日」算出同期群周和周偏移量，按（同期群，偏移量）统计不同用户数，除以偏移量为 0 时的规模，再用 MAX(CASE WHEN week_n = k THEN ... END) 把偏移量转成列。

为什么最新的同期群留存率特别低？

通常是因为它们后面的时间段还没有被完整观察到。加一个成熟度条件：凡是时间段结束于数据最后一个完整日之后的单元格，都返回 NULL。

其他语言： English