连续登录过，为什么还不算次日留存？先把第一次登录找出来

连续登录过为什么还不算次日留存有一个玩家登录日期是这样2月28日 2月29日 3月1日 3月2日 登录 没来 登录 登录他在3月1日、2日连续登录了。那么他算不算次日留存的玩家先别急着写“相邻两条记录日期差1”。如果要求是第一次登录后的第二天有没有回来我们该检查的其实是2月29日后面的连续两天不能补上这次缺席。这就是 LeetCode 550Game Play Analysis IV 要处理的问题每位玩家第一次登录后的次日再次登录算一位回访玩家用回访人数除以总玩家人数四舍五入保留两位小数。原表按玩家和日期唯一一条记录代表当天登录游戏局数可以为0。我们用自己的小数据看看查询是怎么从这个意思长出来的。有6条日志但只有3个玩家下面在独立的学习数据库里运行使用 MySQL 8.0。先选好测试库若已有名为Activity的表不要覆盖或删除它换一个空的学习库。建表不加IF NOT EXISTS让重名及时报错。CREATE TABLE Activity ( player_id INT NOT NULL, device_id INT NOT NULL, event_date DATE NOT NULL, games_played INT NOT NULL, PRIMARY KEY (player_id, event_date) ); INSERT INTO Activity VALUES (7, 1, 2024-02-28, 3), (7, 2, 2024-02-29, 0), (42, 1, 2024-02-28, 1), (42, 1, 2024-03-01, 4), (42, 2, 2024-03-02, 2), (90, 3, 2024-12-31, 5);把日期按玩家排开7号 2月28日 - 2月29日 42号2月28日 - 3月1日 - 3月2日 90号12月31日7号第二天回来了42号是开头那个反例90号没有第二条登录记录。所以应该是1个人回访总共3个人结果为0.33。不是1除以6。六行是登录日志同一个人多来几天会多几条日志但不会因此变成几个不同玩家。先让每个玩家只剩一行要知道42号该检查哪一天得先找到他的第一次登录。按玩家分组取最早的日期就可以SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ORDER BY player_id;结果是7 2024-02-28 42 2024-02-28 90 2024-12-31这张结果表的变化很重要原来每个玩家可能有很多行现在每个人恰好一行。接下来要找的日期也确定了7号找2月29日42号找2月29日90号找下一年的1月1日。我们不用再考虑所有日期两两之间的关系了每个玩家只有一个目标日期。SQL里可以给刚才这张结果起个名字Activity原日志 | | 按玩家取最早日期 v first_login每人一行 | | 各自日期再加一天 v 回到Activity找对应玩家、对应日期下面用的WITH first_login AS (...)就是定义这份命名查询结果便于后面接着使用。不是又创建了一张需要手动维护的业务表。找不到回访的玩家也要留在分母里我们把目标日期与原日志关联起来先不算比例看看究竟找到了什么WITH first_login AS ( SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ) SELECT f.player_id, f.first_date, DATE_ADD(f.first_date, INTERVAL 1 DAY) AS expected_date, a.event_date AS returned_date FROM first_login AS f LEFT JOIN Activity AS a ON a.player_id f.player_id AND a.event_date DATE_ADD(f.first_date, INTERVAL 1 DAY) ORDER BY f.player_id;读7号这一行第一次是2月28日想找2月29日确实找到了2月29日。再看42号想找的同样是2月29日但记录里没有。returned_date为 NULL。后面那两次登录都不是这一次关联的目标。7号 首次2/28 - 找2/29 - 找到 42号首次2/28 - 找2/29 - NULL 90号首次12/31 - 找次年1/1 - NULL 保留3行只有第1行真实匹配这里用左连接是因为没有回来的人也属于总玩家。若用内连接42号和90号会先被删掉再在剩下的一行里算比例就很容易把1/3算成1/1。为什么关联后每人仍然只有一行而没有像前面的考试次数题那样接出重复记录因为题目的(player_id, event_date)唯一同一个玩家在目标日期至多匹配一行。这也是后面能直接数行的重要条件。现在才来数分子和分母上面已经保留了3个人。用COUNT(*)数这些行得到分母3用COUNT(a.player_id)数右边真正匹配的玩家得到分子1。WITH first_login AS ( SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ) SELECT COUNT(a.player_id) AS returned_players, COUNT(*) AS total_players FROM first_login AS f LEFT JOIN Activity AS a ON a.player_id f.player_id AND a.event_date DATE_ADD(f.first_date, INTERVAL 1 DAY);本例输出1、3。右侧匹配不到时a.player_id为 NULLCOUNT(a.player_id)不计它真实的player_id又是不允许 NULL 的所以这里可以拿它识别是否匹配。在考试报表里我们说不能用COUNT(*)作为参加次数。这里却可以把它当分母并不矛盾这次就是要数保留下来的所有玩家包括没回来的玩家。先确定想数什么再选统计表达式不是给某一种COUNT永远贴上“正确”或“错误”。最后用分子除以分母再保留两位小数WITH first_login AS ( SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ) SELECT ROUND(COUNT(a.player_id) / COUNT(*), 2) AS fraction FROM first_login AS f LEFT JOIN Activity AS a ON a.player_id f.player_id AND a.event_date DATE_ADD(f.first_date, INTERVAL 1 DAY);得到0.33。这里使用 MySQL 的/不要换成DIV整数除法否则1除以3会先变成0。回头检查那条容易写错的路如果不先找首次日期而是直接找任意相邻两天SELECT ROUND(COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction FROM Activity AS a JOIN Activity AS b ON a.player_id b.player_id AND b.event_date DATE_ADD(a.event_date, INTERVAL 1 DAY);这份数据会得到0.67。它把7号和42号都数进去了。这条查询可以回答“哪些玩家曾经连续两天登录过”却没有保证这两天从第一次登录开始。DISTINCT只能避免同一个玩家数几次不能把检查日期变回第一次登录的次日。错误不在去重没加够而在前面选择的时间起点。还有两处小条件别顺手加上7号次日games_played0仍然有登录记录而且他的设备从1换到2也不影响这道题对玩家回访的判断。我们统计的是玩家登录不是正局数游戏行为也不是同设备回访。日期加一天不是日期里的数字加12月28日的下一天在2024年是2月29日2月29日再往后是3月1日。到12月31日还要进入下一年。SELECT DATE_ADD(CAST(2024-02-28 AS DATE), INTERVAL 1 DAY), DATE_ADD(CAST(2024-02-29 AS DATE), INTERVAL 1 DAY), DATE_ADD(CAST(2023-02-28 AS DATE), INTERVAL 1 DAY), DATE_ADD(CAST(2026-12-31 AS DATE), INTERVAL 1 DAY);本次结果依次是2024-02-29、2024-03-01、2023-03-01、2027-01-01。这也是为什么直接写DAY(日期)1再比较日号是不够的。日期运算规则可查 MySQL 日期函数文档。题目已经给的是 DATE。若真实日志存的是时间戳还要先确定业务日和时区不能不加说明地把24小时后的时间点当作“次日登录”。真正做报表时还要多问一句假如今天是10月20日某位玩家今天才首次登录。现在没有他10月21日的记录是不是就能认定他没回来当然不能第二天还没到。本题直接基于给定日期表统计实际留存报表却必须定义数据截至时间、完整的观察窗口以及哪些首次登录日期已经有资格参加统计。不能把“还没来得及观察”当成“已经观察过而未回访”也不能随手用今天的时间过滤一半日志让分子和分母来自不同范围。这和开头找首次日期是同一件事SQL之前先把要检查的那个人、那一天和能观察到的范围说清楚。附验证不只拿0.33对一下本次使用 Ubuntu WSL 中独立的 MySQL 8.0.46 实例不连接已有数据库。程序从文章提取实际 SQL核对首次日期、关联明细、人数、正确比例、错误查询和日期边界。再枚举两个玩家、四天内所有登录组合分别选择覆盖闰年二月底和跨年的日期段。是否登录、哪天首次、首次次日是否在集合里由 Python 的日期集合独立判断不用另写一条同结构 SQL 生成预期。另外有正文三人样例、空表和四个日历边界。空表时没有总玩家查询得到 NULL这里没有把“未定义的比例”随意改成0。实际业务若有不同空数据约定应明确制定。还会故意改成任意连续两天、以最后一天代替首次、除以日志行数、内连接、检查首次当天、要求局数大于0、只给日号加1。这些错误都必须在对应反例里暴露出来才说明测试区分了我们关心的条件。下面两份文件放在同一专用实验目录执行python3 verify_retention.py。需要现有 Linux MySQL 8.0 二进制和运行库BLOG_MYSQL_ROOT指向包含usr/bin/mysql、usr/sbin/mysqld的解包目录默认路径对应本地测试包。脚本新建自己的数据目录通过随机名 Unix socket 通信关闭 TCP 与 MySQL X。空密码只用于这次隔离实例不能拿来部署服务器不会安装、替换或连接你现有的数据库服务。结束后关闭实例日志保留。article-steps.json[ { id: setup, sql: CREATE TABLE Activity (\n player_id INT NOT NULL,\n device_id INT NOT NULL,\n event_date DATE NOT NULL,\n games_played INT NOT NULL,\n PRIMARY KEY (player_id, event_date)\n);\nINSERT INTO Activity VALUES\n (7, 1, 2024-02-28, 3),\n (7, 2, 2024-02-29, 0),\n (42, 1, 2024-02-28, 1),\n (42, 1, 2024-03-01, 4),\n (42, 2, 2024-03-02, 2),\n (90, 3, 2024-12-31, 5);, expected: [] }, { id: first, sql: SELECT player_id, MIN(event_date) AS first_date\nFROM Activity\nGROUP BY player_id\nORDER BY player_id;, expected: [ 7\t2024-02-28, 42\t2024-02-28, 90\t2024-12-31 ] }, { id: joined, sql: WITH first_login AS (\n SELECT player_id, MIN(event_date) AS first_date\n FROM Activity\n GROUP BY player_id\n)\nSELECT f.player_id, f.first_date,\n DATE_ADD(f.first_date, INTERVAL 1 DAY) AS expected_date,\n a.event_date AS returned_date\nFROM first_login AS f\nLEFT JOIN Activity AS a\n ON a.player_id f.player_id\n AND a.event_date DATE_ADD(f.first_date, INTERVAL 1 DAY)\nORDER BY f.player_id;, expected: [ 7\t2024-02-28\t2024-02-29\t2024-02-29, 42\t2024-02-28\t2024-02-29\tNULL, 90\t2024-12-31\t2025-01-01\tNULL ] }, { id: counts, sql: WITH first_login AS (\n SELECT player_id, MIN(event_date) AS first_date\n FROM Activity\n GROUP BY player_id\n)\nSELECT COUNT(a.player_id) AS returned_players,\n COUNT(*) AS total_players\nFROM first_login AS f\nLEFT JOIN Activity AS a\n ON a.player_id f.player_id\n AND a.event_date DATE_ADD(f.first_date, INTERVAL 1 DAY);, expected: [ 1\t3 ] }, { id: final, sql: WITH first_login AS (\n SELECT player_id, MIN(event_date) AS first_date\n FROM Activity\n GROUP BY player_id\n)\nSELECT ROUND(COUNT(a.player_id) / COUNT(*), 2) AS fraction\nFROM first_login AS f\nLEFT JOIN Activity AS a\n ON a.player_id f.player_id\n AND a.event_date DATE_ADD(f.first_date, INTERVAL 1 DAY);, expected: [ 0.33 ] }, { id: wrong-any, sql: SELECT ROUND(COUNT(DISTINCT a.player_id) /\n (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction\nFROM Activity AS a\nJOIN Activity AS b\n ON a.player_id b.player_id\n AND b.event_date DATE_ADD(a.event_date, INTERVAL 1 DAY);, expected: [ 0.67 ] }, { id: dates, sql: SELECT DATE_ADD(CAST(2024-02-28 AS DATE), INTERVAL 1 DAY),\n DATE_ADD(CAST(2024-02-29 AS DATE), INTERVAL 1 DAY),\n DATE_ADD(CAST(2023-02-28 AS DATE), INTERVAL 1 DAY),\n DATE_ADD(CAST(2026-12-31 AS DATE), INTERVAL 1 DAY);, expected: [ 2024-02-29\t2024-03-01\t2023-03-01\t2027-01-01 ] } ]verify_retention.pyimport getpass import itertools import json import os from collections import defaultdict from datetime import date, timedelta from decimal import Decimal, ROUND_HALF_UP from pathlib import Path import subprocess import tempfile import time base Path(__file__).resolve().parent steps json.loads((base / article-steps.json).read_text()) package Path(os.environ.get(BLOG_MYSQL_ROOT, /mnt/d/blog-ops/experiments/2026-10-03-candidates/mysql-packages/root)) env dict(os.environ, LD_LIBRARY_PATHstr(package / usr/lib/x86_64-linux-gnu)) run Path(tempfile.mkdtemp(prefixretention-, dirbase)) (run / data).mkdir() sock Path(/dev/shm) / (run.name .sock) args [str(package / usr/sbin/mysqld), --no-defaults, --basedir str(package / usr), --datadir str(run / data), --lc-messages-dir str(package / usr/share/mysql), --user getpass.getuser(), --secure-file-privNULL, --innodb-buffer-pool-size67108864, --innodb-redo-log-capacity16777216, --log-error str(run / server.log)] init subprocess.run(args [--initialize-insecure], envenv, capture_outputTrue, textTrue, timeout90) if init.returncode: raise RuntimeError(init.stderr) client [str(package / usr/bin/mysql), --no-defaults, --protocolSOCKET, --socket str(sock), -u, root, --batch, --skip-column-names, --connect-timeout2] server subprocess.Popen(args [--skip-networking, --mysqlx0, --socket str(sock), --pid-file str(run / mysql.pid), --tmpdir str(run)], envenv, stdoutsubprocess.DEVNULL, stderrsubprocess.DEVNULL) record {passed: False, run: str(run)} def sql(text): result subprocess.run(client, inputtext, envenv, capture_outputTrue, textTrue, timeout60) if result.returncode or result.stderr.strip(): raise RuntimeError(result.stdout result.stderr) return result.stdout.splitlines() def oracle(rows): players defaultdict(set) for player, _, day, _ in rows: players[player].add(date.fromisoformat(day)) if not players: return [NULL] returned sum(min(days) timedelta(days1) in days for days in players.values()) ratio (Decimal(returned) / Decimal(len(players))).quantize(Decimal(.01), roundingROUND_HALF_UP) return [str(ratio)] def insert(rows): # These generated identifiers and dates never come from external SQL input. if not rows: return values ,.join(f({p},{d},{day},{games}) for p, d, day, games in rows) return INSERT INTO Activity VALUES values ; try: for _ in range(200): if server.poll() is not None: raise RuntimeError((run / server.log).read_text()) if sock.exists(): try: sql(SELECT 1;) break except RuntimeError: pass time.sleep(0.1) else: raise TimeoutError(MySQL startup timed out) record[version] sql(SELECT VERSION();)[0] commands [CREATE DATABASE blog_retention; USE blog_retention;] for index, step in enumerate(steps): commands.extend([fSELECT __step_{index}__;, step[sql]]) chunks [] for line in sql(\n.join(commands)): if line.startswith(__step_): chunks.append([]) else: chunks[-1].append(line) assert chunks [s[expected] for s in steps], chunks record[articleSteps] [dict(s, actuala) for s, a in zip(steps, chunks)] final next(s[sql] for s in steps if s[id] final) main [(7, 1, 2024-02-28, 3), (7, 2, 2024-02-29, 0), (42, 1, 2024-02-28, 1), (42, 1, 2024-03-01, 4), (42, 2, 2024-03-02, 2), (90, 3, 2024-12-31, 5)] cases [main, []] for start in [date(2024, 2, 27), date(2026, 12, 30)]: slots [(p, (start timedelta(daysi)).isoformat()) for p in [7, 42] for i in range(4)] for bits in itertools.product([False, True], repeatlen(slots)): cases.append([(p, (i % 3) 1, day, i % 2) for i, ((p, day), enabled) in enumerate(zip(slots, bits)) if enabled]) for days in [(2024-02-28, 2024-02-29), (2024-02-29, 2024-03-01), (2023-02-28, 2023-03-01), (2026-12-31, 2027-01-01)]: cases.append([(99, i 1, day, 0) for i, day in enumerate(days)]) commands [USE blog_retention;] for index, rows in enumerate(cases): commands.extend([DELETE FROM Activity;, insert(rows), fSELECT __case_{index}__;, final]) actual [] for line in sql(\n.join(commands)): if line.startswith(__case_): actual.append([]) else: actual[-1].append(line) assert actual [oracle(rows) for rows in cases] record[cases] len(cases) record[emptyTableResult] actual[1] any_pair SELECT ROUND(COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) FROM Activity a JOIN Activity b ON a.player_idb.player_id AND b.event_dateDATE_ADD(a.event_date, INTERVAL 1 DAY); mutants { any-consecutive-days: any_pair, last-login-instead-of-first: final.replace(MIN(event_date), MAX(event_date)), divide-by-log-rows: final.replace(COUNT(*), (SELECT COUNT(*) FROM Activity)), drop-non-returners: final.replace(LEFT JOIN, INNER JOIN), check-first-day-itself: final.replace(INTERVAL 1 DAY, INTERVAL 0 DAY), require-positive-games: final.replace(INTERVAL 1 DAY), INTERVAL 1 DAY) AND a.games_played 0), increment-only-day-of-month: final.replace( a.event_date DATE_ADD(f.first_date, INTERVAL 1 DAY), YEAR(a.event_date)YEAR(f.first_date) AND MONTH(a.event_date)MONTH(f.first_date) AND DAY(a.event_date)DAY(f.first_date)1) } record[mutants] [] for name, query in mutants.items(): assert query ! final witness main if name ! increment-only-day-of-month else cases[-1] rows sql(USE blog_retention; DELETE FROM Activity;\n insert(witness) query) assert rows ! oracle(witness), name record[mutants].append({name: name, actual: rows, expected: oracle(witness), rejected: True}) record[passed] True finally: server.terminate() try: server.wait(timeout30) except subprocess.TimeoutExpired: server.kill() server.wait(timeout5) record[serverStopped] server.poll() is not None record[socketRemoved] not sock.exists() (run / results.json).write_text(json.dumps(record, indent2)) print(RESULTS str(run / results.json)) assert record[passed] and record[serverStopped] and record[socketRemoved] print(RETENTION_CHECKS_PASSED)