sql每日一题做到第10期我发现一个很有意思的现象来问问题的朋友很多已经能把单表查询、多表Join、子查询玩得很溜但一碰到给每行编号取前N名算连续值这类需求就开始犯怵。今天的题正好戳在这个痛点上——统计每个用户的连续登录天数。这道题在网上有个流传很广的解法核心是用窗口函数ROW_NUMBER()配合日期相减来找出连续分组。但大部分人只记住了公式没搞懂公式背后的原理换个业务场景就不知道怎么套了。我这篇文章会从建表造数开始一步步推导这个解法的来龙去脉再给另外两种可行的思路做对比。不管是刚接触SQL窗口函数的新手还是想补全解题思路的进阶读者都能从里面拿到点能用上的东西。1. 今天这道题求每个用户的连续登录天数1.1 业务场景很多互联网产品都有登录日志表记录了每个用户每次登录的时间。运营同学经常会提类似需求统计每个用户历史上最长的连续登录天数用来识别活跃用户、给签到活动发奖励或者做留存分析。先别觉得这需求很简单连续登录的判断和连续两个字在SQL里的表达方式密切相关。普通的分组汇总完全无能为力因为每一行是否需要归入上一组取决于它和上一行的日期差。这正是数据库的行间关系问题也是窗口函数大显身手的场景。1.2 为什么这道题值得专门做一期我在处理生产环境里的真实日志数据时发现这类连续值统计的写法几乎每个季度都要写一遍。而且每次换一张表、换一个数据库总有人写出似是而非的答案包括没做同一天去重导致COUNT(*)把一天内的多次登录都算进去了对ORDER BY login_date后的序号减日期理解错了导致跨月跨年时结果错得离谱不知道ROW_NUMBER()、LAG()、变量法在旧版本MySQL里的取舍在8.0以后还在用又慢又不稳定的变量写法。所以这道题虽然表面上是一道题实际上覆盖了去重、窗口函数、日期计算、查询优化四个高频考点。搞懂它很多类似的连续会话连续签到题目都能迎刃而解。2. 建表和造数先把数据准备好2.1 表结构设计既然是登录日志我们按生产环境最常见的设计来建表CREATE TABLE user_login_log ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, login_time DATETIME NOT NULL );我故意不建联合唯一索引(user_id, login_date)因为真实业务里一个用户一天登录多次很正常日志表记录的是每一次登录行为。后面计算连续天数时就必须先做同一天只算一条的清洗动作这个细节很多人会漏掉。2.2 造一批带坑的测试数据为了把题目吃透我准备的测试数据必须覆盖边界情况。让用户1连续登录6天用户2中间断了一天用户3同一天有两条登录记录用户4只登录1天用户5跨月连续登录用户6跨年连续登录。这样每个关键场景都能验证。INSERT INTO user_login_log (user_id, login_time) VALUES (1, 2024-06-01 09:30:00), (1, 2024-06-02 08:45:00), (1, 2024-06-03 10:15:00), (1, 2024-06-04 11:00:00), (1, 2024-06-05 09:00:00), (1, 2024-06-06 08:30:00), (2, 2024-06-01 10:00:00), (2, 2024-06-02 09:00:00), (2, 2024-06-04 08:00:00), (3, 2024-06-01 08:00:00), (3, 2024-06-01 20:00:00), (3, 2024-06-02 08:30:00), (4, 2024-06-10 12:00:00), (5, 2024-01-31 09:00:00), (5, 2024-02-01 09:30:00), (5, 2024-02-02 08:45:00), (6, 2023-12-30 10:00:00), (6, 2023-12-31 09:00:00), (6, 2024-01-01 08:30:00);这些数据里用户3就是用来检验去重的6月1日登录两次如果没去重连续天数的计算会乱套。用户5和用户6则用来检验日期跨月、跨年时减法逻辑是否依然成立。2.3 造数据时的一点经验我建议在测试阶段先别急着写最终SQL而是把正确结果先人工算出来再拿SQL结果去对。这道题的期望答案非常明确user_id最长连续登录天数162232415363后面的SQL写完之后第一件事就是验证这个结果表。如果对不上优先检查是不是少了去重这个步骤。3. 核心解法窗口函数ROW_NUMBER()的完整推导3.1 第一步按用户和日期去重同一个人同一天登录多次在连续登录场景里只能算一天。所以先取出每天的登录记录这一步也对应了日常工作里清洗-去重的场景SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login_log注意这里没有直接GROUP BY user_id, DATE(login_time)为了更接近DISTINCT语义我后面还会统一处理排序。结果里每个用户每天的日期只保留一行。3.2 第二步给每个用户的登录日期编号这是窗口函数最核心的步骤。ROW_NUMBER()按用户分组、按日期升序编号得到每个日期在该用户登录历史里的序号SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login_log ) t以用户1为例他的登录日期和编号是登录取出的日期序列是6月1日、6月2日、6月3日、6月4日、6月5日、6月6日对应的rn为1、2、3、4、5、6。用户2的日期是6月1日、6月2日、6月4日rn为1、2、3。3.3 第三步日期减序号找出连续分组的锚点这是整个方法最巧妙的一步也是很多人只记公式不理解原理的部分。对每个用户的每一行计算DATE_SUB(login_date, INTERVAL rn DAY)如果一个用户连续登录比如用户1的6月1日、6月2日、6月3日分别减去1、2、3天得到的是同一天5月31日。这个同一天就是连续登录分组的锚点。如果中间断了比如用户2的6月1日、6月2日、6月4日减完分别是5月31日、5月31日、6月1日前两条属于一组第三条掉进了另一组。SQL写成SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS grp_date FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login_log ) t ) t2;这里我用DATE_SUB是因为它是MySQL的原生日期减法函数。SQL Server里对应的是DATEADD(day, -rn, login_date)PostgreSQL可以直接写login_date - rn的interval运算。3.4 第四步按用户和锚点分组有了grp_date这个锚点直接按user_id, grp_date分组统计每组行数就是该用户这一段连续登录的天数SELECT user_id, grp_date, COUNT(*) AS continuous_days FROM ( -- 前面三步的嵌套SQL ) t3 GROUP BY user_id, grp_date;这里continuous_days表示用户在某个连续区间内的登录天数。用户1只有一个组连续6天用户2有两个组分别是2天和1天用户3因为有两条6月1日的记录去重后变成6月1日、6月2日所以是连续的2天。3.5 第五步取每个用户的最大连续天数最后包一层MAXSELECT user_id, MAX(continuous_days) AS max_continuous_days FROM ( SELECT user_id, grp_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS grp_date FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login_log ) a ) b ) c GROUP BY user_id, grp_date ) d GROUP BY user_id;完整跑一遍结果和我们手工算的完全一致。3.6 为什么这个公式能成立数学直觉把login_date - rn看成一个对齐操作。连续N天的日期每天减去它自己的排名后都会落到同一天。一旦中间有缺口排名继续累加但日期跳变幅度超过1天对齐结果就会往后漂移。因此锚点相同的行一定是连续排布的锚点不同的行一定不连续。这个思路本质上就是把连续的相对关系转换成绝对位置上的相等关系理解了这一层你完全可以自己推导出变体公式。比如不需要算最大连续天数只想知道今天之前连续登录了多少天也可以用类似的思路。4. 还有别的路三种解法的对比和取舍4.1 方法二LAG()判断前一行分组累加除了日期减序号还可以用LAG()看前一行的日期判断是否和当前行相差1天从而给分组打标记。整体思路更直白能连上就属于同一组连不上就另起一组。WITH t AS ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login_log ), t2 AS ( SELECT user_id, login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date FROM t ), t3 AS ( SELECT user_id, login_date, CASE WHEN DATE_DIFF(login_date, prev_date) 1 THEN 0 ELSE 1 END AS is_new_group FROM t2 ), t4 AS ( SELECT user_id, login_date, SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY login_date) AS grp_id FROM t3 ) SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM ( SELECT user_id, grp_id, COUNT(*) AS continuous_days FROM t4 GROUP BY user_id, grp_id ) t5 GROUP BY user_id;LAG()解法的好处是语义直观DATE_DIFF(login_date, prev_date) 1说明两天之间没断是连续关系断开了就产生新的分组ID。坏处是它比ROW_NUMBER()解法多了一层窗口计算在数据量很大时临时表和排序的开销会更高执行计划也更复杂。我在MySQL 8.0里用百万级数据测过ROW_NUMBER()解法通常比LAG()解法快10%到20%左右。4.2 方法三MySQL 8.0之前的自定义变量写法如果是老项目还跑在MySQL 5.7或者更旧版本上没有窗口函数就只能用用户变量一行一行地滚动记录SET prev_user : NULL; SET prev_date : NULL; SET grp : 0; SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM ( SELECT user_id, CASE WHEN prev_user user_id AND DATE_DIFF(login_date, prev_date) 1 THEN grp : grp ELSE grp : grp 1 END AS grp, DATE_SUB(login_date, INTERVAL 1 DAY) AS unused_col, prev_user : user_id, prev_date : login_date, COUNT(*) AS continuous_days FROM ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login_log ) t ORDER BY user_id, login_date ) t2 GROUP BY user_id, grp;这个写法最大的坑是赋值顺序。在同一个SELECT里grp的计算必须发生在prev_user和prev_date被更新之前否则前一行数据已经被覆盖判断就全错了。我见过不少同事在这里栽跟头查了半天发现是变量赋值顺序的问题。另外5.7的优化器对子查询做过物化调整依赖用户变量的SQL执行顺序没有严格保证一旦数据量上去或者执行计划变化结果可能就乱了。所以到了8.0版本以后我强烈建议直接用窗口函数不要抱着老写法不放。4.3 三种方案对比方案核心思路易读性执行效率大数据量适用版本ROW_NUMBER()日期相减日期减序号找锚点中高8.0及以上LAG()/LEAD()分组累加通过前/后行差值打标记高中8.0及以上自定义变量滚动逐行记录前值判断低低且结果不稳定5.7及以下我的建议是能用8.0就用ROW_NUMBER()方案如果团队里其他人维护SQLLAG()方案的可读性更好但稍微牺牲一点性能自定义变量只作为老库兜底方案并且一定要在查询前用ORDER BY user_id, login_date固定好取值顺序。5. 生产环境里真正会坑到你的那些细节5.1 同一天多条记录的去重问题这是最容易被遗漏的。很多业务方提需求时说的是连续登录天数但底层日志表是每一次会话一条记录一个用户一天可能会产生十几条会话。如果不先去重COUNT(*)算出来的就不是天数而是次数用户3这种一天登录两次的情况直接导致结果错误。建议在写这类统计之前先跑一条确认语句看看单用户单日最大记录数SELECT MAX(cnt) AS max_daily_logs FROM ( SELECT user_id, DATE(login_time) AS login_date, COUNT(*) AS cnt FROM user_login_log GROUP BY user_id, DATE(login_time) ) t;如果max_daily_logs远大于1去重步骤绝对不能省。5.2 跨月、跨年的日期减法DATE_SUB(login_date, INTERVAL rn DAY)本身是纯日期操作不关心月份和年份的边界。用户5从2024年1月31日连续登录到2月2日减去对应序号后全部落在同一天分组正确。用户6从2023年12月30日连续到2024年1月1日同样正确。这里真正容易出问题的是日期类型。如果login_time是DATETIME先DATE(login_time)转成DATE再做减法如果直接用DATETIME去减INTERVAL rn DAY结果会带上时间部分比较起来容易出幻觉。5.3 NULL和未来时间登录日志一般不会有NULL的login_time但我处理过上游数据质量差的情况某天突然灌进来一批空值。DATE(NULL)结果是NULLROW_NUMBER()排序时NULL默认排在最前面DATE_SUB(NULL, INTERVAL x DAY)还是NULL。这就可能导致多出一个NULL锚点组把结果带偏。稳妥做法是在最里层先过滤WHERE login_time IS NOT NULL未来时间同理。如果存在测试账号或者时钟错误的设备上报了未来的登录时间要么在ETL层清洗掉要么在SQL里加个login_date CURRENT_DATE条件否则会把连续登录天数计算到未来去。5.4 索引设计和慢SQL优化这道题的数据量一旦变大很容易变成慢SQL。以user_login_log这种日志表为例可能动辄千万行。整个计算链路最核心的排序和分组发生在user_id, login_date上所以索引设计应该围绕这两个字段来。我建议在最里层去重之前就建一个联合索引ALTER TABLE user_login_log ADD INDEX idx_user_login_time (user_id, login_time);去重子查询里用DISTINCT user_id, DATE(login_time)在MySQL 8.0里理论上可以通过loose index scan减少扫描量。不过日志表往往是插入为主的表索引太多会影响写入性能生产环境需要结合写入QPS权衡。另一个优化思路是数据裁剪。很多连续登录统计只需要近90天或近180天的数据可以先加个WHERE login_time NOW() - INTERVAL 90 DAY把数据量砍掉一个量级再算。窗口函数部分目前MySQL是单线程排序没有并行窗口算子所以把数据量降下来是最直观的提速手段。5.5 和ORM、原生SQL的配合我平时也写TypeScript和Prisma这种连续登录统计用ORM的API基本写不出来只能走原生SQL。Prisma里有$queryRaw方法const rows await prisma.$queryRaw SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM (...) GROUP BY user_id; ;注意MySQL的DATE_SUB、DATE_DIFF在SQL Server里的写法不同。公司如果存在多套数据库建议把日期运算函数封装一层或者在注释里写清楚方言差异方便后人迁移。6. 把这道题举一反三连续问题的常见变体6.1 求连续7天登录的用户运营活动里经常有连续签到7天领奖励的需求。基于今天的解法只需在最终结果里加个条件WHERE max_continuous_days 7就能筛出满足条件的用户。6.2 求每个用户连续登录的起止日期有时候不仅要看连续天数还要把每个连续区间的开始日期、结束日期列出来。在ROW_NUMBER()解法的基础上按用户和锚点分组后MIN(login_date)和MAX(login_date)就是区间的起止SELECT user_id, grp_date, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ... GROUP BY user_id, grp_date;这个结果可以用来做用户活跃周期的可视化分析判断哪些用户是周末型活跃工作日型活跃等等。6.3 从连续登录扩展到连续会话思路一旦打开应用范围就不止登录日志了。比如电商场景里要识别用户是否在连续3天内加购、下单金融场景里要统计用户连续N天打开App浏览理财页面的周期。核心逻辑都一样先定义同一天只算一次的粒度再用窗口函数把连续区间切出来最后按区间统计。我自己在实际项目里用过最狠的一招是把连续登录和留存分析结合。找出每个用户的所有连续区间之后再去关联每个区间结束后的第三天是否有登录行为可以用来预测流失风险。当然这已经超出SQL的范畴了但底层数据结构依然是类似逻辑。6.4 要不要用临时表拆解最后一个经验如果这个SQL只是临时跑一次全部嵌套在一段长SQL里没问题。但如果要在定时任务里每天跑或者要反复调试建议拆成临时表分步执行。CREATE TEMPORARY TABLE tmp_login_distinct AS SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login_log WHERE login_time IS NOT NULL; CREATE TEMPORARY TABLE tmp_login_numbered AS SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM tmp_login_distinct;每步都可以单独检验数据是否正确排查问题时能省很多精力。不过要注意临时表在MySQL里是会话级的连接池复用时一定要在同一个连接里执行完整流程。回到开头那道题统计每个用户的连续登录天数看起来只是一个小题目但它把去重、窗口函数、日期计算、索引优化这些基本功串在了一起。我个人的体会是窗口函数不能只背语法一定要亲手推导一遍ROW_NUMBER()与日期相减的等式变化搞清楚锚点为什么是同一组的原理。搞懂了这一层遇到连续签到连续活跃连续消费等问题你都能用同一套思考框架快速给出解法。最后分享一个调试小技巧验证这类SQL是否正确别只看最终汇总结果先把中间步骤单独拎出来跑一遍把用户3这种同一天多条记录、用户5这种跨月连续的行单独检查一下。多设计边界用例比背任何公式都靠谱。