连续登录天数这道题做过数据岗笔面的人基本都躲不开。它看起来只是“查个最大值”真写起来却能把人卡在窗口函数、日期函数和去重逻辑这三道坎上有人写出来结果全是 1有人算出的天数比实际多出一截还有人被追问一句“那顺便把起止日期也给我”就直接哑火。查询每个用户最大连续登录天数本质上是 SQL 里的经典“间隙与孤岛”问题——把一串带日期的流水切成若干段连续区间再取最长的那段。它既能考出你对窗口函数的理解深度也能看出你会不会处理脏数据和边界日期。下面这套内容适合三类人准备面试想拿满分的、工作中真要做连续签到/连续达标统计的、以及写过一次但没写对想搞清楚为什么的。我会从思路选型一路讲到三种数据库方言的可复现代码再补上几个高频追问变体和踩坑记录照着敲一遍基本能形成肌肉记忆。1. 先搞清楚这道题在考什么1.1 从“登录流水”到“连续天数”中间隔着三层抽象很多人一上来就写 SQL写挂了再回头找原因其实问题出在没有先把业务语言翻译成集合语言。原始数据是一张流水表每一行是“某人在某个时刻登录了一次”粒度是事件级。而题目要的答案是“每个用户一行、一个天数”粒度是用户级。这中间要跨过三层转换每一层都有坑。第一层是去重到天。一个人一天登录十次业务上只算登录了一天。如果不做这一步后面所有基于行号的逻辑全部失真。第二层是把“连续”变成可计算的量。SQL 是面向集合的语言它擅长比较、聚合、连接天生不擅长处理“相邻两行之间有没有断档”这种序列问题。所以必须想办法把“连续性”编码成一个普通的列值让GROUP BY能够识别。第三层是在每组内部聚合再取最大值。分组之后每组的天数就是组内行数外层再按用户取MAX。这三层抽象想清楚了代码其实只有几行。我见过太多人卡住不是因为语法不会而是脑子里还在用“遍历每一条记录、记住上一个日期、计数器加一”这种过程式思维。SQL 不是这么干活的你得换个脑子用“给每行打标签、按标签分组”的方式思考。1.2 三种主流写法的取舍窗口函数、变量法、自连接市面上能搜到的写法大致三类我把它们的适用边界列清楚你按手里的数据库版本挑就行。写法核心思路依赖能力适用场景主要缺点窗口函数 日期差值分组日期 - row_number得常量按常量分组窗口函数MySQL 8.0、PG、Hive、Spark、SQL Server 2012首选方案可读性和性能都最好老版本数据库不支持用户变量法逐行比较当前日期与上一行日期断档则分组号加一会话变量MySQL 5.x、部分老版本只能在没有窗口函数时兜底执行顺序不保证跨版本行为不稳定自连接 / 关联子查询对每行统计比它小的连续行数或反查断点基础 SQL教学演示、极简面试手写复杂度 O(n²)大表基本不可用我的建议很明确只要窗口函数可用一律用第一种。它的好处不只是短更重要的是逻辑是“声明式”的——你描述清楚每一行该属于哪个岛剩下的交给优化器执行计划干净索引也吃得上。变量法在 MySQL 8.0 之后官方都不推荐了因为SELECT列表里用户变量的赋值求值顺序没有保证同一个查询在不同版本、不同并行度下可能给出不同结果这种不确定性放在生产环境里是要命的。自连接那条路除非面试官明确说“不许用窗口函数也不许用变量”否则没必要自己给自己加难度。1.3 面试官真正想听的三个得分点写对结果只是及格线。这道题之所以被反复拿出来问是因为它有明显的分层能写出来是 60 分能说清为什么这么写是 80 分能主动指出数据陷阱和变体解法才是满分。第一个得分点是去重意识。你在写代码之前先问一句“这张表是一天一条还是一天多条”或者直接在代码里体现了DISTINCT面试官立刻知道你真处理过线上数据。第二个得分点是边界处理比如跨年跨月会不会算错、日期字段带时分秒怎么办、闰年二月有没有影响。第三个得分点是扩展能力主动抛出“如果要求当前连续天数”“如果允许断一天”“如果要算每天有多少人连续登录超过 7 天”这些变体说明你不是背题而是掌握了模式。顺带说一句这道题的通用名字叫 gaps and islands间隙与孤岛连续登录只是它在用户行为分析里的一个具体投影。同样的骨架可以套在“连续达成销售目标的月份”“设备连续在线的时长”“连续未还款的期数”上。把这个模式吃透比记住某一段 SQL 值钱得多。2. 核心原理为什么“日期减序号”能把连续段塌成一个常量2.1 一个排队类比编号和日期同步前进先讲最关键的洞察。假设某用户从 3 月 1 日开始天天登录一天不落。我们按日期排序给每行编个号3 月 1 日编号 13 月 2 日编号 23 月 3 日编号 3……现在做一件事用日期减去它对应的编号。3 月 1 日减 1 天 2 月 28 日3 月 2 日减 2 天 2 月 28 日3 月 3 日减 3 天 2 月 28 日。你会发现结果永远是同一个日期。这就是整个解法的灵魂日期每天前进 1编号也每天前进 1两者同步差值就被抵消了——就像两个人并排走路速度一样他们之间的间距保持不变。而一旦某天没登录日期跳了 2 天编号只前进了 1差值就会变小一天这个“常量”被打破标记着新的一个岛开始了。所以GROUP BY这个差值天然就把连续区间切成了独立的组。每组内的行数就是这段连续登录的天数。最后按用户取MAX答案就出来了。整个过程不需要任何循环不需要记住“上一行”完全是集合运算。这个思路的好处还在于它天然支持跨月跨年。因为你减的是真实的日期值不是“本月第几天”这种会归零的字段。3 月 1 日减 1 天就是 2 月 28 日或 29 日数据库自己处理闰年你完全不用操心。这也解释了为什么有些人用DAY(login_date) - rn会算错——月份一换DAY归零常量立刻崩。2.2 手工推演8 行数据走一遍光讲道理容易虚拿一份小样本手推一遍最清楚。假设用户 A 的登录日期已去重是3-01、3-02、3-03、3-06、3-07、3-08、3-09、3-15。登录日期row_number日期 - 序号所属分组组内计数2024-03-0112024-02-29G112024-03-0222024-02-29G122024-03-0332024-02-29G132024-03-0642024-03-02G212024-03-0752024-03-02G222024-03-0862024-03-02G232024-03-0972024-03-02G242024-03-1582024-03-07G31看出来了吧三组分别是长度 3、4、1最大连续登录天数就是 4。注意 2024 年是闰年2 月有 29 天所以 3-01 减 1 天落在 2-29。如果你的实现在这里算出 2-28说明日期运算用的是某种减法近似需要检查。再补一个细节分组表达式用的那个常量本身没有业务含义它就是个标签。所以你在GROUP BY里写DATE_SUB(login_date, INTERVAL rn DAY)或者只写别名都行但很多数据库不允许GROUP BY直接引用SELECT里的别名MySQL 允许标准 SQL 不允许稳妥起见可以套一层子查询先算出标签列外层再聚合。我在第 3 节的代码里就是这么写的逻辑分层清晰排查也方便。2.3 为什么去重是这条流水线的第一道闸门这是最容易翻车的地方我必须单独拎出来说。刚才的推导有个隐含前提日期序列每次只前进 1 天。如果同一天出现两行日期不变但编号照样 1差值就会被拉低原本属于两个不同岛的行会被错误地并到一起。举一个真实会算错的例子。用户 B 的流水是1 月 1 日登录了两次1 月 3 日登录了一次。不去重直接算登录日期row_number日期 - 序号2024-01-0112023-12-312024-01-0122023-12-302024-01-0332023-12-31按差值分组后2023-12-31这一组里有 2 行1-01 和 1-03程序会开心地告诉你“最大连续登录 2 天”。但真实情况是 1 月 1 日登录、1 月 2 日没登录、1 月 3 日登录最大连续只有 1 天。答案直接翻倍而且这种错误在小数据量下极难发现因为数字看起来还挺合理。去重的写法有几种SELECT DISTINCT user_id, DATE(login_time)、GROUP BY user_id, DATE(login_time)、或者先子查询聚合再开窗。我个人偏好GROUP BY版本因为顺手可以把当天的登录次数、首次登录时间一起带出来后面做“登录次数分布”之类的扩展时不用改结构。用DISTINCT也行胜在短面试手写时更省行数。顺带提醒一个连带问题去重粒度必须是用户 天。只按日期去重会把不同用户压成一行只按用户去重更离谱。这条听起来像废话但真有人在紧张的时候写错。2.4 row_number、rank、dense_rank 在这里的真实差别窗口函数有三个常用排名函数很多教程说“这里用什么都一样”这个说法在数据已去重的前提下是对的但没去重的时候差别巨大值得说清楚。ROW_NUMBER()是强制连续编号哪怕值相同也给 1、2、3绝不重复。RANK()遇到相同值给相同编号然后跳号1、1、3。DENSE_RANK()相同值给相同编号但不跳号1、1、2。在“日期已去重”的场景下每个日期唯一三者结果完全一致随便用。但在没去重的情况下RANK会让同一天的两行拿到同一个编号日期减编号的结果反而相同会把本来不连续的两天粘成一组ROW_NUMBER则会给出前面演示的那种错位。也就是说三个函数在不该用的时候都会错只是错法不同。所以结论不是“选哪个函数”而是“先把去重做掉然后一律用ROW_NUMBER”。它语义最明确只负责给行排个序不掺和值的比较。我在代码 review 里看到有人用RANK又没去重基本可以直接判定这段逻辑没跑过大盘数据。3. 可复现的完整实操三种方言从建表到出结果3.1 建表与造数一份覆盖各种坑的测试集纸上谈兵不如直接跑。下面这份测试数据我特意设计成能覆盖大多数坑有人一天登录多次、有人连续跨月、有人断档、有人只有单日记录、还有跨年的情况。CREATE TABLE user_login_log ( id BIGINT PRIMARY KEY, user_id INT NOT NULL, login_time DATETIME NOT NULL ); INSERT INTO user_login_log (id, user_id, login_time) VALUES (1, 1001, 2024-01-28 09:12:00), (2, 1001, 2024-01-29 10:03:00), (3, 1001, 2024-01-30 08:45:00), (4, 1001, 2024-01-31 21:30:00), (5, 1001, 2024-02-01 07:20:00), (6, 1001, 2024-02-01 19:55:00), -- 同一天第二次登录 (7, 1001, 2024-02-04 11:10:00), (8, 1001, 2024-02-05 12:00:00), (9, 1002, 2023-12-30 09:00:00), (10, 1002, 2023-12-31 09:30:00), (11, 1002, 2024-01-01 10:00:00), -- 跨年连续 (12, 1002, 2024-01-03 10:00:00), (13, 1003, 2024-03-15 08:00:00), (14, 1003, 2024-03-15 18:20:00);按这份数据人工核对一下预期答案用户 1001 从 1-28 到 2-01 连续 5 天中间 2-01 有两次登录但算一天然后断档2-04、2-05 连续 2 天所以最大值是 5。用户 1002 从 12-30 到 01-01 跨年连续 3 天之后断开最大值 3。用户 1003 只有 3-15 一天两次登录最大值 1。提示造测试数据时务必塞进“同一天多条”和“跨年”这两个特征它们是验证逻辑是否正确的照妖镜。只造一行一天的干净数据跑出正确结果也不能说明代码没问题。3.2 MySQL 8.0 写法窗口函数版标准解法分三层子查询每一层职责单一出问题好定位。最内层去重中间层编号并打标签外层聚合取最大值。SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS grp, -- 打标签 COUNT(*) OVER ( PARTITION BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ) AS continuous_days FROM ( SELECT user_id, DATE(login_time) AS login_date, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY DATE(login_time) ) AS rn FROM user_login_log GROUP BY user_id, DATE(login_time) -- 关键按人按天去重 ) t1 ) t2 GROUP BY user_id ORDER BY user_id;几点说明。第一层用了GROUP BY而不是DISTINCT原因是如果原表在(user_id, login_time)上有索引GROUP BY能直接走索引完成去重和排序少一次额外的排序动作。第二层同时算出rn和标签列grp这里必须注意ROW_NUMBER里的ORDER BY要和PARTITION BY的字段对应上排序字段错了分组就乱。第三层其实没有直接GROUP BY grp后再COUNT而是用了COUNT(*) OVER(PARTITION BY ...)把组内行数挂在每一行上这样省掉一层嵌套——两种写法等价看你顺手。如果你更喜欢“先分组计数、再按用户取最大”的两段式也可以这么写读起来更直白WITH dedup AS ( SELECT user_id, DATE(login_time) AS login_date FROM user_login_log GROUP BY user_id, DATE(login_time) ), tagged AS ( SELECT user_id, login_date, DATE_SUB( login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY ) AS grp FROM dedup ) SELECT user_id, MAX(days) AS max_continuous_days FROM ( SELECT user_id, grp, COUNT(*) AS days FROM tagged GROUP BY user_id, grp ) g GROUP BY user_id;这种写法在面试手写时更有优势每个 CTEWITH子句一句话说清自己在干什么面试官扫一眼就知道你想明白了。我个人平时更爱用这种改起来也方便——比如要加起止日期只在最后一个聚合里补MIN和MAX就行。3.3 PostgreSQL 与 Hive/Spark 的方言差异同一套逻辑换个数据库主要差在日期减法怎么写。这是最容易在跨平台迁移时踩的坑我按常用平台列一下。平台日期减 N 天的写法备注MySQLDATE_SUB(d, INTERVAL n DAY)n可以是列实测可用PostgreSQLd - n或d - n * INTERVAL 1 daydate类型可直接减整数Hive / Spark SQLdate_sub(d, n)传INT与日期字段组合SQL ServerDATEADD(DAY, -n, d)注意负号位置Oracled - nDATE类型支持整数加减PostgreSQL 版本完整写出来是这样注意date类型减整数得到的是date减interval得到的是timestamp两者在GROUP BY时不会混但显示格式不同别被结果吓到WITH dedup AS ( SELECT user_id, login_time::date AS login_date FROM user_login_log GROUP BY user_id, login_time::date ), tagged AS ( SELECT user_id, login_date, login_date - ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date )::int AS grp FROM dedup ) SELECT user_id, MAX(cnt) AS max_continuous_days FROM ( SELECT user_id, grp, COUNT(*) AS cnt FROM tagged GROUP BY user_id, grp ) x GROUP BY user_id ORDER BY user_id;Hive 和 Spark 里要注意两件事一是date_sub只接受INT如果你前面用的是ROW_NUMBER()返回的是BIGINT有的版本会报类型不匹配显式转一下CAST(rn AS INT)更稳二是大数据量下GROUP BY会产生大量 shuffle如果表是按用户分区的把PARTITION BY user_id和分区键对齐能省不少时间。我在一个日增几千万行的日志表上跑过类似逻辑把去重和开窗合并成一个GROUP BY 窗口的算子后耗时就降了将近一半因为省掉了中间落盘。3.4 MySQL 5.7 没有窗口函数变量法的正确姿势与坑老 MySQL 用户绕不开这个问题。变量法的思路是开一个按用户、按日期排好序的游标逐行判断如果当前日期和上一行日期相差 1 天分组号不变否则分组号 1。SELECT user_id, MAX(cnt) AS max_continuous_days FROM ( SELECT user_id, login_date, grp, COUNT(*) AS cnt FROM ( SELECT user_id, login_date, grp : CASE WHEN uid user_id THEN 1 WHEN DATEDIFF(login_date, prev_date) 1 THEN grp ELSE grp 1 END AS grp, uid : user_id AS _uid, prev_date : login_date AS _d FROM ( SELECT user_id, DATE(login_time) AS login_date FROM user_login_log GROUP BY user_id, DATE(login_time) ORDER BY user_id, login_date ) d, (SELECT grp : 0, uid : NULL, prev_date : NULL) init ) t GROUP BY user_id, grp ) x GROUP BY user_id;这段代码有几个必须知道的坑我按踩坑顺序说。第一派生表的排序不保证。变量法强依赖“行按user_id, login_date顺序流过”但 SQL 标准里子查询的ORDER BY在部分场景会被优化器忽略。稳妥做法是别依赖内层排序把ORDER BY放在最外层或者干脆用SELECT ... FROM (...) d ORDER BY user_id, login_date显式约束。我在 MySQL 5.6 上确实遇到过加了LIMIT或者被视图包裹后排序失效结果全乱的情况。第二用户切换时分组号必须重置。上面代码里WHEN uid user_id THEN 1就是干这个的。漏了它用户 1001 的最后一段和用户 1002 的第一段会连成一组最大天数算出个四不像。而且prev_date在切换用户时必须置空否则DATEDIFF会拿一整个月前的日期去比差值大概率不等于 1虽然碰巧也能分对组但那是运气不是逻辑。第三同一条SELECT里连续赋值多个变量的顺序。这行代码里grp、uid、prev_date三个变量在同一个SELECT列表里被赋值执行引擎通常从左到右求值但这个行为官方从来只是“实现如此”不是承诺。升级到大版本后如果有变化这段代码会静默出错。所以我给的建议是生产环境别用变量法能升级就升级。面试时如果被要求手写写完主动补一句“这个写法依赖变量求值顺序MySQL 8.0 之后官方不推荐实际项目里我会用窗口函数”反而加分。4. 面试官最爱追问的四个变体4.1 最大连续天数加上起止日期这是最高频的追问因为业务方看报表时永远想知道“最长那段是从几号到几号”。改法很小在最后那层按grp聚合时多带两个字段就行。SELECT user_id, MAX(cnt) AS max_days, SUBSTRING_INDEX( GROUP_CONCAT( CONCAT(start_date, ~, end_date, #, cnt) ORDER BY cnt DESC ), ,, 1 ) AS longest_range FROM ( SELECT user_id, grp, COUNT(*) AS cnt, MIN(login_date) AS start_date, MAX(login_date) AS end_date FROM tagged GROUP BY user_id, grp ) g GROUP BY user_id;GROUP_CONCAT那一段是为了把“最长那一段”的起止日期也一起取出来因为MAX(cnt)和MIN(login_date)直接组合会张冠李戴——MIN是全局最小的开始日期不一定属于最长那段。这里用了拼接再截取的土办法MySQL 8.0 更优雅的写法是用窗口函数SELECT user_id, cnt AS max_days, start_date, end_date FROM ( SELECT user_id, grp, COUNT(*) AS cnt, MIN(login_date) AS start_date, MAX(login_date) AS end_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) AS rk FROM tagged GROUP BY user_id, grp ) x WHERE rk 1;这个写法干净多了而且在 PostgreSQL、Hive 上都能直接跑。要注意GROUP BY之后在窗口函数里用COUNT(*)排序是合法的因为COUNT是聚合结果窗口函数在聚合之后计算。如果同一用户存在两段长度相同的最长连续并列第一ROW_NUMBER只会返回一条想全要就换成RANK。4.2 当前连续登录天数截至今天跟“最大”不同“当前”只关心最新那一段而且很多场景要求“截至今天还在连续”。这就多了一个判断最后一段的结束日期必须是今天或者昨天如果今天是凌晨且数据还没同步。WITH dedup AS ( SELECT user_id, DATE(login_time) AS login_date FROM user_login_log GROUP BY user_id, DATE(login_time) ), tagged AS ( SELECT user_id, login_date, DATE_SUB( login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY ) AS grp FROM dedup ), sized AS ( SELECT user_id, grp, COUNT(*) AS cnt, MAX(login_date) AS last_date FROM tagged GROUP BY user_id, grp ) SELECT user_id, cnt AS current_days FROM ( SELECT user_id, grp, cnt, last_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY last_date DESC) AS rk FROM sized ) x WHERE rk 1 AND last_date DATE_SUB(CURDATE(), INTERVAL 1 DAY);这里的last_date 昨天就是“还在连续”的判断。为什么留一天缓冲因为离线数仓的日志通常是 T1 落地的你今天跑昨天的分区用户最后登录只能是昨天。如果表格是实时表那就直接比对CURDATE()。这个细节我建议面试时主动说出来它体现的是你对数据链路的真实理解而不是单纯会写 SQL。还有一个隐含问题如果用户最后登录是三天前那他“当前连续”其实是 0不是那段历史长度。所以WHERE last_date 昨天这个条件不是可选的它承担的是“是否仍然连续”的语义。少了它你会把一个早就断签的用户报成连续 30 天。4.3 允许断一天的宽限连续“连续”这个词在业务上经常需要放松。比如健身打卡允许周日休息或者考核规则里“中断一天以内视为连续”。这类需求用前面的“日期减序号”直接套会失效因为断一天之后差值会变形成新岛。解法是换一种分组思路标记断点再做累积求和。思路是这样的先用LAG拿到上一次登录日期判断这次和上次的间隔是否超过允许值允许断 1 天就是间隔大于 2 天算真断断点标记为 1否则为 0然后对标记做累积求和得到的分组号就是“第几个连续段”。这个技巧叫“会话化”是所有变体里最通用的。WITH dedup AS ( SELECT user_id, DATE(login_time) AS login_date FROM user_login_log GROUP BY user_id, DATE(login_time) ), flagged AS ( SELECT user_id, login_date, CASE WHEN DATEDIFF( login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) ) 2 THEN 0 -- 间隔 2 天视为连续允许中间空 1 天 ELSE 1 END AS is_new_group FROM dedup ), grouped AS ( SELECT user_id, login_date, SUM(is_new_group) OVER ( PARTITION BY user_id ORDER BY login_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS grp FROM flagged ) SELECT user_id, MAX(cnt) AS max_days FROM ( SELECT user_id, grp, COUNT(*) AS cnt FROM grouped GROUP BY user_id, grp ) g GROUP BY user_id;注意窗口帧ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW要显式写虽然多数数据库默认就是它但RANGE和ROWS在遇到重复排序值这里日期已去重不会有重复时行为不同显式指定能避免歧义。另外这个写法里“天数”的语义变了它统计的是登录天数而不是日历跨越天数。如果业务考核的是“日历连续天数”那就得改成DATEDIFF(MAX, MIN) 1。这两个口径差别很大需求评审时必须问清楚我见过不止一次因为口径没对齐导致指标对不上。4.4 每天的最大连续登录用户数这个变体是留存和活跃分析里的常见指标问法一般是“统计每天有多少用户的连续登录天数达到 7 天以上”。它需要把前面的结果和日期维度关联起来关键是区间展开——一个用户某段连续登录从 1 号到 10 号那么 3 号这天他也处于“已连续登录 N 天”的状态。WITH dedup AS ( SELECT user_id, DATE(login_time) AS login_date FROM user_login_log GROUP BY user_id, DATE(login_time) ), tagged AS ( SELECT user_id, login_date, DATE_SUB( login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY ) AS grp FROM dedup ), sized AS ( SELECT user_id, grp, MIN(login_date) AS s, MAX(login_date) AS e, COUNT(*) AS cnt FROM tagged GROUP BY user_id, grp ), dim AS ( SELECT DISTINCT login_date AS dt FROM dedup ) SELECT d.dt, COUNT(*) AS user_cnt FROM dim d JOIN sized s ON d.dt BETWEEN s.s AND s.e WHERE s.cnt 7 GROUP BY d.dt ORDER BY d.dt;这段的代价是区间 join大表上会爆。生产环境里通常不这么硬算而是先把用户-日期对展开成一行一天或者直接用日历表做笛卡尔积后过滤。如果数据量真的很大我会把它拆成两步先算出每个用户每天的“截至当日连续天数”用增量表存下来再按天聚合。这样每天只算增量历史数据不动成本可控。5. 踩坑实录与排查清单5.1 结果偏大、偏小、全是 1 的三种典型症状排查这类问题我有个习惯先看结果分布再看原始数据最后才怀疑语法。三种症状对应的病因差别很大。结果偏大九成是没去重。症状是最大值接近总行数或者某个用户的“连续天数”刚好等于他的登录次数。验证方法很简单跑一句SELECT user_id, COUNT(*) FROM user_login_log GROUP BY user_id和COUNT(DISTINCT DATE(login_time))对比差得多就说明有同日多行。另外还有一种偏大的情况是分组字段用错比如按MONTH(login_date)分组跨月会被硬切断或者错误合并。结果偏小常见原因是日期字段带时间没截断。DATE_SUB作用在DATETIME上时2024-01-01 09:00:00减 1 天得到2023-12-31 09:00:00两行的时分秒不同标签就不同连续的几天被打成一组组单行最大值永远是 1。解决办法是去重那层统一DATE()截断或者在建表阶段就把日期单独存一列。结果全是 1除了上面的时间戳问题还有一个高频原因ROW_NUMBER的ORDER BY写错了字段比如按id排序而不是按日期排序。行号和日期不同步差值自然乱跳几乎每行都自成一岛。这个错误特别隐蔽因为语法完全合法运行时也不报错。注意调试时不要只看最终结果把中间层单独SELECT出来看一眼。我会临时把tagged这个 CTE 的内容全部打出来观察grp列的取值是不是呈现出“连续几行相同、然后跳变”的形态一眼就能定位问题出在去重、排序还是减法。5.2 大表上的性能处理流水表动辄上亿行按上面的写法直接跑光 shuffle 就能跑一晚上。几个实用的优化点按性价比排序。第一尽早裁剪数据范围。绝大多数连续登录统计只需要近 90 天或近一年把时间条件下推到最内层能砍掉 90% 以上的数据量。条件要写成常量范围比如login_time 2024-01-01 AND login_time 2024-04-01不要写DATE_FORMAT(login_time, %Y) 2024——后者让索引直接失效这是最典型的性能杀手。第二用好复合索引。(user_id, login_time)是最理想的结构去重和开窗需要的排序可以完全依赖索引顺序完成执行计划里看不到Using filesort。如果表已经存在加索引前评估一下写入放大日志表通常写入频繁得权衡。第三减少中间层落盘。COUNT(*) OVER(PARTITION BY ...)的写法比“先 GROUP BY 再 JOIN 回去”要省一次 join在数据量大时差别明显。另外有些引擎比如 Spark里COUNT(DISTINCT ...)会退化成一个超慢的 shuffle能用GROUP BY就别用COUNT(DISTINCT)。第四注意分区裁剪。如果表按天分区WHERE条件里一定要带分区字段别只写user_id。这条听起来基础但我见过太多人写了个只按用户过滤的查询结果扫了全表所有分区。5.3 常见问题速查表下面这张表我基本是照着实际问题记录整理的遇到报错或者结果不对从上往下对一遍命中率挺高。现象可能原因排查方式解决最大天数等于登录总次数同日多次登录未去重对比COUNT(*)与COUNT(DISTINCT DATE)按user_id, DATE去重连续天数普遍偏小、多为 1日期字段带时分秒看login_date是否含时间部分用DATE()截断分组标签杂乱无规律ROW_NUMBER的ORDER BY用错字段打印中间层rn与日期并列观察改为按日期排序跨月处连续被打断用了DAY()之类的部分字段做运算检查减法表达式直接对完整日期做减法用户之间数据串组缺少PARTITION BY user_id看结果里是否有用户天数异常大所有开窗都加用户分区变量法结果不稳定依赖变量求值顺序换 MySQL 8.0 重跑对比改用窗口函数查询跑不动、超时全表扫描 大量 shuffle看执行计划有没有filesort加索引、下推时间条件当前连续天数虚高没有判断最后登录日是否临近今天检查last_date分布加日期临近条件还有一个容易被忽略的点DATE类型和字符串比较时不同数据库的隐式转换规则不一样。MySQL 里2024-01-01和DATE比较通常没问题但如果字段本身是VARCHAR存的日期排序会变成字符串排序2024-1-9会排在2024-1-10后面连续判断直接崩。遇到这种历史遗留的表结构宁可在子查询里先做一次STR_TO_DATE转换也别指望隐式转换。我个人在实际操作中的体会是这道题真正难的不是窗口函数怎么写而是在动手之前把数据的粒度、字段的类型、业务的口径三件事确认清楚。粒度决定要不要去重类型决定要不要截断和转换口径决定“连续”到底是按自然日还是按登录天数、允许不允许断档。这三件事问明白了代码基本是一次成型问不明白就算语法全对跑出来的数字业务方也不会认。最后再分享一个小技巧写这类带多层嵌套的查询时别一次性写完再跑一层一层地加每加一层用LIMIT 20看一眼中间结果出问题时你能立刻知道是哪一层引入的偏差比事后从最终结果倒推要省太多时间。