ClickHouse Retention SQL：首日队列与自然日留存

最小可运行测试

WITH events AS (
  SELECT * FROM VALUES(
    'distinct_id String, event String, time DateTime64(3)',
    ('u1','SignUp',     '2026-09-01 09:00:00.000'),
    ('u1','SessionStart','2026-09-02 08:00:00.000'),
    ('u1','SessionStart','2026-09-08 08:00:00.000'),
    ('u2','SignUp',     '2026-09-01 10:00:00.000'),
    ('u2','SessionStart','2026-09-02 11:00:00.000'),
    ('u3','SignUp',     '2026-09-02 10:00:00.000')
  )
), cohorts AS (
  SELECT distinct_id, min(toDate(time)) AS cohort_day
  FROM events WHERE event='SignUp' GROUP BY distinct_id
), activity AS (
  SELECT DISTINCT distinct_id, toDate(time) AS active_day
  FROM events WHERE event='SessionStart'
)
SELECT cohort_day,
  count() AS cohort_size,
  countIf(has(days, 1)) AS day_1_users,
  countIf(has(days, 7)) AS day_7_users,
  round(100 * day_1_users / cohort_size, 2) AS day_1_pct,
  round(100 * day_7_users / cohort_size, 2) AS day_7_pct
FROM (
  SELECT c.distinct_id, c.cohort_day,
    groupUniqArray(dateDiff('day', c.cohort_day, a.active_day)) AS days
  FROM cohorts c LEFT JOIN activity a USING (distinct_id)
  GROUP BY c.distinct_id, c.cohort_day
)
GROUP BY cohort_day ORDER BY cohort_day;

预期 9 月 1 日 cohort 为 2 人，D1 为 2 人（100%），D7 为 1 人（50%）；9 月 2 日 cohort 为 1 人，D1/D7 均为 0。

生产表 SQL

WITH
cohorts AS (
  SELECT distinct_id, min(toDate(time)) AS cohort_day
  FROM sensors.event
  WHERE event = 'SignUp' AND distinct_id != ''
  GROUP BY distinct_id
  HAVING cohort_day BETWEEN '2026-09-01' AND '2026-09-30'
),
activity AS (
  SELECT DISTINCT distinct_id, toDate(time) AS active_day
  FROM sensors.event
  WHERE event = 'SessionStart'
    AND ds BETWEEN '2026-09-02' AND '2026-10-30'
)
SELECT cohort_day,
  count() AS cohort_size,
  countIf(has(active_offsets, 1)) AS d1,
  countIf(has(active_offsets, 7)) AS d7,
  countIf(has(active_offsets, 30)) AS d30,
  round(100 * d1 / nullIf(cohort_size,0), 2) AS d1_pct,
  round(100 * d7 / nullIf(cohort_size,0), 2) AS d7_pct,
  round(100 * d30 / nullIf(cohort_size,0), 2) AS d30_pct
FROM (
  SELECT c.distinct_id, c.cohort_day,
    groupUniqArray(dateDiff('day', c.cohort_day, a.active_day)) AS active_offsets
  FROM cohorts c LEFT JOIN activity a USING (distinct_id)
  GROUP BY c.distinct_id, c.cohort_day
)
GROUP BY cohort_day ORDER BY cohort_day;

容易算错的五件事

• 自然日不是 24 小时：23:59 注册、次日 00:01 活跃属于 D1，即使只过两分钟。若要滚动 24 小时留存，必须用秒级差值定义另一指标。
• 去重：activity 用 DISTINCT identity + day，同日启动十次仍只算一个留存用户。
• 分母：LEFT JOIN 保留未回访用户；改成 INNER JOIN 会丢失零留存用户并虚高比例。
• 身份合并：登录前匿名 ID 和登录后 ID 需要映射表或 signup 关联逻辑。本文不假设两个 ID 自动相同。
• 成熟度：尚未走到 D30 的新 cohort 不应展示为 0%；报表应只输出 cohort_day <= today()-30 的 D30，或标记未成熟。

迟到事件与时区

SensorFlow 的 ds 从事件时间按服务端时区派生。客户端时钟错误和离线补发会改变历史 cohort 或活跃日。建议保留事件时间与接收时间、监控异常偏移，并对最近 7–30 天的聚合进行重算；跨地区产品应明确使用 UTC 还是业务时区。

相关阅读