分组内留存率必须按分组（如channel、user_id）重新计算每个用户的首次登录日，否则用全局首日会导致分母混入其他组用户、结果失真；正确做法是group by channel, user_id求first_date，再关联次日登录。
分组内留存率不能直接用全局首次登录日计算，必须按分组（如 channel、user_id）重新算每个用户的首日，否则分母混入其他组用户，结果完全失真。
为什么 GROUP BY channel 后直接 COUNT(DISTINCT user_id) 算留存会错
常见错误写法：SELECT channel, COUNT(DISTINCT user_id) AS new_cnt, COUNT(DISTINCT CASE WHEN login_date = DATE_ADD(first_login, INTERVAL 1 DAY) THEN user_id END) AS day1_retained FROM ... GROUP BY channel ——问题出在 first_login 没有按 channel, user_id 重算，而是用了全量表的 MIN(login_date)，导致 A 渠道新用户被误判为 B 渠道的“回访”，分母膨胀、留存率虚高。
根本原因：留存率的“首日”必须是该分组内用户的首次行为日，不是全量用户的首次行为日。漏掉 channel 就等于没分组。
-
PARTITION BY channel, user_id是窗口函数安全做法；若用子查询，GROUP BY channel, user_id是底线 - 如果只
GROUP BY channel，MIN(login_date)会取该渠道所有用户的最早登录日，而非每个用户在该渠道的首次登录日 - 跨渠道账号（如同一
user_id在多个channel登录）会进一步放大偏差
用 CTE 分步实现分组内首日 + 回访匹配（兼容 MySQL/PostgreSQL/StarRocks）
推荐结构清晰、不依赖高级窗口函数的写法，适配多数数仓环境：
WITH daily_login AS (
SELECT DISTINCT user_id, channel, DATE(login_time) AS login_date
FROM user_behavior
WHERE event_type = 'login'
),
first_by_channel AS (
SELECT user_id, channel, MIN(login_date) AS first_date
FROM daily_login
GROUP BY channel, user_id
),
retention_match AS (
SELECT f.channel, f.user_id, f.first_date,
d.login_date AS day1_date
FROM first_by_channel f
LEFT JOIN daily_login d
ON f.user_id = d.user_id
AND d.channel = f.channel
AND d.login_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)
)
SELECT channel,
COUNT(DISTINCT user_id) AS cohort_size,
COUNT(DISTINCT day1_date) AS retained_day1,
ROUND(COUNT(DISTINCT day1_date) * 100.0 / COUNT(DISTINCT user_id), 2) AS retention_day1
FROM retention_match
GROUP BY channel;
关键点：
-
daily_login去重 + 转日期，避免同日多次登录重复计数 -
first_by_channel必须GROUP BY channel, user_id，这是分组留存的锚点 -
LEFT JOIN保证分母完整；用COUNT(DISTINCT day1_date)而非COUNT(DISTINCT d.user_id)，防止某用户次日多登被重复计数
多日留存扩展时 date_add 的陷阱
扩展到 3 日、7 日留存时，不能简单复制 date_add(..., INTERVAL 2 DAY) 就完事——时间粒度不对齐会导致漏算或错算。
例如：用户首日是 '2026-05-10'，3 日留存应查 '2026-05-13'（即第 3 天），但业务常误理解为“间隔 2 天后”。正确对应关系是：
- 次日留存 →
DATE_ADD(first_date, INTERVAL 1 DAY) - 3 日留存 →
DATE_ADD(first_date, INTERVAL 2 DAY)（第 3 天 = 首日 + 2） - 7 日留存 →
DATE_ADD(first_date, INTERVAL 6 DAY)（第 7 天 = 首日 + 6）
更稳妥的做法是用 datediff 或 date_diff 显式判断天数差：
AND DATEDIFF(d.login_date, f.first_date) = 2 -- 对应 3 日留存
注意：datediff 在 MySQL 中是 DATEDIFF(end, start)，而 ClickHouse 是 dateDiff('day', start, end)，函数参数顺序和单位需核对目标引擎。
容易被忽略的性能与口径一致性问题
真实生产中，最常卡住的不是语法，而是这两点：
- 活跃定义未对齐：
event_type = 'login'是否包含埋点异常？是否过滤了测试账号（如user_id LIKE 'test%'）？这些必须在daily_loginCTE 最早一层过滤干净 - 时间字段类型隐患：如果
login_time是datetime但没走索引，DATE(login_time) = ...会导致全表扫描；建议提前建好login_date字段或函数索引 - 分组维度遗漏：比如按
device_type分析留存，却忘了在first_by_channel的GROUP BY和JOIN条件里加上device_type，结果仍是全局首日污染
复杂点在于：分组维度越多，GROUP BY 和 JOIN 的字段组合越容易漏；而一旦漏一个，整个分组留存就不可信——它不会报错，只会静默失真。
