做游戏数据分析的兄弟应该都被留存率折磨过。老板一句“帮我看看不同胜率的活跃玩家的次日、3日、7日留存率”听起来好像就是一条SQL的事真上手写才知道坑不少胜率到底怎么算活跃怎么定义3日留存是第3天回流还是3天内任意活跃口径一旦乱了数就对不上最后背锅的还是写SQL的人。这篇文章我把整套逻辑完整拆开从业务口径、表结构设计到MySQL里的完整SQL实现、性能优化、常见坑点一次性讲透。按我这套思路写你不仅能跑出结果还能跟老板讲清楚每个数字是怎么来的经得起追问。1. 留存分析到底在算什么先明确口径再动手1.1 一条SQL背后隐藏了三层业务定义写SQL之前最忌讳的事就是上来就写JOIN。留存率这个指标看着简单其实每个词都有定义空间你理解的和业务方理解的经常不是一回事。先拆开这个标题——“不同胜率的活跃玩家的次日、3日、7日留存率”这里面至少有三个待定义的关键词胜率、活跃玩家、留存率。胜率是哪个周期内的胜率是统计当天之前的30天还是7天还是玩家整个生命周期这个直接决定了玩家被分到哪个胜率档位进而影响每档留存率。活跃玩家是当天有登录行为还是有过对局行为两者的用户规模能差出不少。更关键的是这里的留存率到底该按“存量活跃玩家”算还是按“新增玩家”算这是两种完全不同的指标前者是活跃留存后者是新增留存。留存率的分子分母怎么定次日留存是D1那天还在活跃的人数除以D天活跃人数这个没有歧义。但3日留存和7日留存到底是“第3天单日活跃”还是“D1到D3期间任意一天活跃过”不同团队习惯完全不一样。做数据分析这行口径一致比SQL写得漂亮重要一百倍。我的习惯是把口径定义写成一个脚本化的注释块贴到SQL文件头部这样三个月后回来看还能想起来当时在算什么。1.2 “胜率”是天然的玩家分层维度为什么要按胜率来分留存因为胜率直接反映了玩家的水平和对游戏的掌控力而不同水平玩家的留存动因是不一样的。高胜率玩家留存好可能是因为他在这个游戏里能获得成就感和段位荣誉感这类玩家是游戏的核心战斗力低胜率玩家如果留存也不差说明游戏对新手和弱势玩家足够友好匹配机制在起作用最危险的是中胜率玩家大量流失说明游戏在中段体验上出了问题。所以这个分析的实际业务价值在于通过胜率分层定位留存的薄弱人群反过来指导匹配机制、新手保护和数值平衡的调整。这不是一个单纯取数的需求而是一个需要你输出判断的专项分析。2. 活跃玩家与胜率怎么算表结构设计和口径选择2.1 用一张每日活跃快照表别把留存SQL写死在流水表上留存计算最理想的输入不是明细流水而是“每天一个用户一行”的活跃快照表。原因很简单留存率的本质是“某天活跃过的人后续某天是否还活跃”这是一个用户与日期的笛卡尔积判断用快照表做起来清爽很多。我常用的建表语句长这样CREATE TABLE daily_active ( stat_date DATE NOT NULL COMMENT 统计日期, user_id BIGINT NOT NULL COMMENT 玩家ID, login_cnt INT DEFAULT 1 COMMENT 当日登录次数用于参考, PRIMARY KEY (stat_date, user_id), KEY idx_user_date (user_id, stat_date) ) ENGINEInnoDB COMMENT每日活跃玩家快照表;这张表的核心设计原则是“每天每用户一行”。不管用户当天登录了3次还是打了10局只保留一行。这样做的好处太明显了算留存的时候不需要考虑去重直接JOIN就行。注意主键设计成(stat_date, user_id)这个顺序是故意为之。因为留存计算最常见的查询是按日期切片取用户集合主键左前缀能直接命中。后面算“某个用户在哪些天活跃”时走idx_user_date辅助索引。两张索引覆盖了两种高频查询方向。2.2 胜率从哪来对局流水与玩家维表胜率的数据来源是对局流水表。每条对局流水记录的是一局游戏中某个玩家的胜负结果。以MOBA或竞技类游戏为例一张典型对战流水表可能是这样的CREATE TABLE battle_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, battle_id VARCHAR(64) NOT NULL COMMENT 对局ID通常一局一个ID, user_id BIGINT NOT NULL COMMENT 玩家ID, battle_date DATE NOT NULL COMMENT 对局日期, result ENUM(win, lose, draw) NOT NULL COMMENT 对局结果, killer_cnt INT DEFAULT 0 COMMENT 击杀数按需扩展, damage_hp BIGINT DEFAULT 0 COMMENT 伤害量按需扩展, KEY idx_user_date (user_id, battle_date), KEY idx_date_user (battle_date, user_id), KEY idx_battle (battle_id) ) ENGINEInnoDB COMMENT对局流水表;胜率的定义就是统计周期内用户胜场数除以总对局数。这里有两个细节必须注意。第一个是同一局对战如果红蓝双方各产生一条记录那么一个玩家打一局就会在表里留下两行——一行是他在蓝队的视角一行是对方在红队的视角。直接对user_id分组再数次数会把对战局数翻倍胜率自然也是错的。正确做法是先用battle_id和user_id去重或者在建表时就确保“每局每玩家只写一行”。第二个细节是对局表的粒度。如果业务上希望支持复杂分析表中可以保留battle_id和user_id的联合唯一键避免同一玩家同一局重复落库。数据质量问题的排查成本远高于建表时多写一个约束的成本。2.3 胜率统计周期和最低场次阈值胜率应该放在多大的时间窗口里统计这是容易被忽略但特别影响结果的一步。如果窗口太短比如只看基期当天那一个玩家如果当天只打了1局而且赢了胜率就是100%这个样本没有任何统计意义如果窗口太长比如整个生命周期那老玩家的胜率会被很久远的早期数据拖住反映不了当前状态。我一般取基期前30天作为胜率统计窗口同时加一个最低场次阈值比如HAVING 对局数 10。低于10局的玩家样本量太小胜率波动极大分桶之后会严重污染该档位的留存率。要不要用10还是20可以根据游戏的实际对局频率调整核心原则是保证每档玩家有足够的样本量支撑结论。最终口径可以定义成活跃玩家 基期当天有活跃记录的用户胜率 基期日前30天内的胜场数/对局数留存 基期日活跃用户在后续某个观察点的活跃情况。这个口径要写清楚后面所有SQL都围绕它展开。3. 完整SQL实现从胜率分层到三档留存一键输出3.1 第一步用WITH语句计算胜率并分桶MySQL 8.0及以上版本支持CTE公用表表达式用WITH语句写留存计算比嵌套子查询清晰得多。下面我给出一个可直接落地的完整方案。假设要分析基期日2025-02-01的活跃玩家留存胜率统计窗口取2025-01-01到2025-01-31胜率按30%和60%分成三档WITH win_rate_stat AS ( SELECT user_id, COUNT(*) AS battle_cnt, SUM(CASE WHEN result win THEN 1 ELSE 0 END) AS win_cnt, ROUND( SUM(CASE WHEN result win THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS win_rate FROM battle_log WHERE battle_date BETWEEN 2025-01-01 AND 2025-01-31 GROUP BY user_id HAVING COUNT(*) 10 ), player_segment AS ( SELECT user_id, win_rate, CASE WHEN win_rate 60 THEN 高胜率(60%以上) WHEN win_rate 30 THEN 中胜率(30%~60%) ELSE 低胜率(30%以下) END AS segment FROM win_rate_stat ) SELECT * FROM player_segment ORDER BY win_rate DESC;分段阈值没有标准答案。如果整体玩家胜率普遍偏低用60%做高胜率分界线可能一档都没几个人。建议先看胜率分布再定阈值比如用四分位数来切或者按高于均值1个标准差、均值上下、低于均值1个标准差来切。别把SQL写死了分层逻辑应当是一眼能看懂、方便调整的。3.2 第二步基期活跃用户集合与N日活跃集合基期活跃用户集合很简单就是daily_active表里stat_date 2025-02-01的用户ID。但别大意先DISTINCT一下防止快照表里偶尔有冗余数据。N日活跃集合也是同理。比较规范的写法是给每一天单独建一个CTE-- 基期活跃用户 base_active AS ( SELECT DISTINCT user_id FROM daily_active WHERE stat_date 2025-02-01 ), -- 次日(2025-02-02)活跃用户 d1_active AS ( SELECT DISTINCT user_id FROM daily_active WHERE stat_date DATE_ADD(2025-02-01, INTERVAL 1 DAY) ), -- 第3天(2025-02-04)活跃用户按严格时点口径 d3_active AS ( SELECT DISTINCT user_id FROM daily_active WHERE stat_date DATE_ADD(2025-02-01, INTERVAL 2 DAY) ), -- 第7天(2025-02-08)活跃用户按严格时点口径 d7_active AS ( SELECT DISTINCT user_id FROM daily_active WHERE stat_date DATE_ADD(2025-02-01, INTERVAL 6 DAY) )注意这里我刻意把DATE_ADD写在右边而不是直接写死日期。好处是后续如果要批量跑多个基期日SQL可以作为模板循环复用不用每个日期都手动改。关于3日留存的严格口径这里用的是第3天单日D2。如果你的业务方想要的是“D1到D3期间任意活跃”那留存用户集合需要把1到3天的活跃用户做UNION。这个我会在第五部分对照说明。3.3 第三步LEFT JOIN匹配留存用户匹配环节的关键是使用LEFT JOIN而不是INNER JOIN这样才能保留基期活跃但后续没有回流的用户否则分母直接变小留存率会整体虚高。同时要防止JOIN造成的数据膨胀。如果daily_active表有重复记录JOIN上去会生成多行直接COUNT会把一个用户数好几遍。保险做法是结果里统一用COUNT(DISTINCT user_id)。JOIN匹配的完整SQL如下SELECT ps.segment, COUNT(DISTINCT ba.user_id) AS base_users, COUNT(DISTINCT CASE WHEN d1.user_id IS NOT NULL THEN ba.user_id END) AS d1_users, COUNT(DISTINCT CASE WHEN d3.user_id IS NOT NULL THEN ba.user_id END) AS d3_users, COUNT(DISTINCT CASE WHEN d7.user_id IS NOT NULL THEN ba.user_id END) AS d7_users, ROUND( COUNT(DISTINCT CASE WHEN d1.user_id IS NOT NULL THEN ba.user_id END) * 100.0 / COUNT(DISTINCT ba.user_id), 2 ) AS d1_retention_rate, ROUND( COUNT(DISTINCT CASE WHEN d3.user_id IS NOT NULL THEN ba.user_id END) * 100.0 / COUNT(DISTINCT ba.user_id), 2 ) AS d3_retention_rate, ROUND( COUNT(DISTINCT CASE WHEN d7.user_id IS NOT NULL THEN ba.user_id END) * 100.0 / COUNT(DISTINCT ba.user_id), 2 ) AS d7_retention_rate FROM base_active ba JOIN player_segment ps ON ba.user_id ps.user_id LEFT JOIN d1_active d1 ON ba.user_id d1.user_id LEFT JOIN d3_active d3 ON ba.user_id d3.user_id LEFT JOIN d7_active d7 ON ba.user_id d7.user_id GROUP BY ps.segment ORDER BY ps.segment;这段SQL之所以把判断写在CASE WHEN里而不是直接COUNT(d1.user_id)是因为COUNT(字段)会忽略NULL其实直接写COUNT(d1.user_id)也能得到同样的结果。但我习惯用CASE WHEN写语义更明确后续如果想加更复杂的筛选条件扩展起来顺手。JOIN player_segment用的是内连接意思是基期活跃但近30天对局不足10局的玩家不会进入统计。这样做的原因是给这个人群单列一档会导致档位胜率定义混乱不如直接过滤并单独查看。3.4 一步到位的完整SQL脚本把上面几步拼起来就是一份可以直接拿去跑的完整脚本WITH win_rate_stat AS ( SELECT user_id, COUNT(*) AS battle_cnt, SUM(CASE WHEN result win THEN 1 ELSE 0 END) AS win_cnt, ROUND( SUM(CASE WHEN result win THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2 ) AS win_rate FROM battle_log WHERE battle_date BETWEEN 2025-01-01 AND 2025-01-31 GROUP BY user_id HAVING COUNT(*) 10 ), player_segment AS ( SELECT user_id, win_rate, CASE WHEN win_rate 60 THEN 高胜率(60%以上) WHEN win_rate 30 THEN 中胜率(30%~60%) ELSE 低胜率(30%以下) END AS segment FROM win_rate_stat ), base_active AS ( SELECT DISTINCT user_id FROM daily_active WHERE stat_date 2025-02-01 ), d1_active AS ( SELECT DISTINCT user_id FROM daily_active WHERE stat_date DATE_ADD(2025-02-01, INTERVAL 1 DAY) ), d3_active AS ( SELECT DISTINCT user_id FROM daily_active WHERE stat_date DATE_ADD(2025-02-01, INTERVAL 2 DAY) ), d7_active AS ( SELECT DISTINCT user_id FROM daily_active WHERE stat_date DATE_ADD(2025-02-01, INTERVAL 6 DAY) ) SELECT ps.segment, COUNT(DISTINCT ba.user_id) AS base_users, COUNT(DISTINCT d1.user_id) AS d1_users, COUNT(DISTINCT d3.user_id) AS d3_users, COUNT(DISTINCT d7.user_id) AS d7_users, ROUND(COUNT(DISTINCT d1.user_id) * 100.0 / COUNT(DISTINCT ba.user_id), 2) AS d1_retention_rate, ROUND(COUNT(DISTINCT d3.user_id) * 100.0 / COUNT(DISTINCT ba.user_id), 2) AS d3_retention_rate, ROUND(COUNT(DISTINCT d7.user_id) * 100.0 / COUNT(DISTINCT ba.user_id), 2) AS d7_retention_rate FROM base_active ba JOIN player_segment ps ON ba.user_id ps.user_id LEFT JOIN d1_active d1 ON ba.user_id d1.user_id LEFT JOIN d3_active d3 ON ba.user_id d3.user_id LEFT JOIN d7_active d7 ON ba.user_id d7.user_id GROUP BY ps.segment ORDER BY ps.segment;跑出来的结果是一个三行的分组统计表每行代表一个胜率档位包含基期人数和三个留存率。这一步的输出已经可以直接做成图表给业务方看了。4. 大表场景下的性能优化别让留存SQL拖垮线上库4.1 联合索引是第一步但别只会建索引留存计算在百万级用户、千万级流水下如果索引设计不合理一次查询能把数据库跑满。前面建表时已经埋了伏笔daily_active表主键是(stat_date, user_id)这就是为“按日期取全量用户”这个查询服务的。当你执行WHERE stat_date 2025-02-01时InnoDB通过主键直接定位到该日期的数据页速度非常快。battle_log表要同时服务胜率计算和后续可能的时间范围分析所以索引要建两个方向(user_id, battle_date)用于按用户聚合(battle_date, user_id)用于按日期筛选。另一个容易忽略的点是覆盖索引。如果查询只需要读user_id和result两个字段可以把索引建得更宽比如(battle_date, user_id, result)这样InnoDB在索引扫描阶段就拿到了全部所需字段不需要回表性能提升非常明显。4.2 用一次扫描替代多次LEFT JOIN前面完整SQL里JOIN了三个日期的活跃子集每个子集都要独立扫一遍daily_active表。如果只跑一天的数据还好一旦要连续跑30天的留存这种写法会产生90次子查询扫描性能很难看。更优的做法是只扫一次“基期后7天”的活跃表按下一次GROUP BY把每个用户在后续7天内的每日活跃情况压成一行。MySQL 8.0的CTE写法如下follow_flag AS ( SELECT fa.user_id, MAX(CASE WHEN fa.day_diff 1 THEN 1 ELSE 0 END) AS d1_flag, MAX(CASE WHEN fa.day_diff 2 THEN 1 ELSE 0 END) AS d3_flag, MAX(CASE WHEN fa.day_diff 6 THEN 1 ELSE 0 END) AS d7_flag FROM ( SELECT user_id, DATEDIFF(stat_date, 2025-02-01) AS day_diff FROM daily_active WHERE stat_date BETWEEN 2025-02-02 AND DATE_ADD(2025-02-01, INTERVAL 7 DAY) ) fa GROUP BY fa.user_id )这样后续匹配只需要LEFT JOIN follow_flag一次。MAX(CASE WHEN ...)的作用是判断该用户在对应偏移日期是否有活跃记录——只要出现过1就说明活跃过。对于按“区间内任意活跃”口径计算的需求只需要调整day_diff的范围判断比如BETWEEN 1 AND 3就是D1到D3内任意活跃。一次扫描同时支持多种口径非常划算。4.3 多基期批量计算的通用化模板实际业务中通常不会只跑一天而是要连续看近30天或者90天的留存趋势。这种情况下把SQL包一层日期参数用存储过程或者Python脚本循环调用比手动改日期靠谱得多。用Python调MySQL的伪代码思路是这样先写一个带占位符的SQL模板把基期日期、胜率统计起止日期都作为参数传进去循环执行并汇总结果。这样既避免在数据库里维护复杂存储过程也方便后续把结果直接写入报表表。import pymysql base_dates [f2025-02-{d:02d} for d in range(1, 29)] for base_date in base_dates: start_date f{(pd.to_datetime(base_date) - pd.Timedelta(days30)).date()} # 把start_date, base_date 替换进SQL模板 # 执行查询并把结果存入结果表批量跑的时候建议给SQL加一个SET SESSION group_concat_max_len之类的保障参数不过这里不涉及字符串聚合主要关注的是连接池和超时设置。大批量循环查询时尽量用只读账号连接从库避免给线上业务库带来压力。5. 常见问题与排查技巧实录留存SQL翻车现场5.1 留存率算出来超过100%多半是重复记录惹的祸做留存最经典的事故就是留存率超过100%。从数学上说不该发生除非数据有问题。最常见的原因就是daily_active表里同一个用户在同一天出现了多次。如果基期用户集合没做DISTINCT分母会被虚增或保持正常而留存用户JOIN上去后会一行变多行COUNT直接翻倍。尤其很多团队的活跃数据是从埋点日志ETL出来的一次启动可能上报好几条数据去重逻辑没做干净就会出问题。遇到留存率大于100%第一件事先查重SELECT stat_date, COUNT(*) AS total_rows, COUNT(DISTINCT user_id) AS distinct_users FROM daily_active GROUP BY stat_date HAVING total_rows distinct_users;查出来有差异就说明快照表本身有重复。修复方式要么是ETL层做去重要么在SQL里统一用COUNT(DISTINCT user_id)两条路至少要保一条。5.2 胜率是NULL或者0先看JOIN条件和数据过滤条件胜率算出来全是0或者NULL占比很高通常是两种情况。第一种是battle_log表里result字段的取值跟你SQL里写的不一样。比如有的团队用victory而不是win有的用数字1表示胜利直接SUM(CASE WHEN result win THEN 1 ELSE 0 END)算出来的永远是0。我的排查习惯是先看一眼字段实际的枚举值分布SELECT result, COUNT(*) FROM battle_log GROUP BY result;第二种情况是玩家ID关联不上。daily_active.user_id和battle_log.user_id如果来自不同的埋点体系比如一个是自有账号ID一个是设备ID或者渠道ID那JOIN的结果会大范围为空。这种问题不是SQL能解决的需要从数据源头统一ID映射。5.3 同一局对战产生两条流水胜率被悄悄抬高前面提过如果对局流水表的设计是“一局对战双方各写一行”那一个玩家打一局就会在表里留下两条记录两条记录都属于他本人。直接按user_id分组再数等于把他打的每一局都算了两遍胜率不会变——因为分子分母同时翻倍总胜率数值倒是碰巧一样——但分桶之后整体对局数会虚高HAVING COUNT(*) 10这个阈值也会失真。排查方法是检查同一个battle_id下同一个user_id是否存在多条记录SELECT battle_id, user_id, COUNT(*) AS cnt FROM battle_log GROUP BY battle_id, user_id HAVING cnt 1如果确实存在要么在ETL层去重要么在不改变胜率含义的前提下用COUNT(DISTINCT battle_id)来计算总局数。我建议能去重则去重靠SQL绕容易埋雷。5.4 留存口径不一致业务方跟你对不上数这是最憋屈的一类问题。SQL没写错数据也对但和业务方拍的数对不上。十有八九是口径差异。按我前面的设计3日留存是D2严格时点7日留存是D6严格时点。如果业务方默认“7日留存”是“D1到D7内任意一天活跃过”那你的分子比他小自然对不上。这时候不要争谁对谁错应该把两种口径都算出来并标注清楚。口径对照表指标严格时点口径本文默认区间口径次日留存D1当日活跃D1当日活跃3日留存D2当日活跃D1至D3任意活跃7日留存D6当日活跃D1至D7任意活跃区间口径SQL实现也不难在follow_flag里加两个字段即可MAX(CASE WHEN fa.day_diff BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS d1_3_flag, MAX(CASE WHEN fa.day_diff BETWEEN 1 AND 7 THEN 1 ELSE 0 END) AS d1_7_flag建议在给业务方交付的结果表里同时带retention_type字段区分strict_day和any_in_range从源头消灭对不上数的问题。5.5 常见问题速查表常见现象可能原因排错/解决方案留存率超过100%快照表重复或JOIN导致行膨胀基期先DISTINCT统一用COUNT(DISTINCT)胜率全部为0或NULLresult字段枚举值不匹配或ID关联不上先SELECT DISTINCT result排查枚举值再检查ID口径对局数异常偏大一局双方各一行导致重复计数检查battle_iduser_id粒度必要时用COUNT(DISTINCT battle_id)留存率极低活跃表只记录了登录但对局同一天不同时点入库确认活跃定义是否包含对局行为检查ETL写入时间3日/7日留存与业务方对不上“第3天”和“3天内”口径混用两种口径都输出结果表加口径标识SQL跑太久超时多次重复扫描活跃表用follow_flag一次扫描替代多次LEFT JOIN加联合索引胜率分桶后某档人数为0阈值设置不合理或过滤条件太狠先看胜率分布直方图按分位数或业务经验调整阈值把这张表存下来下次遇到留存SQL出问题照着排查能省半天时间。按我这套流程走从口径定义、表结构设计到MySQL SQL实现、性能优化和排错基本覆盖了留存率计算的全流程。最后再分享一个小技巧跑批量的留存任务时把胜率分层的结果先物化成一张临时表后续每天只需要增量算基期活跃和留存匹配不要每次都从原始对局流水重新聚合。数据量大起来之后这个优化能让整个任务的时间缩短一半以上而且是能直接落地的。