前几天有个做数据开发的朋友把他面完的题发我最后一道 SQL 就是查询每个用户最大连续登录天数。他说当时窗口函数写出来了但因为没去重被面试官一句话问穿最后挂在边界条件考虑不足上。这道题在数仓、数据分析、后端岗位的面试里出现频率极高难的不是语法而是你能不能把连续这两个字翻译成数据库能算的东西。它考的是窗口函数、日期运算、分组聚合三件事的组合运用也考你对脏数据的敏感度。下面我把这道题从为什么这么解到怎么写得稳完整拆一遍不管你是刚学完窗口函数的新手还是写过几千行 Hive SQL 的老手应该都能从里面捞到点东西。1. 这道题真正在考什么从数天数到找连续区间题目本身很短一张登录日志表里面有用户 ID 和登录日期求每个用户历史上最长连续登录了多少天。很多人看到连续就下意识去想前后两条记录日期差是不是 1 天于是脑子里冒出来的是自关联、游标、甚至写个循环。这条路不算错但它把问题做成了 O(n²)而且逻辑一旦展开就变得又长又难维护。面试官想看的恰恰不是你能不能写出来而是你会不会把连续这个自然语言概念转换成一种可以一次性分组的特征。这里有个关键的思维转换。连续登录这件事本质上是在一条按用户、按日期排好序的时间轴上找出一段一段日期不中断的区间。这类问题在数据库圈子里有个专门的说法叫间隙与岛屿gaps and islands连续的记录是一座岛中断的地方就是海。你要做的不是逐条判断相邻关系而是给每座岛打上一个统一的编号然后按编号分组数一数每组有几条。只要你能给同一个连续区间里的所有日期打上相同的分组标记剩下的事情就是GROUP BY加COUNT(*)简单到不像话。所以这道题真正考察的三个能力是第一你会不会用ROW_NUMBER()这类窗口函数给行编号第二你知不知道日期减去编号会产生什么效果第三你有没有意识去处理重复日期、空值、跨年这些真实数据里一定会出现的问题。前两个是技巧第三个是工程素养也是区分背过答案和真做过数据的分水岭。我还想强调一点这道题的输出粒度是每个用户一行而不是每个用户每个区间一行。这意味着解题一定有两层聚合内层按岛屿分组算出每段连续天数外层再按用户分组取最大值。很多人内层写对了外层忘了套结果返回了一堆中间结果面试官一看就知道是拼凑出来的。1.1 大多数人第一反应错在哪最常见的错误写法是先GROUP BY user_id, login_date去重然后直接用COUNT(*)当连续天数理由是登录一次算一天。这种写法算出来的是总登录天数不是连续天数中间断过多少次完全被抹平了。我见过有人在简历上写熟练使用窗口函数结果这道题上来就写了个COUNT(DISTINCT login_date)面试官连追问的兴趣都没有了。第二个高频错误是相邻比较法写一半卡住。思路是LEAD(login_date)拿到下一条记录判断DATEDIFF(next_date, login_date) 1然后想用SUM(CASE WHEN ... THEN 0 ELSE 1 END)累计标记。这个思路其实是成立的它和日期减行号是同一件事的两种表达但写法更绕容易在多用户并发、日期重复的情况下出错。更麻烦的是一旦要求同时输出最长区间的起止时间这种写法还得再套一层代码迅速膨胀。第三个错误是纯粹的逻辑顺序颠倒先算连续天数再去重。比如有人直接对原始日志开窗编号同一用户同一天有三次登录就占三行编号哗啦啦往下加最后算出来的连续天数比真实值大出一截。这个坑非常隐蔽因为在小数据集上跑出来的数字看着还挺像回事只有拿真实日志一验才会露馅。1.2 连续登录的本质把无序日期压成可分组的有序序列换个角度理解这件事。假设有一个用户他的登录日期是 1 月 1 日、2 日、3 日、7 日、8 日。如果你把这些日期写在纸上然后从 1 开始依次标序号会得到这样两列登录日期行号日期减行号2024-01-0112023-12-312024-01-0222023-12-312024-01-0332023-12-312024-01-0742024-01-032024-01-0852024-01-03看出来了吗同一段连续区间里日期每往后走一天行号也往后走一格两者同步前进差值就是一个常数。一旦中间断了日期跳了几天而行号只加 1差值就会变。这个差值就是天然的岛屿编号同一个差值对应同一段连续登录不同差值对应不同的段。你不需要知道这段有多长也不需要比较相邻行只要按差值分组COUNT(*)出来就是每段的长度。这个推导之所以漂亮是因为它把连续性判断从逐行依赖变成了全局可并行计算的特征。在分布式引擎里逐行比较意味着必须把同一个用户的数据 shuffle 到同一个节点并按序处理而编号 分组虽然也要按用户分区但计算过程是向量化的效率高得多。这也是为什么大数据场景下大家都用这一套。2. 日期减行号连续区间识别的核心推导理解了原理接下来要把推导变成可以落地的 SQL。这一段我会把每一步的动机讲清楚而不是直接甩一段代码让你抄。因为面试的时候面试官很可能追问如果我把日期字段换成时间戳会怎样如果我想同时拿到每段区间的开始和结束日期怎么改只有理解了每一步在干什么你才接得住。2.1 用一张 10 行的小表手推一遍假设登录表叫login_log字段是user_id和login_date。我们造一个用户 A 的 8 条记录日期分别是 3 月 1、2、3 日5 日8、9、10 日以及 3 月 10 日重复的一条。先做去重再编号再看差值login_daternlogin_date - rn2024-03-0112024-02-292024-03-0222024-02-292024-03-0332024-02-292024-03-0542024-03-012024-03-0852024-03-032024-03-0962024-03-032024-03-1072024-03-03注意 2024 年是闰年2 月有 29 天所以 3 月 1 日减 1 天是 2 月 29 日。这个细节不影响结果但如果你手动验算的时候算错了会怀疑自己的逻辑有问题。按差值分组后三组分别是 3 天、1 天、3 天最大连续天数就是 3。整个过程一次窗口函数、一次分组聚合没有任何逐行循环。如果这张表里还有用户 B那么编号必须按user_id分区重新开始否则 B 的第一条记录会接着 A 的编号往下排差值就全乱了。这是PARTITION BY user_id存在的意义也是很多人第一次写会漏掉的地方。漏掉的表现是跑出来的最大连续天数大得离谱因为不同用户的数据被拼成了一段连续。2.2 为什么是日期减行号而不是行号减日期有人会问反过来减行不行数学上当然也行差值同样能起到分组作用只是符号相反。但实际写的时候日期减整数在多数数据库里都有原生支持得到的结果还是一个日期类型方便你后面直接拿来当分组键、甚至直接当区间的起始日期用而行号减日期在某些方言里会报类型错误还得额外转换。所以习惯上统一用日期减行号。还有一个更稳妥的变体把日期转成天数再减。MySQL 里有TO_DAYS()PostgreSQL 里可以直接login_date - DATE 1970-01-01得到整数天数Hive 里可以用DATEDIFF(login_date, 1970-01-01)。这样得到的差值是一个纯整数跨数据库移植的时候最省心也不会因为日期类型的时区、精度问题产生意外。我在生产环境里更倾向这种写法因为整数比较永远比日期比较快而且不依赖各个数据库对日期减整数的隐式转换规则。提示如果你的日期字段是DATETIME或者带时区的时间戳务必先用DATE()截断到天再做减法否则同一天不同时刻的记录会被当成不同日期连续判断直接失效。2.3 分组之后的聚合与岛屿边界内层拿到用户 岛屿编号 每组条数之后外层就是标准的GROUP BY user_id加MAX(cnt)。这一步没什么技术含量但有两个细节值得说。第一MAX(cnt)取的是最长的那一段如果你要的是截至目前的连续天数就不能用MAX得定位到最后一段。这个变形我会在第 5 节展开。第二如果你想同时输出最长连续区间的起止日期就在内层聚合时把MIN(login_date)和MAX(login_date)一起算出来外层再按cnt倒序取第一条。这里有个小陷阱如果同一个用户有两段长度相同的最大连续区间直接ORDER BY cnt DESC LIMIT 1会随机返回一段结果不稳定。严谨的做法是用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY cnt DESC, start_date)显式指定第二排序键保证结果可复现。3. 各家数据库的落地写法与函数差异原理讲完接下来是实操。同一道题在不同数据库里的写法差别不小面试的时候如果你能顺口说出MySQL 8 这样写Hive 那边日期函数要换一下印象分会高很多。下面按方言分别给一版可以直接跑的代码并标出容易踩的地方。3.1 MySQL 8.0 与 PostgreSQL窗口函数一把梭MySQL 8.0 开始支持窗口函数这道题终于可以写得干干净净WITH dedup AS ( SELECT DISTINCT user_id, DATE(login_date) AS login_date FROM login_log WHERE user_id IS NOT NULL AND login_date IS NOT NULL ), numbered AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ), island AS ( SELECT user_id, DATE_SUB(login_date, INTERVAL rn DAY) AS grp, COUNT(*) AS days FROM numbered GROUP BY user_id, grp ) SELECT user_id, MAX(days) AS max_consecutive_days FROM island GROUP BY user_id;PostgreSQL 的写法几乎一样只是把DATE_SUB(login_date, INTERVAL rn DAY)换成login_date - rn因为 PG 里date - integer直接返回日期语义非常自然。窗口函数部分完全一致ROW_NUMBER()的语法两家通用。这里有个 MySQL 的细节要注意INTERVAL rn DAY里的rn是窗口函数算出来的BIGINTMySQL 允许这么写但如果你把它换成变量或者表达式可能会遇到类型转换问题。稳妥起见也可以写成login_date - INTERVAL rn DAY效果一样。另外DISTINCT和DATE()一起用的时候DATE()只作用在login_date上DISTINCT是对整个(user_id, login_date)组合去重这个顺序没问题。3.2 Hive 与 Spark SQL日期函数和类型推断的坑Hive 里没有DATE_SUB的双参数日期版本那么随意标准写法是date_sub(login_date, rn)第二个参数必须是INT而ROW_NUMBER()返回的是BIGINT严格模式下可能报类型不匹配需要显式CAST(rn AS INT)。这一点在 Spark SQL 里稍微宽松些但为了保险我还是建议加上转换WITH dedup AS ( SELECT DISTINCT user_id, login_date FROM login_log WHERE user_id IS NOT NULL AND login_date IS NOT NULL ), numbered AS ( SELECT user_id, login_date, CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS INT) AS rn FROM dedup ), island AS ( SELECT user_id, date_sub(login_date, rn) AS grp, COUNT(1) AS days FROM numbered GROUP BY user_id, date_sub(login_date, rn) ) SELECT user_id, MAX(days) AS max_consecutive_days FROM island GROUP BY user_id;Hive 还有一个常见的坑GROUP BY里不能直接引用别名取决于版本和配置所以我在island那层把date_sub(login_date, rn)又写了一遍而不是写GROUP BY user_id, grp。如果你用的是较新版本的 Spark SQL别名是可以用的但为了跨环境兼容重复写一遍表达式最保险。另外Hive 的login_date如果建表时是STRING类型这在日志表里非常常见date_sub会隐式转换但格式必须是yyyy-MM-dd。如果你的原始数据是yyyyMMdd或者带时分秒的字符串必须先to_date(from_unixtime(unix_timestamp(login_date, yyyyMMdd), yyyy-MM-dd))之类的转换走一遍否则结果会静默出错不报错但全错这种最难查。3.3 MySQL 5.7 的变量法能跑但要认清它的代价老项目里 MySQL 5.7 还很常见没有窗口函数只能用用户变量模拟。写法大致是这样SELECT user_id, MAX(days) AS max_consecutive_days FROM ( SELECT user_id, grp, COUNT(*) AS days FROM ( SELECT user_id, login_date, g : IF(p user_id AND DATEDIFF(login_date, d) 1, g, g 1) AS grp, p : user_id AS _p, d : login_date AS _d FROM ( SELECT DISTINCT user_id, login_date FROM login_log WHERE user_id IS NOT NULL AND login_date IS NOT NULL ORDER BY user_id, login_date ) t, (SELECT g : 0, p : NULL, d : NULL) init ) t2 GROUP BY user_id, grp ) t3 GROUP BY user_id;这段代码能跑但它有三个致命问题。第一它依赖ORDER BY的执行顺序而 SQL 标准并不保证ORDER BY一定在变量赋值之前生效MySQL 8.0 之后这种写法已经被标记为不推荐未来可能直接移除。第二变量在SELECT列表里的赋值顺序是从左到右g的计算必须排在p、d更新之前一旦有人调整了字段顺序结果就错了。第三优化器在某些情况下会合并子查询或者改变执行计划导致变量初始化那一步失效。所以我个人的态度是面试时如果能提一句5.7 可以用变量法但不推荐8.0 之后应该用窗口函数比真的写出一段变量法更加分。如果面试官非要你写写完记得补一句这段代码依赖执行顺序生产环境我会避免。3.4 SQL Server 与 Oracle 的写法对照SQL Server 里日期减整数同样是按天算的DATEADD(DAY, -rn, login_date)或者直接login_date - rn都行窗口函数语法和 MySQL 8 一致。Oracle 稍微特殊一点DATE类型做减法得到的是天数差所以login_date - rn也成立但如果你用的是TIMESTAMP减法会返回INTERVAL需要CAST回DATE。Oracle 的ROW_NUMBER()语法倒是标准没有额外坑。下面这张表把几种方言的关键差异列一下方便你写的时候对照数据库日期减行号写法窗口函数支持版本主要注意点MySQLDATE_SUB(d, INTERVAL rn DAY)或d - rn8.05.7 需变量法PostgreSQLd - rn8.4无特殊坑Hivedate_sub(d, CAST(rn AS INT))0.11日期字符串格式校验Spark SQLdate_sub(d, rn)全版本注意GROUP BY别名SQL ServerDATEADD(DAY, -rn, d)2005无特殊坑Oracled - rnDATE 类型全版本TIMESTAMP 需转换4. 那些让答案当场翻车的边界条件代码能跑通只是及格线。真正让面试官决定给不给 offer 的是你能不能主动说出这段逻辑在什么情况下会出问题。下面这几个边界条件都是我在真实数据和面试现场反复见到的。4.1 一天多条登录记录不去重就等于白算这是最高频的错误没有之一。用户上午登一次、下午再登一次日志表里就是两条记录。如果不做去重直接开窗编号这两条记录的rn会连续递增但login_date是同一个值减出来的差值必然不同于是同一天被拆成了两个岛屿每个岛屿只有 1 天。结果是明明连续登录了 7 天的用户算出来最大值只有 1 或者 2。正确做法是在最内层先用SELECT DISTINCT user_id, DATE(login_date)或者GROUP BY user_id, DATE(login_date)把粒度压到用户 天。我一般推荐GROUP BY因为它在语义上更明确而且如果你后面还想顺便统计当天登录次数可以在这里加聚合函数一次扫描拿到多个指标。DISTINCT更简洁但在大表上两者的执行计划可能不同具体看引擎优化。注意去重一定要放在编号之前。顺序反过来编号已经按重复行算过了再去重会把编号跳空差值同样错乱。这个顺序问题很多人绕不过来我自己第一次写的时候也栽过。4.2 DATE 与 DATETIME 混用带来的灾难真实日志表里的时间字段十有八九是DATETIME或者TIMESTAMP精度到秒甚至毫秒。如果你忘了截断同一天 09:00 和 21:00 就是两个不同的值DATEDIFF出来是 0 天但ROW_NUMBER编号却加了两格连续判断直接崩掉。更隐蔽的是某些数据库的DATE()函数在遇到非法日期时会返回NULL而不是报错NULL参与窗口排序会被排到最后导致编号错位。我的处理习惯是在 CTE 的第一层就把时间字段统一成日期类型并且在WHERE里过滤掉NULL和明显异常的日期比如 1970-01-01 这种默认值或者 2099 年这种未来时间。这一步看着啰嗦但它能挡掉后面 80% 的莫名其妙的问题。如果数据源允许最好在建表或者 ETL 阶段就统一成DATE类型源头治理永远比事后补丁划算。4.3 跨年、闰年与 2 月 29 日因为用的是日期减法而不是月份加一跨年、跨月、闰年这些情况天然不会出问题。12 月 31 日到 1 月 1 日差值照样是连续同步变化不需要任何特殊处理。这正是日期减行号比按月份分桶更健壮的地方。但有个细节需要留意如果你的日期字段是字符串而且格式不统一比如有的写2024-3-1、有的写2024-03-01转换的时候可能失败或者被解析成错误日期。闰年 2 月 29 日尤其容易暴露格式问题因为不是所有解析器都能正确处理。我一般会在 ETL 层加一个日期合法性校验把解析失败的行单独落到一张错误表里而不是默默丢掉或者让它们变成NULL混进结果。4.4 空表、单行记录与全勤用户空表的情况下ROW_NUMBER不产生任何行最终结果集也是空的这没问题。但如果你的外层用了LEFT JOIN关联用户维表就要注意用COALESCE(max_consecutive_days, 0)把没有登录记录的用户补成 0否则下游看报表的时候会发现用户数少了几个。单行记录也值得测一下。一个用户只有一条登录去重后编号为 1差值为login_date - 1分组后COUNT(*)等于 1MAX出来也是 1逻辑正确。这类边界看着简单但我见过因为在内层用了HAVING COUNT(*) 1过滤而把单次登录用户整个丢掉的情况原因是他想排除异常数据结果把正常用户也排掉了。还有一种情况是全勤用户比如一整年每天都登录。这时候整张表就是一座大岛COUNT(*)出来是 365 或 366结果正确但要注意INT和BIGINT的溢出问题基本不存在倒是不用担心。真正要担心的是数据量如果用户量是千万级、每人都有一年记录内层DISTINCT加窗口排序的开销会非常大这个我在第 6 节讲性能的时候再说。5. 面试官最爱追问的几个变形基础版写对之后面试官通常不会就此打住而是顺着题目往业务场景上引。下面这几个变形出现频率最高我按改哪里的思路讲方便你现场推导而不是死记。5.1 截至今天还连着登的用户当前连续天数是多少这个需求在留存分析里非常常见。关键区别是最长连续天数取MAX当前连续天数只能取以最大日期结尾的那一段。判断方法有两种。第一种是先算出全局的参考日期也就是数据里的最大日期或者今天然后筛选出MAX(login_date) 参考日期的用户再取其最后一段的长度。写法上可以在内层岛屿表里加一列MAX(login_date) OVER (PARTITION BY user_id)外层用WHERE max_login_date CURRENT_DATE过滤。第二种更直接把岛屿按结束日期倒序编号取第一条。用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY end_date DESC)得到rk外层WHERE rk 1同时判断end_date是否等于今天。这里要注意今天的定义是数据库服务器的CURRENT_DATE还是业务时区的今天跨时区的系统里这两者可能差一天会导致结果对不上。我一般会在 ETL 里把时区统一成业务时区避免在 SQL 里做时区转换。5.2 连续登录不少于 N 天的用户名单这个变形只需要在内层分组之后加一个HAVING COUNT(*) N然后外层去重。注意是内层过滤不是外层过滤因为外层已经聚合成每个用户一行了你没法在那里判断有没有任意一段达到 N 天。如果要输出的是每个用户的连续登录段数那就把HAVING去掉直接COUNT(DISTINCT grp)。这个指标在用户活跃度分层里挺有用比如一个用户一年里连续登录了 20 段另一段只连续了 2 段说明前者是间歇性活跃、后者是短期集中使用运营策略完全不同。5.3 把最长连续区间的起止日期一起捞出来在内层岛屿聚合时同时算MIN(login_date) AS start_date和MAX(login_date) AS end_date然后在外层用窗口函数按days DESC排序取第一条。前面提过如果存在并列最长要加start_date作为第二排序键保证稳定。还有一种需求是输出所有连续区间按长度倒序这时候连外层取第一条都不需要直接对岛屿表ORDER BY user_id, days DESC就行。写报表的时候这种明细表往往比一个汇总数字更有用因为业务方可以自己再筛选。5.4 按最长连续天数排名与分位数如果题目变成求最长连续登录天数排名前 10% 的用户就在基础结果之上再套一层窗口函数RANK() OVER (ORDER BY max_consecutive_days DESC)或者NTILE(10)。这里要提醒的是RANK和DENSE_RANK在并列情况下的行为不同如果业务要求并列不占用后续名次用DENSE_RANK如果要求严格按位置排用ROW_NUMBER加显式第二排序键。这个细节面试官很可能会追问一句答得上来就是加分项。6. 从面试题到生产查询性能与工程化面试题的数据量通常是几十行怎么写都能秒出结果。但同样的逻辑放到千万级用户的日志表上写法和优化思路要调整不少。这一节讲的是我在实际项目里踩过的性能坑。6.1 索引怎么建才算到位这道题的核心访问模式是按用户分区、按日期排序所以最理想的索引是(user_id, login_date)的联合索引。有了它窗口函数的PARTITION BY user_id ORDER BY login_date可以直接走索引有序扫描省掉一次排序。如果查询里还有日期范围过滤比如只算最近 90 天那索引顺序要不要调整成(login_date, user_id)就得看过滤条件的区分度了。我一般会先用EXPLAIN看一眼执行计划重点看两件事有没有出现Using filesort或者Using temporary以及扫描行数是不是接近全表。如果Extra里出现了Using filesort而且数据量大说明索引没被用上排序得检查字段类型是否一致、字符集是否匹配。这类问题在跨库同步的表上特别常见源头表是utf8mb4目标表建成了utf8索引直接失效查询慢十倍。6.2 大表场景下的分桶与预聚合如果表有几十亿行即使有索引每次跑窗口函数也不现实。实际做法通常是做预聚合每天跑一个离线任务把当天的登录用户去重后落到一张日粒度表里字段就是user_id和login_date主键设成这两列的组合。这张表的数据量比原始日志小一个数量级后面算连续天数就在它上面跑。再往上还可以做增量计算。连续天数的特点是只要知道用户昨天的连续天数和今天有没有登录就能推出今天的连续天数完全不需要重新扫全历史。这就是典型的递推思路今天登录了当前连续天数加一今天没登录归零。很多实时数仓里就是这么做的用一张user_streak表存user_id、last_login_date、current_streak每天更新一次。面试的时候如果能提到这个思路说明你不只是会做题还想过怎么落地。6.3 用 Python 写个暴力解做对拍SQL 写完之后我习惯用 Python 写一个暴力版本对拍尤其是涉及日期运算的题目人眼验算太容易出错。思路很简单把数据读成字典key 是用户value 是日期集合然后对每个用户的日期排序遍历一遍数连续段。from collections import defaultdict from datetime import date, timedelta def max_consecutive(rows): user_days defaultdict(set) for user_id, d in rows: if user_id is None or d is None: continue if isinstance(d, str): d date.fromisoformat(d) user_days[user_id].add(d) result {} for user_id, days in user_days.items(): sorted_days sorted(days) best cur 1 for i in range(1, len(sorted_days)): if sorted_days[i] - sorted_days[i - 1] timedelta(days1): cur 1 best max(best, cur) else: cur 1 result[user_id] best return result这段代码的价值不在于性能而在于它把逻辑写成了最直白的相邻日期差 1 就加一没有任何窗口函数和分组的心智负担。拿它和 SQL 结果一比如果对不上大概率是 SQL 里的去重或者类型转换出了问题。我做过好几次每次都是 SQL 那边漏了DISTINCT或者日期字段是字符串没转换。这种对拍习惯在写复杂分析 SQL 的时候特别救命比盯着屏幕一行行看靠谱多了。另外一个小技巧造测试数据的时候除了正常场景一定要手工构造几个脏用户比如只有一条记录的、连续段长度相同的、日期跨年的、有重复日期的、日期为NULL的。这几个用例覆盖住基本就能把大部分逻辑漏洞挡在提交之前。最后分享一个我自己的习惯。每次写完这类带窗口函数的 SQL我都会问自己三个问题去重放在编号前了吗日期截断到天了吗外层聚合的粒度是每个用户一行吗这三个问题对应这道题最容易翻车的三个点问完基本就不会出大错。至于面试如果时间允许主动把边界条件和性能考虑说出来比多写十行代码更能让人记住你。