★ FEATURED ARTICLE LeetCode 数据库题单做到后面会发现大量题目其实是在为真实世界的报表需求做“降维”。这道“报告系统状态的连续日期”就是非常典型的一道。题面很短系统每天运行成功日期和失败日期分别记在两张表里要求把某个时间段内所有“连续状态区间”找出来输出每个区间是成功还是失败、从哪天开始到哪天结束。很多人第一反应是“按状态分组然后取MIN、MAX不就行了”真动手写才发现难点全在“连续区间”四个字上。这篇文章就从头梳理这道题的完整推导过程、最稳的窗口函数解法以及我实际写 SQL 和做报表时踩过的几个坑。1. 题目到底在问什么从两张状态表还原系统运行时间线1.1 表结构与我对题意的理解先看数据结构。题目给了两张表一张记录失败日期一张记录成功日期表名字段说明Failedfail_date系统失败的日期主键Succeededsuccess_date系统成功的日期主键题目有个隐含前提系统每天运行所以每一天的状态是唯一的要么成功、要么失败两张表的日期不会重叠。需要输出的结果包含三列period_state状态、start_date区间开始日期、end_date区间结束日期按开始日期排序。光说概念有点抽象我构造一个简化例子。假设只关注 2019 年 1 月 1 日到 1 月 8 日状态分别是1、2 日成功3、4 日失败5、6、7 日成功8 日失败那么期望输出应该是period_statestart_dateend_datesucceeded2019-01-012019-01-02failed2019-01-032019-01-04succeeded2019-01-052019-01-07failed2019-01-082019-01-08注意最后一行失败状态只有一天也要作为一个独立区间输出不能因为“太短”就丢。1.2 真实业务里“连续区间”问题的样子这道题看起来是刷题实际上是很多报表需求的缩影。比如监控系统要统计“服务连续可用了多少天”“最近一次故障从哪天持续到哪天”用户增长团队要统计“每个用户连续登录了多少天”“最大连续签到天数”电商运营要看“优惠券连续发放了多少天”。这些需求背后的数学模型都一样给一堆带有日期、带有状态标签的记录把相同状态且日期连续的记录归成一段然后计算每段的起止时间。如果只按状态分组会出大问题。比如系统 1 月成功、2 月失败、3 月又成功直接 GROUP BY 状态会把 1 月和 3 月的成功记录合并成一条“成功区间 1 月 1 日到 3 月 31 日”中间的失败段被彻底吞掉。所以必须先识别“连续段”再对段做聚合。这也是为什么不能简单用GROUP BY period_state解决。2. 连续区间分组的核心原理为什么不是简单的前后行比较2.1 自连接与逐行判断的困境我第一次见这道题时脑子里冒出来的方案是先按日期排序然后看每一天和前一天的日期是否相差 1 天。如果相差 1 天说明连续如果不连续说明是新区间开头。这个思路没有错但要落地非常麻烦。你要么用LAG()逐行判断后再想方设法把“是否连续”的标记转换成区间编号要么用自连接把每一天往前 JOIN 半天找出所有连续段的起点和终点再把它们拼接起来。后者在数据量小的时候能跑但 SQL 写出来又长又脆遇到时间跨度大、状态切换频繁的情况自己都能把自己绕晕。这类问题的本质不是“判断两个日期是否连续”而是“给每个连续段分配一个稳定且唯一的编号”。一旦拿到了段编号之后所有统计都变成普通的GROUP BY。所以问题核心在这里怎么一分钱不花地给每个连续段编上号。2.2 两个序号相减把连续行变成同一个分组标识这里要引入两个窗口函数编号rn_all按日期全局排序得到每一行在整个时间线里的位置。rn_state按状态分区再按日期排序得到每一行在“同一种状态内部”的位置。看下面这个表我按 1 月 1 日到 8 日的数据演示日期状态rn_allrn_statern_all - rn_state2019-01-01succeeded1102019-01-02succeeded2202019-01-03failed3122019-01-04failed4222019-01-05succeeded5322019-01-06succeeded6422019-01-07succeeded7522019-01-08failed835仔细观察最后两列。连续的 1 号、2 号都是成功状态rn_all和rn_state同步递增差值都是 05 号到 7 号成功状态里全局序号在涨状态内序号也在涨两者差值恒定在 2。反而是状态切换时比如 3 号变成失败rn_all从 2 跳到 3但失败状态的rn_state从 0 开始计为 1差值一下子从 0 跳到 2。这就是关键同一段连续状态内差值保持不变一旦状态切换或日期断档差值就会改变。所以GROUP BY period_state, rn_all - rn_state就能把每一段连续区间分开。成功状态里差值 0 是 1-2 号差值 2 是 5-7 号失败状态里差值 2 是 3-4 号差值 5 是 8 号完美切分。2.3 一个帮助理解的生活化类比我给同事讲这个思路时习惯用电影院座位来形容。假设观众按到达顺序拿到排队号这是“全局编号”但影厅座位是按排编号的同一排从 1 号开始这是“分区编号”。如果一拨人连续买走同一排的 1、2、3 号座位他们的排队号和座位号同步增长排队号减座位号的差值恒定。一旦中间有人买走了前一排的票再往后的人虽然座位号也从 1 开始但排队号已经高出很多差值一下就变了。状态切换就像换了一排座位“全局序号”和“分区序号”错位的程度恰好告诉我们谁和谁属于同一个连续小团体。2.4 只按状态编号也能做原理等价但更脆有朋友可能会问我不用rn_all只按状态内部编号行不行比如直接ROW_NUMBER() OVER (PARTITION BY status ORDER BY date) AS rn_state然后拿date - rn_state作为分组锚点。在“每天只有一条记录”的干净数据下这样确实也能分组连续两天日期每天加 1状态内编号也每天加 1相减后锚点不变一旦断档锚点右移新段就有了新锚点。那为什么很多题解仍然用两个编号相减因为真实场景常有脏数据比如同一天出现多条状态记录。如果某天系统里状态被记录了两笔只按状态编号做日期减法就会被多出来的行干扰连续区间的边界容易算错。两个编号相减的写法对“先把时间线压平、再分组”这件事更鲁棒。这也是我推荐大家理解并掌握两个ROW_NUMBER()写法的原因。3. 从零推导完整 SQL合并时间线、编号、分组取端点3.1 第一步把成功和失败记录合并成一条时间线两张表各管一种状态但我们的连续区间要看完整时间线所以第一步是把它们合并起来给每一行补上状态标签。我用 CTE 来组织逻辑清晰WITH all_dates AS ( SELECT fail_date AS event_date, failed AS period_state FROM Failed WHERE fail_date BETWEEN 2019-01-01 AND 2019-12-31 UNION ALL SELECT success_date AS event_date, succeeded AS period_state FROM Succeeded WHERE success_date BETWEEN 2019-01-01 AND 2019-12-31 )这里用UNION ALL而不是UNION因为两张表日期不会重叠而且每行都有状态标签不需要去重即便以后遇到可能有重复的脏数据也应该在这里先做去重而不是依赖UNION悄悄过滤。为什么要在这里做日期范围过滤先记住这个动作第 4 章我会专门讲不这么写的坑。3.2 第二步分别计算全局编号和状态内编号接着在all_dates之上加窗口函数numbered AS ( SELECT event_date, period_state, ROW_NUMBER() OVER (ORDER BY event_date) AS rn_all, ROW_NUMBER() OVER (PARTITION BY period_state ORDER BY event_date) AS rn_state FROM all_dates )PARTITION BY period_state是给状态分区让成功和失败各自从 1 开始编号。ORDER BY event_date保证每个分区内按时间先后编号。两个窗口函数放在同一个查询里没有冲突它们各自独立计算。3.3 第三步按“状态差值”分组取每个区间的起止日期分组和聚合就很简单了SELECT period_state, MIN(event_date) AS start_date, MAX(event_date) AS end_date FROM numbered GROUP BY period_state, rn_all - rn_state ORDER BY start_date;rn_all - rn_state就是上一节说的差值它和period_state共同充当区间编号。同一段连续的日期差值相同MIN、MAX自然取到段首和段尾。这里要注意SELECT里没有直接选rn_all - rn_state只把它放在GROUP BY中避免某些数据库对 SELECT 列和 GROUP BY 列不一致的报错。3.4 建表验证从数据到结果的完整过程为了让大家能直接复现我给出一个 MySQL 8 下的完整示例CREATE TABLE Succeeded ( success_date DATE PRIMARY KEY ); CREATE TABLE Failed ( fail_date DATE PRIMARY KEY ); INSERT INTO Succeeded VALUES (2019-01-01), (2019-01-02), (2019-01-05), (2019-01-06), (2019-01-07); INSERT INTO Failed VALUES (2019-01-03), (2019-01-04), (2019-01-08);然后把前面三段 SQL 拼起来执行结果应该是period_statestart_dateend_datesucceeded2019-01-012019-01-02failed2019-01-032019-01-04succeeded2019-01-052019-01-07failed2019-01-082019-01-08和我的示例完全一致。把数据换成长达一年的真实数据逻辑也不会变。4. 提交之后我才注意到的几个坑过滤、边界和方言差异4.1 日期过滤放在外层会让连续区间跨年并产生“幽灵段”这是最容易翻车的点。假设系统在 2018 年 12 月 31 日成功2019 年 1 月 1 日也成功如果不在合并前过滤日期这两行会一起进入编号被划到同一个连续区间里。结果start_date变成 2018 年最后一天而题目只关心 2019 年。有人可能想“那我再对结果加一个WHERE start_date 2019-01-01不就行了”但这样会把 12 月 31 日和 1 月 1 日连在一起的区间直接砍掉而不是把起点修正为 1 月 1 日语义就错了。最稳妥的办法就是像我在 3.1 写的那样在UNION ALL之前对每张表都单独加上BETWEEN 2019-01-01 AND 2019-12-31。先把时间线裁剪成题目关心的范围再谈连续性。这样跨年段自然被排除在计算之外。4.2 单日区间不是 bug别用 HAVING COUNT 1 过滤我看到不少人在写完分组后顺手加一句HAVING COUNT(*) 1理由是“只有一天的区间没什么分析价值”。在真实业务里这个需求可能存在但在这道题里是错的。失败一天也是一个完整区间8 号单独一段必须出现在结果里。真想过滤单日段说明业务上对“至少连续 N 天”有要求这属于需求变形不是题目原本的意思。可以留到第 5 章变体里做不要在基础解法中画蛇添足。4.3 不同数据库的窗口函数与方言差异窗口函数现在已经是主流数据库的标配了MySQL 8.0、PostgreSQL、SQL Server、Oracle 都支持ROW_NUMBER() OVER (...)。但要注意几个细节SQL Server 中GROUP BY对表达式的处理比较严格如果你SELECT rn_all - rn_state但没把它放进GROUP BY会报错。我的写法里没有把它放到 SELECT 列表所以问题不大。MySQL 8.0 之前没有窗口函数老项目里如果还在用 5.7需要改成用户变量模拟累加或者先做自连接。核心思想仍然一样构造一个随连续段变化的差值。不同数据库对日期加减的函数名不同比如 MySQL 用DATE_SUB/DATE_ADDSQL Server 用DATEADD。如果只把“差值”当作整数分组键就可以完全避开日期函数这也是我喜欢这种写法的原因之一。4.4 真实数据没有题目承诺的那么干净先压平再编号题目保证两张表日期不重叠、每天恰好一条但真实业务流里常常不是这样。比如一张“系统状态变更流水表”一天可能记录三次状态切换既有成功又有失败。如果直接拿流水表跑上面的 SQL同一天会有多行窗口函数的编号彻底乱掉连续区间也会被切成碎片。正确做法是先做“日粒度折叠”按日期分组取出当天的最终状态或者业务上定义的快照状态得到一张干净的“每日状态表”然后再套用窗口函数。这一步很关键可以说它才是真实报表里真正的重点。我遇到过不少同事把刷题逻辑背得很熟结果一遇到流水表就崩原因就是漏了“压平”预处理。5. 这套解法能迁移到哪些场景从系统状态到用户行为分析5.1 变体一统计每个状态最长的一段连续日期理解了分段原理后所有后续统计都变成了在“段”上的普通聚合。想找“最长连续失败天数”可以在分组结果上再加一层SELECT period_state, MAX(continuous_days) AS max_continuous_days FROM ( SELECT period_state, DATEDIFF(MAX(event_date), MIN(event_date)) 1 AS continuous_days FROM numbered GROUP BY period_state, rn_all - rn_state ) t GROUP BY period_state;这里DATEDIFF的方言差异比较大MySQL 里DATEDIFF(MAX, MIN)返回天数差SQL Server 里要写成DATEDIFF(day, MIN, MAX)。加 1 是因为首尾都要算进连续天数比如 1 号和 2 号相差 1 天但实际连续 2 天。5.2 变体二只有一张流水表时统计每个用户的连续登录天数这是最常被问到的变形。假设有一张user_login(user_id, login_date)表每个用户每天可能登录多次也可能漏登。先压平成“每个用户每天一条登录记录”然后用同一个思路只不过分区变成用户和状态标记SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn_user, ROW_NUMBER() OVER (PARTITION BY user_id, is_login ORDER BY login_date) AS rn_flag FROM daily_login ) t GROUP BY user_id, rn_user - rn_flag;这里的关键是is_login这个标记列。因为每日登录表已经只有登录记录没有一个“未登录”的显式状态所以更常见的做法是只按用户分区编号然后用login_date - rn_user做锚点。连续登录的日期日期和编号同步增加相减后锚点相同一旦某天没登录日期跳变但编号只加 1锚点右移自然地分出新段。这个写法和 2.4 里说的“状态内部编号 日期减法”本质一致。5.3 变体三连续 N 次以上才算有效区间的等级化判定业务上经常有“连续签到 7 天发奖励”“连续失败超过 3 天触发告警”的规则。这只需要在分组聚合后加一个HAVINGSELECT period_state, MIN(event_date) AS start_date, MAX(event_date) AS end_date, COUNT(*) AS days FROM numbered GROUP BY period_state, rn_all - rn_state HAVING COUNT(*) 3;比如“连续失败超过 3 天”就取period_state failed且HAVING COUNT(*) 3。段编号的思想完全不变过滤条件随意加。5.4 面试里最常被追问的点为什么先要把时间线压平很多面试官顺着这道题会继续问同一个用户一天登录三次怎么办系统一天内有多次状态切换怎么办这些追问的核心都在考察一件事——你清不清楚数据粒度。窗口函数、GROUP BY都只是工具真正决定结果正确与否的是你在编号前有没有把时间线整理成“每天一行”的稳定粒度。我的经验是遇到所有“连续区间”类问题先按下面的顺序自查一遍记录能不能折叠成“时间 状态”的日粒度同一时间、同一状态会不会有多条重复记录连续的定义是什么日期连续还是业务上的自然日连续空表、单行数据、单日区间这些边界有没有验证这四件事都想清楚再落 SQL 就不容易出偏差。我个人在这道题上最大的收获还不是记住了这个 SQL 模板而是养成了先画“时间线”再写代码的习惯。以前遇到连续在线、连续登录、持续故障这些需求我第一反应是写递归查询或者在应用层循环遍历现在反而会先停下来想这些记录能不能被折叠成一条干净的每日状态线如果能后面就是两个ROW_NUMBER()加一个GROUP BY的事。这道题好就好在它把连续区间问题浓缩得很干净不炫技却把窗口函数、CTE、分组思想全串起来了。多说一句SQL 里的连续问题本质上都是“给连续段找一个稳定身份”。你找到了这个身份后面所有统计都水到渠成。看完文章如果觉得理解了一定自己建一张表把示例数据跑一遍——跑通一次比盯着题解看十遍都管用。
阅读完成 · 觉得有帮助？
