“最大连续登录天数”这道 SQL 题我在带新人和做模拟面试复盘时见过太多次几乎成了窗口函数类题目的标配。它有迷惑性题面一句话很多人第一反应是 group by 一下 count 就完事真动手写才发现自己根本分不清“连续”两个字该怎么落到 SQL 里。这篇就把这道题从思路到代码、从方言差异到踩坑排查完整拆一遍顺带把面试官最爱追问的几个变体一起讲了。适合正在准备 SQL 面试的同学也适合每天跟数据打交道但一直靠“感觉写 SQL”的分析同学——不管你是刚学完窗口函数还是能写 join 但一遇到“连续”就卡壳读完应该能自己从头推一遍。1. 先搞清楚这道题在考什么1.1 题面复述与字段假设题面通常长这样有一张用户登录记录表包含用户 ID 和登录时间求每个用户的最大连续登录天数。字段就两个一个标识用户一个标识登录发生的日期或时间戳。表名随便叫我习惯叫user_login两个字段user_id和login_date或者login_time。看着简单但真正下手前必须先确认三件事这三件事决定了你后面 SQL 的写法也决定了面试官对你的第一印象。第一表的粒度是什么是一条登录流水一天可能好几条还是一天一个用户只有一条快照第二时间字段带不带时分秒如果是datetime类型直接拿来做日期减法结果会离谱到你自己都不信。第三连续的定义是自然日连续还是允许中间空一天绝大多数场景都是自然日连续也就是 1 号、2 号、3 号算连续1 号、3 号不算。这三个问题你不问闷头写很可能写出来的 SQL 逻辑上没错但业务上全错。我在实际项目里就吃过这个亏一张埋点流水表一个用户一天能产生十几条登录记录我直接ROW_NUMBER()编号结果同一批日期被拆成好几组最后算出来的“最大连续天数”永远是 1。后来加了DISTINCT才正常。提示面试时哪怕不确定也要主动说明“我假设这张表是登录流水一天可能多条所以我会先去重”。这一句话的分量比后面写十行 SQL 都重。1.2 面试官真正想看到的三个能力这道题之所以经典是因为它一层套一层能同时考察三个不同维度的能力而且从易到难回答得越深越能拉开差距。第一层是对窗口函数的掌握程度。你会不会用ROW_NUMBER()知不知道PARTITION BY和ORDER BY分别管什么能不能在子查询里把窗口函数的结果引出来继续用。这是基本功写不出来基本就废了。第二层是把业务语言翻译成数据结构的能力。“连续”这个词在 SQL 里没有直接对应物你必须自己造一个东西去承载它。这道题的精髓就在于连续日期减去递增序号会得到同一个值。这个转换不是背下来的是推出来的。能自己想出这条路的人后面遇到“连续签到”“连续下单”“连续达标”全都能套。第三层是边界意识和工程习惯。数据去重做了没有时间字段截断了没有只有一个用户只有一天登录的情况处理了没有表大了索引建在哪。这些东西面试官不会明说但会通过你的 SQL 看出来。1.3 为什么它比“取 TopN”难一个量级GROUP BY加ORDER BY加LIMIT的 TopN 题本质是排序和截断逻辑是线性的脑子里过一遍就出来了。连续天数题不一样它的难点在于分组键不是现成的字段。平时我们GROUP BY的都是表里真实存在的列比如按城市分组、按月份分组。但“连续”这个性质表里没有哪一列叫“这是第几段连续”。你必须用现有的列组合算出一个能代表“同一段连续”的标识自己把这个虚拟的分组键造出来然后再去GROUP BY它。更麻烦的是这个标识还得保证同一段连续里的所有行标识相同不同段之间标识不同。稍微错一点要么两段被合并成一段要么一段被拆成好几段。这就是很多人写完跑一遍发现数字不对、又不知道错在哪的原因——因为没有中间结果可视化光看最终的数字根本看不出分组有没有分对。所以我在教别人的时候一直强调这道题一定要先在纸上手推一遍小数据把中间每一步的结果都写出来再去写 SQL。手推过一次你对分组键的理解就是实打实的不是死记硬背。2. 核心解法日期差值分组法2.1 一句话把原理讲透假设把每个用户的登录日期按从小到大排好序依次编号为 1、2、3、4……如果这个用户连着登录了三天那么这三天的日期分别是 D、D1、D2编号是 1、2、3。现在做个减法D 减 1 等于 D-1D1 减 2 也等于 D-1D2 减 3 还是 D-1。三个结果完全一样。换成不连续的情况比如 D、D1、D3编号 1、2、3。减完之后是 D-1、D-1、D。前两个相等第三个不一样自然就分成了两组。这就是全部的秘密连续日期的日期差恒为 1编号的增量也恒为 1两者相减差值恒定一旦中间断了一天日期差变成 2编号增量还是 1差值就变了。所以登录日期 - 序号这个结果相同就代表它们属于同一段连续登录。反过来用登录日期 序号也行只是分组的键值方向不一样逻辑完全对称。还有人喜欢用DATEDIFF(登录日期, 某个固定基准日) - 序号把日期运算降级成整数运算性能上更稳后面性能那一节我会细说。这个思路有个专门的名字业内常叫“日期差值分组法”或者“Gaps and Islands”岛屿就是连续段间隙就是断开的地方。名字不重要能自己推出来才重要。2.2 用一张小表手工推一遍光讲道理容易飘拿数据走一遍最踏实。假设用户 1001 的登录日期是 2024-01-01、01-02、01-03、01-05、01-06用户 1002 只有一天 2024-01-02。user_idlogin_daternlogin_date - rn 天分组10012024-01-0112023-12-31A10012024-01-0222023-12-31A10012024-01-0332023-12-31A10012024-01-0542024-01-01B10012024-01-0652024-01-01B10022024-01-0212024-01-01A看第三列和第四列rn是每个用户各自从 1 开始编的所以PARTITION BY user_id不能省。用户 1001 得到两组A 组 3 行B 组 2 行。用户 1002 只有一组 1 行。最后按user_id聚合取MAX(组内行数)1001 得到 31002 得到 1。结果正确。这里有个细节值得注意用户 1002 的分组键算出来是 2024-01-01恰好和用户 1001 的 B 组一样。所以最后的GROUP BY必须是(user_id, 分组键)这个组合不能只按分组键分组否则两个用户的数据会被串到一起。这是新手特别容易犯的错写完发现结果条数比用户数还少八成就是这里漏了user_id。2.3 完整 SQL 与逐层拆解上面推演的逻辑翻译成 MySQL 8 的写法就是这样WITH dedup AS ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login ), ranked AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ), grouped AS ( SELECT user_id, DATE_SUB(login_date, INTERVAL rn DAY) AS grp, COUNT(*) AS continuous_days FROM ranked GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ) SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM grouped GROUP BY user_id;这段我拆成四层来看每一层只干一件事这样写的好处是调试的时候可以把任何一层单独拎出来跑看中间结果对不对。第一层dedup把login_time截断成login_date并且去重。这一步绝对不能省。如果你的表已经是“一天一行”的日活快照DISTINCT属于冗余但无害如果是流水表没有DISTINCT后面全盘皆错。DATE()函数负责把2024-01-01 08:30:15和2024-01-01 21:10:02都变成2024-01-01这两个是同一天必须合并。第二层ranked用ROW_NUMBER()给每个用户按日期升序编号。这里必须用ROW_NUMBER()不能换成RANK()或DENSE_RANK()。因为去重之后日期本身不会重复三个函数结果一样但如果哪天你忘了去重RANK遇到重复日期会给相同的号后面的减法就全乱了。养成用ROW_NUMBER()的习惯更保险。第三层grouped算出分组键并按(user_id, grp)聚合计数。注意GROUP BY后面我写的是完整表达式DATE_SUB(login_date, INTERVAL rn DAY)而不是别名grp因为 MySQL 的分组阶段可能还没算完 SELECT 里的别名虽然大部分情况能用但不同版本和不同方言行为不一致写全表达式最稳。第四层做最终聚合取每个用户的最大值。这一层还可以顺手加上排序、过滤、去重统计后面变体题里会说。3. 实操从建表到跑出结果3.1 建表与造测试数据写这种题的 SQL一定要有能验证的数据。我习惯自己造一张小表覆盖几种典型情况连续多天、中间断开、只有一天、同一天多条记录、跨月、跨年。CREATE TABLE user_login ( user_id INT NOT NULL, login_time DATETIME NOT NULL, KEY idx_user_time (user_id, login_time) ); INSERT INTO user_login (user_id, login_time) VALUES (1001, 2024-01-01 09:12:33), (1001, 2024-01-02 10:01:05), (1001, 2024-01-03 08:45:11), (1001, 2024-01-05 20:30:00), (1001, 2024-01-06 21:15:42), (1001, 2024-01-06 23:59:59), -- 同一天第二条用于验证去重 (1002, 2024-01-02 11:00:00), (1003, 2024-01-30 12:00:00), (1003, 2024-01-31 12:00:00), (1003, 2024-02-01 12:00:00), (1003, 2024-02-02 12:00:00), -- 跨月连续 (1004, 2023-12-30 08:00:00), (1004, 2023-12-31 08:00:00), (1004, 2024-01-01 08:00:00); -- 跨年连续这批数据的设计意图很明确。用户 1001 有两条同一天记录用来验证去重有没有生效正确答案应该是 31、2、3 号如果算出来是 4 或 5说明去重漏了。用户 1002 只有一天边界情况答案 1。用户 1003 跨了月份正确答案 4如果DATE_SUB在跨月时算错这里会露馅。用户 1004 跨年正确答案 3。期望结果user_idmax_continuous_days10013100211003410043注意造数据时把“同一天多条”和“跨年跨月”这两种情况一定要放进去。我见过不少人 SQL 写得其实没错但因为测试数据太干净漏了去重上生产才发现数字不对。3.2 第一步把时间粒度对齐DATE(login_time)这步的目的是把时间戳降维到天。为什么必须做因为DATE_SUB对DATETIME做减法时时间部分会被保留下来。2024-01-01 09:12:33减 1 天得到2023-12-31 09:12:332024-01-02 10:01:05减 2 天得到2023-12-31 10:01:05两者看着像但根本不是同一个值分组会失败。这个问题在 MySQL 里特别隐蔽因为你肉眼扫一眼结果集看到都是 2023-12-31 开头以为一样实际上时分秒不同。所以哪怕字段类型明确是DATE我也会习惯性套一层DATE()成本几乎为零收益是防呆。在 Hive 或 Spark SQL 里日期字段如果存成字符串yyyy-MM-dddate_sub会返回字符串格式还是yyyy-MM-dd这时候字符串直接比较也能分组但一定要确认字符串是补零的标准格式2024-1-1这种不补零的格式排序和比较都会出错。遇到这种脏数据先date_format(to_date(login_time, yyyy-M-d), yyyy-MM-dd)洗一遍。3.3 第二步编号与分组键生成编号这一步有个小选择ROW_NUMBER()从 1 开始还是从 0 开始其实无所谓只要全程一致就行。从 1 开始意味着减完之后分组键是“第一天日期的前一天”从 0 开始分组键就是“第一天日期本身”。我个人喜欢从 1 开始因为分组键的语义是“这一段连续开始之前的那一天”描述起来更自然——连续段的起点是分组键加一天。分组键的类型也要留意。MySQL 里DATE_SUB(date, INTERVAL n DAY)返回的是DATE类型可以直接GROUP BY。PostgreSQL 里date - int返回date也没问题。但如果你用的是DATEDIFF - rn这种整数方案分组键就变成整数聚合出来的结果一样只是可读性差一点。我个人更推荐整数方案原因是它的方言兼容性好而且不受日期函数行为差异影响。写法是先把日期转成一个自纪元起的整数天序号再减rn-- MySQL DATEDIFF(login_date, 1970-01-01) - rn AS grp_num -- Hive / Spark datediff(login_date, 1970-01-01) - rn AS grp_num这样得到的grp_num是个整数GROUP BY它就行。好处是不同数据库的DATEDIFF参数顺序不一样但至少返回值都是整数比日期加减的可控性强。3.4 第三步聚合与取最大值到这里其实已经水到渠成了。第三层按(user_id, grp)计数第四层按user_id取MAX。有个小优化点如果你的目标只是“最大值”中间层可以不输出连续天数直接输出行数但统计口径上COUNT(*)和COUNT(DISTINCT login_date)在这一层是等价的因为前面已经去重过了。用COUNT(*)更快因为没有去重的开销。如果你的需求是“输出每个用户的连续登录区间明细”那就把MIN(login_date)和MAX(login_date)一起带上这个后面变体题里讲。还有个容易忽略的地方如果某个用户在你的统计周期内一次都没登录他压根不会出现在结果里。如果业务方要的是“所有用户的最大连续天数没登录的记为 0”你就得左连接用户维度表COALESCE兜底成 0。这种口径问题一定要跟需求方确认清楚别自己拍脑袋。3.5 四种方言的改写对照同一个逻辑换到不同数据库主要是日期加减函数不一样。我把常见几种整理成一张对照表写的时候直接查数据库日期减天数写法注意事项MySQL 8DATE_SUB(d, INTERVAL n DAY)n为表达式时部分版本需CAST(n AS SIGNED)PostgreSQLd - n::introw_number()返回 bigint必须显式转 intSQL ServerDATEADD(DAY, -n, d)减号要写在数字上函数本身是加Hive / Sparkdate_sub(d, n)返回字符串格式yyyy-MM-ddOracled - ndate类型直接减整数即可再说方言的方言写法PostgreSQL 版本完整版WITH dedup AS ( SELECT DISTINCT user_id, login_time::date AS login_date FROM user_login ), ranked AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ), grouped AS ( SELECT user_id, login_date - rn::int AS grp, COUNT(*) AS days FROM ranked GROUP BY user_id, login_date - rn::int ) SELECT user_id, MAX(days) AS max_continuous_days FROM grouped GROUP BY user_id;Hive / Spark 版本注意子查询嵌套要保证每层都有别名SELECT user_id, MAX(days) AS max_continuous_days FROM ( SELECT user_id, grp, COUNT(*) AS days FROM ( SELECT user_id, login_date, date_sub(login_date, rn) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT DISTINCT user_id, to_date(login_time) AS login_date FROM user_login ) t1 ) t2 ) t3 GROUP BY user_id, grp ) t4 GROUP BY user_id;SQL Server 版本要额外注意日期类型如果字段是DATETIMECAST(... AS DATE)截断后再参与运算WITH dedup AS ( SELECT DISTINCT user_id, CAST(login_time AS DATE) AS login_date FROM user_login ), ranked AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ), grouped AS ( SELECT user_id, DATEADD(DAY, -rn, login_date) AS grp, COUNT(*) AS days FROM ranked GROUP BY user_id, DATEADD(DAY, -rn, login_date) ) SELECT user_id, MAX(days) AS max_continuous_days FROM grouped GROUP BY user_id;写多方言的时候有个小技巧先在纸上把逻辑画清楚再逐句翻译。别一边想逻辑一边想函数名很容易乱。逻辑熟了之后翻译就是查函数表的事。4. 没有窗口函数怎么办4.1 MySQL 5.7 的变量法很多老系统还在 MySQL 5.7没有窗口函数面试也会专门问你“不用窗口函数怎么写”。这时候就得靠用户变量手动维护状态。思路是排序后逐行扫描如果当前行的用户和上一行相同并且当前日期减上一行日期等于 1那就延续上一条的连续计数加 1否则重置为 1。最后再取最大值。SELECT user_id, MAX(cnt) AS max_continuous_days FROM ( SELECT user_id, DATE_SUB(login_date, INTERVAL rn DAY) AS grp, COUNT(*) AS cnt FROM ( SELECT user_id, login_date, rn : IF(prev_uid user_id AND DATEDIFF(login_date, prev_date) 1, rn 1, 1) AS rn, prev_uid : user_id AS _u, prev_date : login_date AS _d FROM ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login ORDER BY user_id, login_date ) t, (SELECT rn : 0, prev_uid : NULL, prev_date : NULL) vars ) a GROUP BY user_id, grp ) b GROUP BY user_id;这段代码有两个关键点必须说清楚不然抄过去跑不通。第一子查询里的ORDER BY不能省。变量法是逐行顺序敏感的如果输入顺序乱了prev_uid和prev_date拿到的就是错乱的前一行整个逻辑崩掉。虽然官方文档一直说ORDER BY在子查询里的行为不保证但实践中绝大多数版本都能生效真要保险的话可以在最外层再包一层排序。第二变量赋值的顺序在同一行内是从左到右生效的。rn这一列先算用的是上一行留下的prev_uid和prev_date算完之后prev_uid和prev_date才被更新成当前行。所以判断和更新必须写成两个独立的列绝对不能合并。我见过有人把prev_date : login_date塞进IF里面结果判断永远用错值。变量法还有个隐患它依赖参数初始化用连接池的环境下如果上一次查询的变量值残留下来可能出诡异结果。所以初始化那一行(SELECT rn : 0, ...)一定要带上不能嫌麻烦。4.2 自连接法及其代价还有一种更朴素的路子自连接。把表跟自己按用户关联找到所有比当前行日期大的行然后判断这些行是不是连续的。但“连续”这个条件用 join 表达非常别扭你需要找“刚好比当前大的那一天”是否存在然后再向前推。实际写起来是这样先给每行找出同用户下一天如果存在就说明这一天和上一天连续然后一路链下去。这条路需要递归 CTE 或者多轮迭代代码长、易错、性能差除了特定场景基本不推荐。另一种思路是用“连续段的起点”来标记某一行如果它的前一天没有登录记录它就是某段连续的起点。然后每行向下找最近的起点就能归属到某一段。WITH d AS ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login ), flag AS ( SELECT user_id, login_date, CASE WHEN EXISTS ( SELECT 1 FROM d d2 WHERE d2.user_id d.user_id AND d2.login_date d.login_date - INTERVAL 1 DAY ) THEN 0 ELSE 1 END AS is_start FROM d ) SELECT * FROM flag;这段能标出每段连续的起点但它用了相关子查询每行都要回表扫一次大表上性能灾难。所以这个方法更适合用来理解问题不适合用来解决问题。面试的时候如果你能说出“我可以用起点标记法来理解但实现上我会用差值分组”反而是个加分项说明你想过不止一条路。4.3 几种方案的横向对比方案可读性性能方言依赖适用场景日期差值分组 窗口函数高好需支持窗口函数首选方案整数差值分组 窗口函数中高好需支持窗口函数跨库迁移时更稳用户变量法低好MySQL 专有5.7 及更早版本相关子查询标记起点中差通用理解原理、小数据递归 CTE中一般需支持 CTE需要输出完整链路时我的建议是新版本数据库一律用窗口函数方案代码短、可读性好、优化器也认。老系统才考虑变量法。至于自连接和递归除非需求特殊否则别碰——写出来的 SQL 三个月后自己都看不懂。5. 面试官最爱的几个追问变体5.1 求每段连续登录的起止日期原题只要最大值追问一句“把每段连续的起止日期也列出来”很多人就得改结构了。其实改动很小把第三层的聚合函数从COUNT(*)换成COUNT(*)加MIN(login_date)加MAX(login_date)就行。SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ranked GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ORDER BY user_id, start_date;注意ranked是前面第二层的子查询直接复用它就行。这种“一段一条记录”的输出在业务上特别有用比如做用户活跃度分析时你想知道某个用户最长的那段活跃期具体是哪几天就得这么查。还可以加个HAVING COUNT(*) 3过滤掉短段只留长连续。如果要给每段编个号可以再套一层ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY start_date)得到“该用户的第几段连续”。5.2 找出连续 N 天以上的用户这个变体是把“最大值”变成“阈值过滤”。有两种写法各有优劣。第一种是在原结构外层加HAVINGSELECT user_id, MAX(continuous_days) AS max_continuous_days FROM grouped GROUP BY user_id HAVING MAX(continuous_days) 7;第二种是先在分段层面过滤再去重用户SELECT DISTINCT user_id FROM grouped WHERE continuous_days 7;这两种结果不完全一样。第一种要求“最长的那段 ≥ 7”第二种要求“存在任意一段 ≥ 7”。如果用户只有一段两者等价如果用户有多段第一种更严格。面试时把这两个口径点出来说明你考虑过语义差异比单纯写出 SQL 更能加分。顺便说个实际的坑连续 7 天这个阈值要不要包含当天要不要按自然周算这些都得跟需求方对齐。我遇到过一次业务说的是“连续 7 天登录”结果他们实际想要的是“7 天内每天都有登录”完全不是一回事——后者只看一个固定窗口内的覆盖情况用COUNT(DISTINCT login_date) 7就行根本不需要连续逻辑。所以听到“连续”两个字先反问清楚。5.3 当前连续登录天数怎么算“最大连续”是历史最值“当前连续”是截至今天或者截至数据最新日期还没断的那一段两个概念完全不同。算法是先确定截止日期然后只保留最后一段。一个实用的写法是算出每段连续后用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY end_date DESC)找最后一段然后判断这一段的结束日期是否等于截止日期。WITH seg AS ( SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days FROM ranked GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ), last_seg AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY end_date DESC) AS rk FROM seg ) SELECT user_id, CASE WHEN end_date (SELECT MAX(login_date) FROM dedup) THEN days ELSE 0 END AS current_continuous_days FROM last_seg WHERE rk 1;这里的截止日期取的是全表的最大登录日期这是常见做法避免因为数据延迟导致所有人都被判成“断了”。如果你的业务是按“今天”算那就把截止日期换成CURRENT_DATE但要提前跟需求方确认好因为数据同步有延迟的时候这个口径会让人很困惑。5.4 断点识别找出中断的那一天还有个反向问法“找出每个用户登录中断的日期”。这在流失预警场景里很实用——你想知道用户是哪天开始不来了。思路是把连续段的结束日期拿到然后加一天就是中断的那一天。但要注意排除两种情况一是这个用户之后再也没有登录过那不是中断是彻底流失二是这段结束日期已经是数据里的最后一天。WITH seg AS ( SELECT user_id, MAX(login_date) AS end_date FROM ranked GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ) SELECT s.user_id, DATE_ADD(s.end_date, INTERVAL 1 DAY) AS break_date FROM seg s WHERE EXISTS ( SELECT 1 FROM ranked r WHERE r.user_id s.user_id AND r.login_date s.end_date );EXISTS那一段的作用是确认“后面还有登录”只有后面还有记录这个end_date加一天才算真正的中断点。没有这个条件的版本会把流失用户也算进去结果就是每个人都会被标一个中断日期全是噪音。6. 踩坑记录与排查清单6.1 去重没做全盘皆错这是最高频的错误没有之一。流水表里一个用户一天登录多次如果直接编号同一天的几条记录会拿到不同的rn算出来的分组键各不相同于是每天都被算成独立的一段最大值永远是 1。排查方法很简单先跑一句SELECT user_id, login_date, COUNT(*) FROM user_login GROUP BY user_id, login_date HAVING COUNT(*) 1如果有结果说明必须先DISTINCT。养成习惯拿到登录表第一件事就是查粒度别急着写主体逻辑。还有一种隐蔽情况表里有设备维度或者登录渠道维度同一个用户同一天在不同设备上登录就有多条。这时候DISTINCT user_id, login_date依然能去重但如果SELECT里没写全维度DISTINCT的效果就不对。所以DISTINCT后面跟的列要仔细核对只留user_id和日期两列别把其他列带进来。6.2 时间带时分秒导致分组失败前面提过一次这里再说透。DATE_SUB作用在DATETIME上时间部分会原样保留。比如2024-01-01 09:12:33和2024-01-02 08:00:00前者减 1 天是2023-12-31 09:12:33后者减 2 天是2023-12-31 08:00:00。两个值看起来都在 12-31实际不相等分组直接裂开。这个错误的迷惑性在于结果不会报错只是数字偏小。你看着结果觉得不对劲又没有明显线索。所以我建议在写的时候就用DATE()包一层把类型强制对齐从源头上断掉这种可能。如果字段类型是字符串情况更复杂。2024-01-01 09:12:33这种字符串做日期运算MySQL 会隐式转换某些情况下能跑通遇到格式不标准的就直接返回NULL。NULL参与分组会聚成一堆结果扑朔迷离。所以字符串日期字段一律先STR_TO_DATE转成日期类型再运算。6.3 排序键和分区键写反ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)这两个位置很容易写反。写反了不会报错但结果毫无意义——相当于按日期分组、按用户编号编号在同一个日期内递增跟连续登录一点关系都没有。这个错误识别起来有个窍门如果结果的用户数远小于表里的用户数或者最大值大得离谱多半是这两个写反了。因为PARTITION BY写错会导致跨用户编号编号值普遍偏大减完之后的分组号也跟着乱。正确的记忆方式是PARTITION BY后面跟“你要分组的主体”这里就是用户ORDER BY后面跟“决定先后的时间维度”这里就是日期。念一遍语义“在同一个用户内按日期顺序编号”就对了。6.4 空值、单天、边界用户边界情况不处理结果会少人或者多算。几个必须检查的点login_date为NULL的记录参与ROW_NUMBER()排序时会被排在最前或者最后取决于数据库实现减完之后分组键也是NULL聚合时COUNT(*)还是能算出数字但语义上一堆NULL被当成连续是纯脏数据。所以前面要加WHERE login_time IS NOT NULL。只有一天登录的用户编号是 1减完得到前一天GROUP BY后COUNT(*)是 1MAX是 1逻辑上没问题。但如果你在某个环节加了HAVING COUNT(*) 1之类的过滤这类用户就被过滤掉了最后结果里查无此人。需求如果是“输出所有用户”这个过滤就不能加。还有一种情况是用户 ID 为0或者空字符串通常是埋点异常或者未登录访客。这类数据要不要算进“用户”得跟业务确认。我一般会在WHERE里加user_id 0排除掉避免污染结果。6.5 大表上的性能问题小表怎么写都行几千万行的登录表就得想想了。几个实用的优化点一是先过滤再分组。如果只需要最近 90 天的数据WHERE要写在最里层子查询别等到最外层才过滤。窗口函数的计算开销和参与计算的数据量成正比早过滤能省不少。二是索引要建对。(user_id, login_date)这个联合索引能覆盖DISTINCT和排序两个操作让执行计划走索引扫描而不是全表扫描。如果字段是DATETIME索引上存的是完整时间戳DATE()包裹后索引会失效可以用login_time 2024-01-01 AND login_time 2024-01-31这种范围条件来保住索引。三是大表分段处理。用户量太大时可以按user_id取模分批跑或者按天增量计算——新的一天数据来了只需要更新受影响的用户不用全量重算。增量计算的思路是维护一张“当前连续天数”的结果表每天跑一次如果今天有登录就加 1没有就归零。这个方案在数据量大、实时性要求高的场景里更实用。四是别用SELECT *。窗口函数需要物化中间结果SELECT *会把没用的列全部带进中间层内存和 IO 都浪费。只SELECT需要的列能省很多。7. 我在面试和带新人时的几点体会7.1 先说思路再写代码这道题真正拉开差距的地方不在代码在前三十秒。如果你上来就敲键盘写一半卡住再回头改观感会很差。比较稳的节奏是先说表结构和粒度假设再说“连续日期减递增序号得到相同分组键”这个核心思路然后问一句“我可以用窗口函数吗”得到肯定的答复再动手。说思路的时候如果能主动画一下那三四行小数据的推演效果会非常好。因为面试官能立刻判断出你是真理解了还是在背模板。我见过不少人 SQL 写得对但让他解释为什么这么做就说不清楚这种情况在需要现场改需求的环节会非常吃亏。还有一个细节写的时候把 SQL 分层写每层加个注释。哪怕面试是在纸上写分行清晰、缩进规范给人的印象也完全不一样。代码的可读性本身就是能力的一部分。7.2 追问环节提前准备这道题的追问基本就那几类提前想清楚能省很多现场思考时间。常见的是不用窗口函数怎么写、表里一天多条怎么办、要输出起止日期怎么改、连续 7 天以上的用户怎么筛、怎么算当前连续天数、数据量太大怎么办。每一类我都建议准备一个能说出口的答案不需要背代码但思路要清楚。比如“不用窗口函数”那题你至少要能说出“用变量按顺序扫描逐行维护连续计数器”这个方向具体语法现场查也行但方向说错就废了。还有一类追问是关于口径的连续的定义包不包含周末、跨年怎么算、用户当天注册但没登录算不算。这些没有标准答案面试官想看的是你会不会主动澄清需求而不是自己假设一个口径闷头写。7.3 我自己的练习方法最后说点实际练的办法。这道题我建议至少手写三遍第一遍照着思路写写完不看答案自己跑数据验证第二遍隔一天关掉所有参考纯手写第三遍换个方言写比如从 MySQL 换到 Hive 或 PostgreSQL。第三遍特别有价值因为方言差异会逼你重新审视每一行的意图而不是机械记忆。比如 PostgreSQL 里date - integer的写法会让你真正意识到“日期减整数”这个操作的本质而不是只记住DATE_SUB这个函数名。另外强烈建议把几个变体都跑一遍尤其是“当前连续天数”和“断点识别”。这两个变体在实际工作里的出现频率比原题还高而且思路稍微拐了个弯练过之后对窗口函数的理解会上一个台阶。我自己带新人的时候一般会让他把这张表自己造一遍故意造出同一天多条、跨月、跨年、只有一天这几种情况。造数据的过程就是梳理边界的过程比直接给一份干净数据让他写效果好太多。数据造完、SQL 跑通、结果对得上期望值这道题才算真的过了。后续想继续深挖的话可以往这几个方向走把连续登录扩展成“连续满足某个条件”比如连续 3 天订单金额大于 100逻辑框架完全一样只是把日期列换成条件标记列或者研究一下“会话切分”本质也是连续性问题只是判断连续的依据从日期变成了时间间隔阈值。这些在用户行为分析里都是高频需求一通百通。 
长河君 · 2026/11/2 12:39:08 · 阅读 1.2k SQL最大连续登录天数：窗口函数与日期差值分组法
“最大连续登录天数”这道 SQL 题我在带新人和做模拟面试复盘时见过太多次几乎成了窗口函数类题目的标配。它有迷惑性题面一句话很多人第一反应是 group by 一下 count 就完事真动手写才发现自己根本分不清“连续”两个字该怎么落到 SQL 里。这篇就把这道题从思路到代码、从方言差异到踩坑排查完整拆一遍顺带把面试官最爱追问的几个变体一起讲了。适合正在准备 SQL 面试的同学也适合每天跟数据打交道但一直靠“感觉写 SQL”的分析同学——不管你是刚学完窗口函数还是能写 join 但一遇到“连续”就卡壳读完应该能自己从头推一遍。1. 先搞清楚这道题在考什么1.1 题面复述与字段假设题面通常长这样有一张用户登录记录表包含用户 ID 和登录时间求每个用户的最大连续登录天数。字段就两个一个标识用户一个标识登录发生的日期或时间戳。表名随便叫我习惯叫user_login两个字段user_id和login_date或者login_time。看着简单但真正下手前必须先确认三件事这三件事决定了你后面 SQL 的写法也决定了面试官对你的第一印象。第一表的粒度是什么是一条登录流水一天可能好几条还是一天一个用户只有一条快照第二时间字段带不带时分秒如果是datetime类型直接拿来做日期减法结果会离谱到你自己都不信。第三连续的定义是自然日连续还是允许中间空一天绝大多数场景都是自然日连续也就是 1 号、2 号、3 号算连续1 号、3 号不算。这三个问题你不问闷头写很可能写出来的 SQL 逻辑上没错但业务上全错。我在实际项目里就吃过这个亏一张埋点流水表一个用户一天能产生十几条登录记录我直接ROW_NUMBER()编号结果同一批日期被拆成好几组最后算出来的“最大连续天数”永远是 1。后来加了DISTINCT才正常。提示面试时哪怕不确定也要主动说明“我假设这张表是登录流水一天可能多条所以我会先去重”。这一句话的分量比后面写十行 SQL 都重。1.2 面试官真正想看到的三个能力这道题之所以经典是因为它一层套一层能同时考察三个不同维度的能力而且从易到难回答得越深越能拉开差距。第一层是对窗口函数的掌握程度。你会不会用ROW_NUMBER()知不知道PARTITION BY和ORDER BY分别管什么能不能在子查询里把窗口函数的结果引出来继续用。这是基本功写不出来基本就废了。第二层是把业务语言翻译成数据结构的能力。“连续”这个词在 SQL 里没有直接对应物你必须自己造一个东西去承载它。这道题的精髓就在于连续日期减去递增序号会得到同一个值。这个转换不是背下来的是推出来的。能自己想出这条路的人后面遇到“连续签到”“连续下单”“连续达标”全都能套。第三层是边界意识和工程习惯。数据去重做了没有时间字段截断了没有只有一个用户只有一天登录的情况处理了没有表大了索引建在哪。这些东西面试官不会明说但会通过你的 SQL 看出来。1.3 为什么它比“取 TopN”难一个量级GROUP BY加ORDER BY加LIMIT的 TopN 题本质是排序和截断逻辑是线性的脑子里过一遍就出来了。连续天数题不一样它的难点在于分组键不是现成的字段。平时我们GROUP BY的都是表里真实存在的列比如按城市分组、按月份分组。但“连续”这个性质表里没有哪一列叫“这是第几段连续”。你必须用现有的列组合算出一个能代表“同一段连续”的标识自己把这个虚拟的分组键造出来然后再去GROUP BY它。更麻烦的是这个标识还得保证同一段连续里的所有行标识相同不同段之间标识不同。稍微错一点要么两段被合并成一段要么一段被拆成好几段。这就是很多人写完跑一遍发现数字不对、又不知道错在哪的原因——因为没有中间结果可视化光看最终的数字根本看不出分组有没有分对。所以我在教别人的时候一直强调这道题一定要先在纸上手推一遍小数据把中间每一步的结果都写出来再去写 SQL。手推过一次你对分组键的理解就是实打实的不是死记硬背。2. 核心解法日期差值分组法2.1 一句话把原理讲透假设把每个用户的登录日期按从小到大排好序依次编号为 1、2、3、4……如果这个用户连着登录了三天那么这三天的日期分别是 D、D1、D2编号是 1、2、3。现在做个减法D 减 1 等于 D-1D1 减 2 也等于 D-1D2 减 3 还是 D-1。三个结果完全一样。换成不连续的情况比如 D、D1、D3编号 1、2、3。减完之后是 D-1、D-1、D。前两个相等第三个不一样自然就分成了两组。这就是全部的秘密连续日期的日期差恒为 1编号的增量也恒为 1两者相减差值恒定一旦中间断了一天日期差变成 2编号增量还是 1差值就变了。所以登录日期 - 序号这个结果相同就代表它们属于同一段连续登录。反过来用登录日期 序号也行只是分组的键值方向不一样逻辑完全对称。还有人喜欢用DATEDIFF(登录日期, 某个固定基准日) - 序号把日期运算降级成整数运算性能上更稳后面性能那一节我会细说。这个思路有个专门的名字业内常叫“日期差值分组法”或者“Gaps and Islands”岛屿就是连续段间隙就是断开的地方。名字不重要能自己推出来才重要。2.2 用一张小表手工推一遍光讲道理容易飘拿数据走一遍最踏实。假设用户 1001 的登录日期是 2024-01-01、01-02、01-03、01-05、01-06用户 1002 只有一天 2024-01-02。user_idlogin_daternlogin_date - rn 天分组10012024-01-0112023-12-31A10012024-01-0222023-12-31A10012024-01-0332023-12-31A10012024-01-0542024-01-01B10012024-01-0652024-01-01B10022024-01-0212024-01-01A看第三列和第四列rn是每个用户各自从 1 开始编的所以PARTITION BY user_id不能省。用户 1001 得到两组A 组 3 行B 组 2 行。用户 1002 只有一组 1 行。最后按user_id聚合取MAX(组内行数)1001 得到 31002 得到 1。结果正确。这里有个细节值得注意用户 1002 的分组键算出来是 2024-01-01恰好和用户 1001 的 B 组一样。所以最后的GROUP BY必须是(user_id, 分组键)这个组合不能只按分组键分组否则两个用户的数据会被串到一起。这是新手特别容易犯的错写完发现结果条数比用户数还少八成就是这里漏了user_id。2.3 完整 SQL 与逐层拆解上面推演的逻辑翻译成 MySQL 8 的写法就是这样WITH dedup AS ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login ), ranked AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ), grouped AS ( SELECT user_id, DATE_SUB(login_date, INTERVAL rn DAY) AS grp, COUNT(*) AS continuous_days FROM ranked GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ) SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM grouped GROUP BY user_id;这段我拆成四层来看每一层只干一件事这样写的好处是调试的时候可以把任何一层单独拎出来跑看中间结果对不对。第一层dedup把login_time截断成login_date并且去重。这一步绝对不能省。如果你的表已经是“一天一行”的日活快照DISTINCT属于冗余但无害如果是流水表没有DISTINCT后面全盘皆错。DATE()函数负责把2024-01-01 08:30:15和2024-01-01 21:10:02都变成2024-01-01这两个是同一天必须合并。第二层ranked用ROW_NUMBER()给每个用户按日期升序编号。这里必须用ROW_NUMBER()不能换成RANK()或DENSE_RANK()。因为去重之后日期本身不会重复三个函数结果一样但如果哪天你忘了去重RANK遇到重复日期会给相同的号后面的减法就全乱了。养成用ROW_NUMBER()的习惯更保险。第三层grouped算出分组键并按(user_id, grp)聚合计数。注意GROUP BY后面我写的是完整表达式DATE_SUB(login_date, INTERVAL rn DAY)而不是别名grp因为 MySQL 的分组阶段可能还没算完 SELECT 里的别名虽然大部分情况能用但不同版本和不同方言行为不一致写全表达式最稳。第四层做最终聚合取每个用户的最大值。这一层还可以顺手加上排序、过滤、去重统计后面变体题里会说。3. 实操从建表到跑出结果3.1 建表与造测试数据写这种题的 SQL一定要有能验证的数据。我习惯自己造一张小表覆盖几种典型情况连续多天、中间断开、只有一天、同一天多条记录、跨月、跨年。CREATE TABLE user_login ( user_id INT NOT NULL, login_time DATETIME NOT NULL, KEY idx_user_time (user_id, login_time) ); INSERT INTO user_login (user_id, login_time) VALUES (1001, 2024-01-01 09:12:33), (1001, 2024-01-02 10:01:05), (1001, 2024-01-03 08:45:11), (1001, 2024-01-05 20:30:00), (1001, 2024-01-06 21:15:42), (1001, 2024-01-06 23:59:59), -- 同一天第二条用于验证去重 (1002, 2024-01-02 11:00:00), (1003, 2024-01-30 12:00:00), (1003, 2024-01-31 12:00:00), (1003, 2024-02-01 12:00:00), (1003, 2024-02-02 12:00:00), -- 跨月连续 (1004, 2023-12-30 08:00:00), (1004, 2023-12-31 08:00:00), (1004, 2024-01-01 08:00:00); -- 跨年连续这批数据的设计意图很明确。用户 1001 有两条同一天记录用来验证去重有没有生效正确答案应该是 31、2、3 号如果算出来是 4 或 5说明去重漏了。用户 1002 只有一天边界情况答案 1。用户 1003 跨了月份正确答案 4如果DATE_SUB在跨月时算错这里会露馅。用户 1004 跨年正确答案 3。期望结果user_idmax_continuous_days10013100211003410043注意造数据时把“同一天多条”和“跨年跨月”这两种情况一定要放进去。我见过不少人 SQL 写得其实没错但因为测试数据太干净漏了去重上生产才发现数字不对。3.2 第一步把时间粒度对齐DATE(login_time)这步的目的是把时间戳降维到天。为什么必须做因为DATE_SUB对DATETIME做减法时时间部分会被保留下来。2024-01-01 09:12:33减 1 天得到2023-12-31 09:12:332024-01-02 10:01:05减 2 天得到2023-12-31 10:01:05两者看着像但根本不是同一个值分组会失败。这个问题在 MySQL 里特别隐蔽因为你肉眼扫一眼结果集看到都是 2023-12-31 开头以为一样实际上时分秒不同。所以哪怕字段类型明确是DATE我也会习惯性套一层DATE()成本几乎为零收益是防呆。在 Hive 或 Spark SQL 里日期字段如果存成字符串yyyy-MM-dddate_sub会返回字符串格式还是yyyy-MM-dd这时候字符串直接比较也能分组但一定要确认字符串是补零的标准格式2024-1-1这种不补零的格式排序和比较都会出错。遇到这种脏数据先date_format(to_date(login_time, yyyy-M-d), yyyy-MM-dd)洗一遍。3.3 第二步编号与分组键生成编号这一步有个小选择ROW_NUMBER()从 1 开始还是从 0 开始其实无所谓只要全程一致就行。从 1 开始意味着减完之后分组键是“第一天日期的前一天”从 0 开始分组键就是“第一天日期本身”。我个人喜欢从 1 开始因为分组键的语义是“这一段连续开始之前的那一天”描述起来更自然——连续段的起点是分组键加一天。分组键的类型也要留意。MySQL 里DATE_SUB(date, INTERVAL n DAY)返回的是DATE类型可以直接GROUP BY。PostgreSQL 里date - int返回date也没问题。但如果你用的是DATEDIFF - rn这种整数方案分组键就变成整数聚合出来的结果一样只是可读性差一点。我个人更推荐整数方案原因是它的方言兼容性好而且不受日期函数行为差异影响。写法是先把日期转成一个自纪元起的整数天序号再减rn-- MySQL DATEDIFF(login_date, 1970-01-01) - rn AS grp_num -- Hive / Spark datediff(login_date, 1970-01-01) - rn AS grp_num这样得到的grp_num是个整数GROUP BY它就行。好处是不同数据库的DATEDIFF参数顺序不一样但至少返回值都是整数比日期加减的可控性强。3.4 第三步聚合与取最大值到这里其实已经水到渠成了。第三层按(user_id, grp)计数第四层按user_id取MAX。有个小优化点如果你的目标只是“最大值”中间层可以不输出连续天数直接输出行数但统计口径上COUNT(*)和COUNT(DISTINCT login_date)在这一层是等价的因为前面已经去重过了。用COUNT(*)更快因为没有去重的开销。如果你的需求是“输出每个用户的连续登录区间明细”那就把MIN(login_date)和MAX(login_date)一起带上这个后面变体题里讲。还有个容易忽略的地方如果某个用户在你的统计周期内一次都没登录他压根不会出现在结果里。如果业务方要的是“所有用户的最大连续天数没登录的记为 0”你就得左连接用户维度表COALESCE兜底成 0。这种口径问题一定要跟需求方确认清楚别自己拍脑袋。3.5 四种方言的改写对照同一个逻辑换到不同数据库主要是日期加减函数不一样。我把常见几种整理成一张对照表写的时候直接查数据库日期减天数写法注意事项MySQL 8DATE_SUB(d, INTERVAL n DAY)n为表达式时部分版本需CAST(n AS SIGNED)PostgreSQLd - n::introw_number()返回 bigint必须显式转 intSQL ServerDATEADD(DAY, -n, d)减号要写在数字上函数本身是加Hive / Sparkdate_sub(d, n)返回字符串格式yyyy-MM-ddOracled - ndate类型直接减整数即可再说方言的方言写法PostgreSQL 版本完整版WITH dedup AS ( SELECT DISTINCT user_id, login_time::date AS login_date FROM user_login ), ranked AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ), grouped AS ( SELECT user_id, login_date - rn::int AS grp, COUNT(*) AS days FROM ranked GROUP BY user_id, login_date - rn::int ) SELECT user_id, MAX(days) AS max_continuous_days FROM grouped GROUP BY user_id;Hive / Spark 版本注意子查询嵌套要保证每层都有别名SELECT user_id, MAX(days) AS max_continuous_days FROM ( SELECT user_id, grp, COUNT(*) AS days FROM ( SELECT user_id, login_date, date_sub(login_date, rn) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT DISTINCT user_id, to_date(login_time) AS login_date FROM user_login ) t1 ) t2 ) t3 GROUP BY user_id, grp ) t4 GROUP BY user_id;SQL Server 版本要额外注意日期类型如果字段是DATETIMECAST(... AS DATE)截断后再参与运算WITH dedup AS ( SELECT DISTINCT user_id, CAST(login_time AS DATE) AS login_date FROM user_login ), ranked AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ), grouped AS ( SELECT user_id, DATEADD(DAY, -rn, login_date) AS grp, COUNT(*) AS days FROM ranked GROUP BY user_id, DATEADD(DAY, -rn, login_date) ) SELECT user_id, MAX(days) AS max_continuous_days FROM grouped GROUP BY user_id;写多方言的时候有个小技巧先在纸上把逻辑画清楚再逐句翻译。别一边想逻辑一边想函数名很容易乱。逻辑熟了之后翻译就是查函数表的事。4. 没有窗口函数怎么办4.1 MySQL 5.7 的变量法很多老系统还在 MySQL 5.7没有窗口函数面试也会专门问你“不用窗口函数怎么写”。这时候就得靠用户变量手动维护状态。思路是排序后逐行扫描如果当前行的用户和上一行相同并且当前日期减上一行日期等于 1那就延续上一条的连续计数加 1否则重置为 1。最后再取最大值。SELECT user_id, MAX(cnt) AS max_continuous_days FROM ( SELECT user_id, DATE_SUB(login_date, INTERVAL rn DAY) AS grp, COUNT(*) AS cnt FROM ( SELECT user_id, login_date, rn : IF(prev_uid user_id AND DATEDIFF(login_date, prev_date) 1, rn 1, 1) AS rn, prev_uid : user_id AS _u, prev_date : login_date AS _d FROM ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login ORDER BY user_id, login_date ) t, (SELECT rn : 0, prev_uid : NULL, prev_date : NULL) vars ) a GROUP BY user_id, grp ) b GROUP BY user_id;这段代码有两个关键点必须说清楚不然抄过去跑不通。第一子查询里的ORDER BY不能省。变量法是逐行顺序敏感的如果输入顺序乱了prev_uid和prev_date拿到的就是错乱的前一行整个逻辑崩掉。虽然官方文档一直说ORDER BY在子查询里的行为不保证但实践中绝大多数版本都能生效真要保险的话可以在最外层再包一层排序。第二变量赋值的顺序在同一行内是从左到右生效的。rn这一列先算用的是上一行留下的prev_uid和prev_date算完之后prev_uid和prev_date才被更新成当前行。所以判断和更新必须写成两个独立的列绝对不能合并。我见过有人把prev_date : login_date塞进IF里面结果判断永远用错值。变量法还有个隐患它依赖参数初始化用连接池的环境下如果上一次查询的变量值残留下来可能出诡异结果。所以初始化那一行(SELECT rn : 0, ...)一定要带上不能嫌麻烦。4.2 自连接法及其代价还有一种更朴素的路子自连接。把表跟自己按用户关联找到所有比当前行日期大的行然后判断这些行是不是连续的。但“连续”这个条件用 join 表达非常别扭你需要找“刚好比当前大的那一天”是否存在然后再向前推。实际写起来是这样先给每行找出同用户下一天如果存在就说明这一天和上一天连续然后一路链下去。这条路需要递归 CTE 或者多轮迭代代码长、易错、性能差除了特定场景基本不推荐。另一种思路是用“连续段的起点”来标记某一行如果它的前一天没有登录记录它就是某段连续的起点。然后每行向下找最近的起点就能归属到某一段。WITH d AS ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login ), flag AS ( SELECT user_id, login_date, CASE WHEN EXISTS ( SELECT 1 FROM d d2 WHERE d2.user_id d.user_id AND d2.login_date d.login_date - INTERVAL 1 DAY ) THEN 0 ELSE 1 END AS is_start FROM d ) SELECT * FROM flag;这段能标出每段连续的起点但它用了相关子查询每行都要回表扫一次大表上性能灾难。所以这个方法更适合用来理解问题不适合用来解决问题。面试的时候如果你能说出“我可以用起点标记法来理解但实现上我会用差值分组”反而是个加分项说明你想过不止一条路。4.3 几种方案的横向对比方案可读性性能方言依赖适用场景日期差值分组 窗口函数高好需支持窗口函数首选方案整数差值分组 窗口函数中高好需支持窗口函数跨库迁移时更稳用户变量法低好MySQL 专有5.7 及更早版本相关子查询标记起点中差通用理解原理、小数据递归 CTE中一般需支持 CTE需要输出完整链路时我的建议是新版本数据库一律用窗口函数方案代码短、可读性好、优化器也认。老系统才考虑变量法。至于自连接和递归除非需求特殊否则别碰——写出来的 SQL 三个月后自己都看不懂。5. 面试官最爱的几个追问变体5.1 求每段连续登录的起止日期原题只要最大值追问一句“把每段连续的起止日期也列出来”很多人就得改结构了。其实改动很小把第三层的聚合函数从COUNT(*)换成COUNT(*)加MIN(login_date)加MAX(login_date)就行。SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ranked GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ORDER BY user_id, start_date;注意ranked是前面第二层的子查询直接复用它就行。这种“一段一条记录”的输出在业务上特别有用比如做用户活跃度分析时你想知道某个用户最长的那段活跃期具体是哪几天就得这么查。还可以加个HAVING COUNT(*) 3过滤掉短段只留长连续。如果要给每段编个号可以再套一层ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY start_date)得到“该用户的第几段连续”。5.2 找出连续 N 天以上的用户这个变体是把“最大值”变成“阈值过滤”。有两种写法各有优劣。第一种是在原结构外层加HAVINGSELECT user_id, MAX(continuous_days) AS max_continuous_days FROM grouped GROUP BY user_id HAVING MAX(continuous_days) 7;第二种是先在分段层面过滤再去重用户SELECT DISTINCT user_id FROM grouped WHERE continuous_days 7;这两种结果不完全一样。第一种要求“最长的那段 ≥ 7”第二种要求“存在任意一段 ≥ 7”。如果用户只有一段两者等价如果用户有多段第一种更严格。面试时把这两个口径点出来说明你考虑过语义差异比单纯写出 SQL 更能加分。顺便说个实际的坑连续 7 天这个阈值要不要包含当天要不要按自然周算这些都得跟需求方对齐。我遇到过一次业务说的是“连续 7 天登录”结果他们实际想要的是“7 天内每天都有登录”完全不是一回事——后者只看一个固定窗口内的覆盖情况用COUNT(DISTINCT login_date) 7就行根本不需要连续逻辑。所以听到“连续”两个字先反问清楚。5.3 当前连续登录天数怎么算“最大连续”是历史最值“当前连续”是截至今天或者截至数据最新日期还没断的那一段两个概念完全不同。算法是先确定截止日期然后只保留最后一段。一个实用的写法是算出每段连续后用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY end_date DESC)找最后一段然后判断这一段的结束日期是否等于截止日期。WITH seg AS ( SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days FROM ranked GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ), last_seg AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY end_date DESC) AS rk FROM seg ) SELECT user_id, CASE WHEN end_date (SELECT MAX(login_date) FROM dedup) THEN days ELSE 0 END AS current_continuous_days FROM last_seg WHERE rk 1;这里的截止日期取的是全表的最大登录日期这是常见做法避免因为数据延迟导致所有人都被判成“断了”。如果你的业务是按“今天”算那就把截止日期换成CURRENT_DATE但要提前跟需求方确认好因为数据同步有延迟的时候这个口径会让人很困惑。5.4 断点识别找出中断的那一天还有个反向问法“找出每个用户登录中断的日期”。这在流失预警场景里很实用——你想知道用户是哪天开始不来了。思路是把连续段的结束日期拿到然后加一天就是中断的那一天。但要注意排除两种情况一是这个用户之后再也没有登录过那不是中断是彻底流失二是这段结束日期已经是数据里的最后一天。WITH seg AS ( SELECT user_id, MAX(login_date) AS end_date FROM ranked GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) ) SELECT s.user_id, DATE_ADD(s.end_date, INTERVAL 1 DAY) AS break_date FROM seg s WHERE EXISTS ( SELECT 1 FROM ranked r WHERE r.user_id s.user_id AND r.login_date s.end_date );EXISTS那一段的作用是确认“后面还有登录”只有后面还有记录这个end_date加一天才算真正的中断点。没有这个条件的版本会把流失用户也算进去结果就是每个人都会被标一个中断日期全是噪音。6. 踩坑记录与排查清单6.1 去重没做全盘皆错这是最高频的错误没有之一。流水表里一个用户一天登录多次如果直接编号同一天的几条记录会拿到不同的rn算出来的分组键各不相同于是每天都被算成独立的一段最大值永远是 1。排查方法很简单先跑一句SELECT user_id, login_date, COUNT(*) FROM user_login GROUP BY user_id, login_date HAVING COUNT(*) 1如果有结果说明必须先DISTINCT。养成习惯拿到登录表第一件事就是查粒度别急着写主体逻辑。还有一种隐蔽情况表里有设备维度或者登录渠道维度同一个用户同一天在不同设备上登录就有多条。这时候DISTINCT user_id, login_date依然能去重但如果SELECT里没写全维度DISTINCT的效果就不对。所以DISTINCT后面跟的列要仔细核对只留user_id和日期两列别把其他列带进来。6.2 时间带时分秒导致分组失败前面提过一次这里再说透。DATE_SUB作用在DATETIME上时间部分会原样保留。比如2024-01-01 09:12:33和2024-01-02 08:00:00前者减 1 天是2023-12-31 09:12:33后者减 2 天是2023-12-31 08:00:00。两个值看起来都在 12-31实际不相等分组直接裂开。这个错误的迷惑性在于结果不会报错只是数字偏小。你看着结果觉得不对劲又没有明显线索。所以我建议在写的时候就用DATE()包一层把类型强制对齐从源头上断掉这种可能。如果字段类型是字符串情况更复杂。2024-01-01 09:12:33这种字符串做日期运算MySQL 会隐式转换某些情况下能跑通遇到格式不标准的就直接返回NULL。NULL参与分组会聚成一堆结果扑朔迷离。所以字符串日期字段一律先STR_TO_DATE转成日期类型再运算。6.3 排序键和分区键写反ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)这两个位置很容易写反。写反了不会报错但结果毫无意义——相当于按日期分组、按用户编号编号在同一个日期内递增跟连续登录一点关系都没有。这个错误识别起来有个窍门如果结果的用户数远小于表里的用户数或者最大值大得离谱多半是这两个写反了。因为PARTITION BY写错会导致跨用户编号编号值普遍偏大减完之后的分组号也跟着乱。正确的记忆方式是PARTITION BY后面跟“你要分组的主体”这里就是用户ORDER BY后面跟“决定先后的时间维度”这里就是日期。念一遍语义“在同一个用户内按日期顺序编号”就对了。6.4 空值、单天、边界用户边界情况不处理结果会少人或者多算。几个必须检查的点login_date为NULL的记录参与ROW_NUMBER()排序时会被排在最前或者最后取决于数据库实现减完之后分组键也是NULL聚合时COUNT(*)还是能算出数字但语义上一堆NULL被当成连续是纯脏数据。所以前面要加WHERE login_time IS NOT NULL。只有一天登录的用户编号是 1减完得到前一天GROUP BY后COUNT(*)是 1MAX是 1逻辑上没问题。但如果你在某个环节加了HAVING COUNT(*) 1之类的过滤这类用户就被过滤掉了最后结果里查无此人。需求如果是“输出所有用户”这个过滤就不能加。还有一种情况是用户 ID 为0或者空字符串通常是埋点异常或者未登录访客。这类数据要不要算进“用户”得跟业务确认。我一般会在WHERE里加user_id 0排除掉避免污染结果。6.5 大表上的性能问题小表怎么写都行几千万行的登录表就得想想了。几个实用的优化点一是先过滤再分组。如果只需要最近 90 天的数据WHERE要写在最里层子查询别等到最外层才过滤。窗口函数的计算开销和参与计算的数据量成正比早过滤能省不少。二是索引要建对。(user_id, login_date)这个联合索引能覆盖DISTINCT和排序两个操作让执行计划走索引扫描而不是全表扫描。如果字段是DATETIME索引上存的是完整时间戳DATE()包裹后索引会失效可以用login_time 2024-01-01 AND login_time 2024-01-31这种范围条件来保住索引。三是大表分段处理。用户量太大时可以按user_id取模分批跑或者按天增量计算——新的一天数据来了只需要更新受影响的用户不用全量重算。增量计算的思路是维护一张“当前连续天数”的结果表每天跑一次如果今天有登录就加 1没有就归零。这个方案在数据量大、实时性要求高的场景里更实用。四是别用SELECT *。窗口函数需要物化中间结果SELECT *会把没用的列全部带进中间层内存和 IO 都浪费。只SELECT需要的列能省很多。7. 我在面试和带新人时的几点体会7.1 先说思路再写代码这道题真正拉开差距的地方不在代码在前三十秒。如果你上来就敲键盘写一半卡住再回头改观感会很差。比较稳的节奏是先说表结构和粒度假设再说“连续日期减递增序号得到相同分组键”这个核心思路然后问一句“我可以用窗口函数吗”得到肯定的答复再动手。说思路的时候如果能主动画一下那三四行小数据的推演效果会非常好。因为面试官能立刻判断出你是真理解了还是在背模板。我见过不少人 SQL 写得对但让他解释为什么这么做就说不清楚这种情况在需要现场改需求的环节会非常吃亏。还有一个细节写的时候把 SQL 分层写每层加个注释。哪怕面试是在纸上写分行清晰、缩进规范给人的印象也完全不一样。代码的可读性本身就是能力的一部分。7.2 追问环节提前准备这道题的追问基本就那几类提前想清楚能省很多现场思考时间。常见的是不用窗口函数怎么写、表里一天多条怎么办、要输出起止日期怎么改、连续 7 天以上的用户怎么筛、怎么算当前连续天数、数据量太大怎么办。每一类我都建议准备一个能说出口的答案不需要背代码但思路要清楚。比如“不用窗口函数”那题你至少要能说出“用变量按顺序扫描逐行维护连续计数器”这个方向具体语法现场查也行但方向说错就废了。还有一类追问是关于口径的连续的定义包不包含周末、跨年怎么算、用户当天注册但没登录算不算。这些没有标准答案面试官想看的是你会不会主动澄清需求而不是自己假设一个口径闷头写。7.3 我自己的练习方法最后说点实际练的办法。这道题我建议至少手写三遍第一遍照着思路写写完不看答案自己跑数据验证第二遍隔一天关掉所有参考纯手写第三遍换个方言写比如从 MySQL 换到 Hive 或 PostgreSQL。第三遍特别有价值因为方言差异会逼你重新审视每一行的意图而不是机械记忆。比如 PostgreSQL 里date - integer的写法会让你真正意识到“日期减整数”这个操作的本质而不是只记住DATE_SUB这个函数名。另外强烈建议把几个变体都跑一遍尤其是“当前连续天数”和“断点识别”。这两个变体在实际工作里的出现频率比原题还高而且思路稍微拐了个弯练过之后对窗口函数的理解会上一个台阶。我自己带新人的时候一般会让他把这张表自己造一遍故意造出同一天多条、跨月、跨年、只有一天这几种情况。造数据的过程就是梳理边界的过程比直接给一份干净数据让他写效果好太多。数据造完、SQL 跑通、结果对得上期望值这道题才算真的过了。后续想继续深挖的话可以往这几个方向走把连续登录扩展成“连续满足某个条件”比如连续 3 天订单金额大于 100逻辑框架完全一样只是把日期列换成条件标记列或者研究一下“会话切分”本质也是连续性问题只是判断连续的依据从日期变成了时间间隔阈值。这些在用户行为分析里都是高频需求一通百通。