留存率指某天新增用户在之后第N天仍活跃的比例，如次日留存率=（第0天新增且第1天登录用户数）/（第0天新增用户总数）；转化率指完成目标步骤用户数与起始步骤用户数之比，需通过窗口函数、条件聚合及行为顺序识别实现，并注意去重、时间对齐与口径统一。
留存率和转化率是分析用户行为的核心指标，SQL 可以高效完成这类计算，关键是明确时间维度、用户标识和行为定义。
留存率：看老用户是否回来
留存率通常指“某天新增的用户，在之后第 N 天仍活跃的比例”。例如次日留存率 =（第 0 天新增且第 1 天也登录的用户数）/（第 0 天新增用户总数）。
常用做法是先提取每日新增用户（按首次行为日期标记），再关联后续日期的行为记录：
- 用窗口函数 MIN(event_date) OVER (
PARTITION BY user_id) 找出每个用户的首日
- 将首日用户表与行为表自连接，限定时间差为 1 天、7 天等，统计回访人数
- 用聚合 + CASE WHEN 或 LEFT JOIN + COUNT(CASE WHEN ...) 计算分母和分子
转化率：看漏斗中用户走了多远
转化率 = 完成目标步骤的用户数 / 进入起始步骤的用户数。比如“浏览商品 → 加入购物车 → 下单”，要算从浏览到下单的整体转化率，或各环节间转化率。
关键在识别同一用户在不同步骤中的有序行为：
- 用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) 标记行为顺序
- 用条件聚合（如 COUNT(DISTINCT CASE WHEN event_type='view' THEN user_id END)）分别统计各步骤用户数
- 若需严格路径（如必须先 view 再 cart），可用自连接或 LAG/LEAD 获取前序行为，过滤有效链路
注意细节：去重、时间对齐与口径统一
很多结果偏差来自细节处理不当：
- 用户去重必须用 DISTINCT user_id，不能直接 COUNT(*)，否则重复行为会虚高分母
- 日期比较建议用 DATE(event_time) 统一格式，避免时区或时间戳精度干扰
- 留存计算中，“新增”和“回访”必须基于同一行为类型（如都用 login，而非新增看 register、回访看 page_view）
- 转化漏斗中，建议用 user_id + session_id 组合判断是否为同一次会话内的连续动作（可选增强）
简单示例：次日留存 SQL 框架
假设表 events 含 user_id, event_date, event_type：
WITH first_day AS (
SELECT user_id, MIN(event_date) AS first_date
FROM events
GROUP BY user_id
),
retained AS (
SELECT f.user_id
FROM first_day f
INNER JOIN events e
ON f.user_id = e.user_id
AND DATE(e.event_date) = DATE_ADD(f.first_date, INTERVAL 1 DAY)
)
SELECT
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT r.user_id) AS retained_users,
ROUND(COUNT(DISTINCT r.user_id) * 100.0 / COUNT(DISTINCT f.user_id), 2) AS retention_rate
FROM first_day f
LEFT JOIN retained r ON f.user_id = r.user_id;
