留存率矩阵是横轴为用户首次活跃日期、纵轴为后续第N天是否回访的二维交叉表；单个GROUP BY只能按一个维度聚合，无法同时表达“从哪天来”和“在哪些天留下”两个时间维度，必须配合条件聚合等方法实现。
什么是留存率矩阵，为什么不能只用单个 GROUP BY
留存率矩阵本质是二维交叉表：横轴是用户首次活跃日期（或注册日期），纵轴是后续第 N 天是否回访。单个 GROUP BY 只能按一个维度聚合，无法同时表达“从哪天来”和“在哪些天留下”两个时间维度——必须配合窗口函数、自连接或条件聚合才能展开成矩阵结构。
用条件聚合 + GROUP BY 构建宽表矩阵（最实用）
这是生产环境最稳定的做法：先算出每个用户的首日（first_date）和所有活跃日期，再用 MAX(CASE WHEN ...) 按天展开是否留存，最后用 GROUP BY first_date 汇总比例。
常见错误现象：GROUP BY 漏掉 first_date 导致全量聚合；CASE 中未限定日期差范围导致列爆炸；没用 MAX() 或 BOOL_OR() 直接写 CASE 出现多行。
- 确保基础表含
user_id、event_date，且已去重（同一用户同日多次行为只计 1 次） - 用子查询或 CTE 先计算每个用户的
first_date，避免在主查询中重复计算 -
CASE WHEN event_date = first_date + INTERVAL '1 day' THEN 1 ELSE 0 END判断次日留存，注意日期类型兼容性（PostgreSQL 用INTERVAL，MySQL 用DATE_ADD(first_date, INTERVAL 1 DAY)） - 每列用
AVG()而非COUNT()：因为AVG(1/0)自动处理分母为总用户数，比手动除更安全
SELECT
first_date,
AVG(CASE WHEN event_date = first_date THEN 1.0 ELSE 0.0 END) AS "D0",
AVG(CASE WHEN event_date = first_date + INTERVAL '1 day' THEN 1.0 ELSE 0.0 END) AS "D1",
AVG(CASE WHEN event_date = first_date + INTERVAL '7 day' THEN 1.0 ELSE 0.0 END) AS "D7"
FROM (
SELECT user_id, event_date,
MIN(event_date) OVER (PARTITION BY user_id) AS first_date
FROM user_activity
) t
GROUP BY first_date
ORDER BY first_date;
GROUP BY 配合 LATERAL / JOIN 实现动态天数（适合需要 30+ 列的场景）
当要输出 D0–D30 留存时，手写 31 个 CASE 易错且难维护。可用生成序列（如 PostgreSQL 的 generate_series）与用户首日做 CROSS JOIN，再关联活跃事实表判断是否存在——但必须加 GROUP BY first_date, day_offset 再 pivot，否则无法还原矩阵形状。
性能影响明显：generate_series(0,30) 会让中间结果膨胀 31 倍；MySQL 不支持该函数，需用递归 CTE 或临时数字表替代。
- 务必对
user_activity表的(user_id, event_date)建联合索引，否则JOIN性能骤降 - 如果只要看「某几天」而非连续天数，优先用条件聚合，别为了“灵活”引入 JOIN
- PostgreSQL 中
LATERAL可替代部分CROSS JOIN场景，减少无效组合生成
容易被忽略的细节：去重逻辑和时间粒度对结果的影响
留存率不是简单“有无行为”，而是“是否在目标日存在有效行为”。很多团队漏掉这步：用户当天多次登录，只应计 1 次；跨午夜的行为（如 23:59 和 00:01）若按小时切分，可能被误判为两天活跃。
- 原始数据必须先按
(user_id, DATE(event_date))去重，不能依赖DISTINCT user_id, event_date——因为event_date可能含时分秒 - 统一使用
DATE(event_date)或CAST(event_date AS DATE)，避免因时区或精度导致同日变两日 - 首日定义要明确：是注册时间？首次启动时间？还是首次产生关键事件时间？这个口径必须贯穿整个计算链路
矩阵真正难的不是 SQL 写法，而是把业务定义（比如“活跃”指什么、“留存”是否排除测试账号）无损翻译成可执行的过滤条件和分组键。写完记得用小样本人工核对几行，比调参数重要得多。
