最近几年，只要和数据相关的岗位面试，十次里有八次会遇到这道题：给一张用户登录记录表，统计连续登录N天及以上的用户数。我第一次看到这题时，第一反应是"这有什么好考的"，后来真正处理过用户活跃分析才发现，这道题能在一百个候选人里筛出九十个——它考的不只是会不会写一个窗口函数，而是能不能把"连续"这个词翻译成可计算的分组逻辑。

这篇文章我准备从一道具体需求出发，把建表、造数、Hive/MySQL两个版本的解法、最容易踩的坑，以及从"连续登录"扩展到"连续活跃"的思路全部过一遍。无论你是刚接触SQL的数据新人，还是已经写过不少统计的数仓开发，应该都能从中找到有用的东西。

1. 这道经典题在考什么：连续登录的定义与需求拆解

先看需求的原形。假设有一张登录流水表，里面记录了每个用户的登录日期，现在要找出"连续登录3天及以上"的用户有哪些，如果只给最终结论，只需要输出用户id和对应的连续天数。严格来说，很多业务方还会要求输出用户数，比如"上个月连续打卡7天的用户有多少"，那就在这个基础上再套一层去重计数。

"连续"这个词在SQL里没有天然的表达方式，必须自己构造。常见定义是按自然日连续，也就是说，用户A在1月1日、1月2日、1月3日这三天每天都登录，就算连续3天；如果中间隔了一天，比如1月1日和1月3日登录，但1月2日没来，就不算连续。更严格一点，还要满足"每天至多只算一次登录"，毕竟用户一天登十次，活跃度也不该按十次算。

1.1 需求先拆成三层再动手写SQL

拿到这种需求，我习惯先把它拆成三个子问题：去重、排序、分组。去重解决"同一天多条登录记录"的问题；排序解决"每个用户日期先后顺序"的问题；分组则是核心，要决定"哪些日期属于同一个连续段"。

很多初学者犯的错误是上来就写SELECT user_id, COUNT(DISTINCT login_date)，这样只能算出总登录天数，算不出连续天数。因为COUNT(DISTINCT)会把断掉的日子也拼在一起，比如1月1日、1月3日、1月5日统计出来是3天，但显然不连续。

1.2 一张能说清需求的样例数据

假设我们有四个用户，登录记录长这样：

从肉眼看：101有1月1日到3日连续3天，102虽然1月1日有两条记录，但1月1日到2日只有2天，达不到3天，103连续4天，104从2023年12月29日一路跨到2024年1月2日，连续5天。所以最终需要输出的用户是101、103、104。

这里的102就是典型的"同一天多次登录"陷阱，如果不去重直接算，1月1日会占两个排序位置，本来连续的两天也会被拆得乱七八糟。

1.3 表结构只需要四个字段

做这个分析，一张简单的登录流水表就够了。实际业务里可能还有设备id、登录渠道、登录时长这些字段，但统计连续登录只关心三样：用户id、登录日期、以及是否还有更多信息需要追溯。如果需要看登录时间明细，可以再加一个login_time；如果没有，这张表最小可用的结构就是user_id加login_date。后面我会给完整的建表语句，这里先把模型在脑子里搭好。

2. 核心解法：日期减序号，为什么这一招能识别连续登录

网上关于这题的解法很多，但绕来绕去都离不开同一个核心技巧：把登录日期按用户排序编上号，然后用日期减去编号，得到一个"分组标记"。日期连续时，这个标记相同；日期一旦断了，标记就会跳变。

2.1 从数学上理解为什么能分组

连续登录的日期序列，本质上是一个公差为1的等差数列。比如1月1日、1月2日、1月3日，每两个相邻日期之间差1天。如果我们给这些日期从1开始依次编号，编号序列也是公差为1的等差数列：1、2、3。

同一个等差数列减去另一个公差相同的等差数列，结果是一个常数。所以日期减编号得到的是同一个值。反过来，一旦中间断了一天，日期仍然按年份递增，但编号已经被跳过的日子隔开，相当于日期序列还是连续的公差1，编号序列却没有对应上，两者的差值就会发生变化。

2.2 排队发号码牌的生活类比

可以想象用户每天去签到，每天签到的队伍里都有固定的一天位置。用户连续登录3天，就是这3天每天都在队伍里；日子每天往后挪一天，用户的签到次数也加1，所以"日期这一列减去次数这一列"的差值始终固定。如果用户隔了一天再来，日期往后跳了两天，但签到次数只加了1，差值就被拉大了，一下子就能看出这是两段。

这个思路和很多字符串匹配里的"滑动窗口"不太一样，它不需要窗口，只需要一次排序和一次减法，SQL表达非常简洁。

2.3 手把手算一遍101用户的过程

以101为例，他登录了1月1日、1月2日、1月3日、1月6日。先按日期排序，编上序号rn，再计算date_sub(login_date, rn)（MySQL写法是DATE_SUB(login_date, INTERVAL rn DAY)），结果如下：

看到没有，前三行日期连续，减出来的group_flag都是2023-12-31，第四行单独是2024-01-02。这样按user_id和group_flag分组，count一下，就能得到101有连续3天和连续1天两个段。这里rn从1开始，所以组标记比实际起始日期早了一天，但这完全不影响分组。

2.4 分组后如何筛选连续N天

有了group_flag之后，事情就简单了：GROUP BY user_id, group_flag，然后用COUNT(*)数每个连续段的天数。想要"连续3天及以上"，加一句HAVING COUNT(*) >= 3就行。如果想要"最大连续登录天数"，去掉HAVING，直接取MAX(continuous_days)。

这里有个小细节很容易被忽略：连续天数就是该组里的记录条数，因为我们已经按天去重过了。如果没去重，count出来的是登录次数而不是天数，后面会专门讲这个坑。

3. 完整实战：建表、造数据，Hive/MySQL双版本跑通

这个部分直接给能跑的东西。我尽量让Hive和MySQL的建表、插入、查询都齐整，方便你本地复现。

3.1 MySQL建表与测试数据

我见过很多建模方式是把user_id和login_date设成联合主键，表示"一个用户一天只能有一条登录记录"。但真实流水表往往是多端登录、多渠道上报，同一天同一用户有多条记录很正常。所以更稳妥的建模是保留自增id，把user_id和login_date建一个联合索引，查询时在SQL里去重。

CREATE TABLE user_login_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL COMMENT '用户ID', login_date DATE NOT NULL COMMENT '登录日期，YYYY-MM-DD', login_time DATETIME DEFAULT NULL COMMENT '登录时间', KEY idx_user_date (user_id, login_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户登录流水表';

插入测试数据，其中102在1月1日故意插了两条：

INSERT INTO user_login_log (user_id, login_date, login_time) VALUES (101, '2024-01-01', '2024-01-01 09:12:00'), (101, '2024-01-02', '2024-01-02 10:01:00'), (101, '2024-01-03', '2024-01-03 08:20:00'), (101, '2024-01-06', '2024-01-06 20:55:00'), (102, '2024-01-01', '2024-01-01 08:00:00'), (102, '2024-01-01', '2024-01-01 12:30:00'), (102, '2024-01-02', '2024-01-02 09:05:00'), (102, '2024-01-04', '2024-01-04 11:11:00'), (103, '2024-01-01', '2024-01-01 07:45:00'), (103, '2024-01-02', '2024-01-02 18:30:00'), (103, '2024-01-03', '2024-01-03 10:10:00'), (103, '2024-01-04', '2024-01-04 21:00:00'), (104, '2023-12-29', '2023-12-29 08:00:00'), (104, '2023-12-30', '2023-12-30 09:00:00'), (104, '2023-12-31', '2023-12-31 10:00:00'), (104, '2024-01-01', '2024-01-01 11:00:00'), (104, '2024-01-02', '2024-01-02 12:00:00');

3.2 Hive建表与数据写入

Hive的表结构和MySQL基本一致，差异主要在存储格式和类型上。为了演示方便，我直接用非分区表；生产环境建议按日期字段加分区，后面会讲分区裁剪怎么用。

CREATE TABLE user_login_log ( user_id BIGINT COMMENT '用户ID', login_date STRING COMMENT '登录日期，格式YYYY-MM-DD', login_time STRING COMMENT '登录时间' ) COMMENT '用户登录流水表' STORED AS ORC;

插入数据和MySQL类似：

INSERT INTO TABLE user_login_log VALUES (101, '2024-01-01', '2024-01-01 09:12:00'), (101, '2024-01-02', '2024-01-02 10:01:00'), (101, '2024-01-03', '2024-01-03 08:20:00'), (101, '2024-01-06', '2024-01-06 20:55:00'), (102, '2024-01-01', '2024-01-01 08:00:00'), (102, '2024-01-01', '2024-01-01 12:30:00'), (102, '2024-01-02', '2024-01-02 09:05:00'), (102, '2024-01-04', '2024-01-04 11:11:00'), (103, '2024-01-01', '2024-01-01 07:45:00'), (103, '2024-01-02', '2024-01-02 18:30:00'), (103, '2024-01-03', '2024-01-03 10:10:00'), (103, '2024-01-04', '2024-01-04 21:00:00'), (104, '2023-12-29', '2023-12-29 08:00:00'), (104, '2023-12-30', '2023-12-30 09:00:00'), (104, '2023-12-31', '2023-12-31 10:00:00'), (104, '2024-01-01', '2024-01-01 11:00:00'), (104, '2024-01-02', '2024-01-02 12:00:00');

3.3 核心查询SQL：Hive和MySQL的细微差别

先看Hive版本，我习惯用子查询一层套一层，这样在各个版本里都相对稳妥。第一步去重，第二步开窗排序，第三步算group_flag，第四步分组统计：

SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, CAST(rn AS INT)) AS group_flag FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ) dedup ) seq ) t GROUP BY user_id, group_flag HAVING COUNT(*) >= 3 ORDER BY user_id;

MySQL版本几乎一样，唯一的差别是日期减天数的写法：Hive用date_sub(login_date, n)，MySQL用DATE_SUB(login_date, INTERVAL n DAY)。

SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ) dedup ) seq ) t GROUP BY user_id, group_flag HAVING COUNT(*) >= 3 ORDER BY user_id;

这里用的是MySQL 8.0+的窗口函数。如果你的生产环境还是MySQL 5.7，别急，第5章会专门讲变量写法。

3.4 结果解析和最终用户数

跑出来的结果应该是：

和手工看数据得出的结论完全一致。102虽然1月1日有两条记录，但因为先去重了，他最大连续段只有2天，被HAVING过滤掉。

如果要输出"连续登录3天及以上的用户数"，需要在上面再包一层：

SELECT COUNT(DISTINCT user_id) AS user_cnt FROM ( SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ) dedup ) seq ) t GROUP BY user_id, group_flag HAVING COUNT(*) >= 3 ) cnt;

这个嵌套的目的是先算出每个用户的连续段，再横向去重统计用户数。你可能会问，直接在外面SELECT COUNT(*)不行吗？不行，因为一个用户可能有多个连续3天以上的时间段，比如连续3天、隔两天又连续3天，直接count会把同一个用户计多次。业务上问"用户在统计周期内是否连续登录过3天"，需要先按用户维度折叠成一个布尔值，再计数。

4. 那些容易踩的坑：重复登录、空值、跨年、统计口径

这道题看似简单，真正跑数的时候坑非常多。我见过不少人笔试写得头头是道，一到生产环境查出来的数对不上，问题大多出在这一章。

4.1 同一天多次登录必须先去重，这是第一号坑

如果表里一个用户一天登录了多次，直接开窗排序，1月1日会拿到两个不同的rn。比如102在1月1日有两条记录，排序后rn可能是1和2，1月2日是3。此时计算date_sub(login_date, rn)，1月1日两条分别得到2023-12-31和2023-12-30，本来连续的1月1日到1月2日也被拆成了两个组，统计结果彻底错乱。

所以无论原始数据是不是一天多条，第一步必须做GROUP BY user_id, login_date或SELECT DISTINCT user_id, login_date。我在真实数据环境中还会再加一点：先确认时间字段没有带时分秒，或者至少要CAST(login_time AS DATE)，否则2024-01-01 08:00和2024-01-01 23:00也会被当成两条。

4.2 排序函数选哪个：row_number用在前，rank/dense_rank用在哪

去重之后，每个用户在同一个日期只有一条记录，所以日期之间不存在并列，ROW_NUMBER()是最自然的选择。有些文章喜欢用DENSE_RANK()，但那是处理"同一天多条记录"时的补救方案，不是标准解法。

这里顺便说一个面试经常追问的点：如果不先去重，直接用DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date)，同一天的记录会拿到同一个排名，但这会让后续date_sub(login_date, rn)中的rn代表"不同日期的个数"，虽然能变相做到去重效果，可一旦日期带分组外的其他维度，或者要输出明细，很容易让逻辑变得混乱。与其在排序函数上绕弯子，不如老老实实先去重。

4.3 字符串日期和跨年跨月的坑

Hive里如果日期字段是STRING，一定要保证所有值都是YYYY-MM-DD格式。我在实际处理中遇到过20240101、2024-1-1、2024/01/01混在一起的情况，date_sub处理不出来，排序也可能错乱。稳妥做法是统一regexp_replace或from_unixtime(unix_timestamp(...))转成同一格式，再参与计算。

跨年跨月本身不是问题，因为日期函数是按日历日计算的。比如104用户，从2023年12月29日连续到2024年1月2日：

group_flag依然一样，不会被月份或年份的边界打断。但如果有人自作聪明用SUBSTR(login_date, 1, 7)按月截串，再去做日期减序号，那跨年必出问题。

4.4 空值和脏数据必须在预处理阶段干掉

login_date为NULL时，ROW_NUMBER()排序后通常会在最前面，date_sub结果也是NULL，GROUP BY之后变成一个以NULL为分组标记的组，会污染统计结果。user_id为空同理，多个空用户会被并到同一个NULL组里。我在写生产脚本时，第一步就会加上WHERE user_id IS NOT NULL AND login_date IS NOT NULL，不要指望后续逻辑帮你去兜脏数据。

4.5 统计口径：连续N天、恰好N天、还是近N天

"连续登录3天及以上"和"恰好连续3天"的写法略有不同。前者用HAVING COUNT(*) >= 3，后者用= 3。但"恰好连续3天"有个隐藏问题：用户可能有一个连续4天的段，按恰好3天算他就不该被统计，可业务往往想的是"至少3天"。所以拿到需求第一件事是跟业务确认口径。

还有一个容易踩的是"近30天连续登录用户"。很多人直接WHERE login_date >= date_sub(current_date, 29)然后按连续段分组，但这样会把刚好发生在30天窗口边缘的连续段截断，比如一个用户从30天前第一天开始连续登录5天，窗口里其实只有4天，到底算不算"近30天连续5天"？不同业务有不同的答案。我在交付前一般会把口径写成一句话附在结果表描述里，避免后续扯皮。

5. 不止窗口函数：MySQL 5.x变量方案与lag方案的取舍

MySQL 8.0发布之前，窗口函数是没有的。但这道题出现的时间远早于MySQL 8.0，所以面试官经常追一句"如果你们库不支持窗口函数，怎么写？"下面这两个思路值得掌握。

5.1 为什么还要学非窗口函数方案

一方面，面试里问这个问题是在考察你到底是背模板还是真懂原理；另一方面，有些公司确实还在用MySQL 5.6/5.7，或者Hive版本比较老，真要落地时窗口函数不一定都开了。这时候变量方案或者自连接方案就是保底手段。

5.2 MySQL 5.x用户变量写法

用户变量的核心是：按user_id和login_date排序后，用变量记住上一个用户和上一个日期。如果当前用户和上一个用户相同，且当前日期正好比上一个日期多1天，说明连续，复用之前的组标记；否则重新开一组。

SET @prev_user := NULL; SET @prev_date := NULL; SET @grp_start := NULL; SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, @grp_start := IF( @prev_user = user_id AND DATEDIFF(login_date, @prev_date) = 1, @grp_start, login_date ) AS grp_start, @prev_user := user_id, @prev_date := login_date FROM ( SELECT DISTINCT user_id, login_date FROM user_login_log ORDER BY user_id, login_date ) t ) s GROUP BY user_id, grp_start HAVING COUNT(*) >= 3 ORDER BY user_id;

这个写法依赖MySQL在SELECT子句中从左到右计算用户变量的行为，属于实现细节，不是SQL标准。数据量不大时可以用，数据量大或者要长期维护的报表，我不建议把变量写到线上任务里，因为它太容易出现环境差异导致的结果漂移。面试展示思路没问题，生产还是优先升级到8.0或换Hive。

5.3 lag窗口函数：用"上一次登录日期"构造分组

除了"日期减序号"，还有一种很直观的解法：计算每个用户上一次登录日期，如果当前日期和上一次登录日期相差不是1天，说明这是一个新连续段的开始。然后把"是否新段"做窗口累加，得到段编号。

SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, SUM(is_new_group) OVER ( PARTITION BY user_id ORDER BY login_date ) AS group_id FROM ( SELECT user_id, login_date, CASE WHEN prev_login_date IS NULL OR DATEDIFF(login_date, prev_login_date) <> 1 THEN 1 ELSE 0 END AS is_new_group FROM ( SELECT user_id, login_date, LAG(login_date, 1) OVER ( PARTITION BY user_id ORDER BY login_date ) AS prev_login_date FROM ( SELECT DISTINCT user_id, login_date FROM user_login_log ) t ) pre ) mark ) t GROUP BY user_id, group_id HAVING COUNT(*) >= 3 ORDER BY user_id;

这个思路的好处是逻辑更接近"人怎么理解连续"，而且后续如果要判断断档天数，直接在CASE里改条件就行。缺点是比日期减序号多一层窗口聚合，数据量大时开销稍高。

5.4 三种方案怎么选

如果环境允许，我一般首选日期减序号；如果面试官追问，拿出lag方案聊思路；如果对方坚持要兼容MySQL 5.7，再给变量方案。

6. 从一道题到一类业务：连续登录统计的扩展

连续登录这个"连续"模型，能延伸出很多玩法。掌握核心思路之后，很多需求其实都是同一套打法换皮。

6.1 连续区间明细和最大连续天数

只输出连续天数还不够，很多运营活动要看具体起止日期。比如要奖励连续打卡7天以上的用户，最好把每个连续段的开始日期和结束日期都拉出来。

SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ) dedup ) seq ) t GROUP BY user_id, group_flag HAVING COUNT(*) >= 3 ORDER BY user_id, start_date;

最大连续天数就是在这个结果上取MAX(continuous_days)按用户折叠。注意一个用户可能有多个连续段，所以要先在段粒度聚合，再在用户粒度折叠，两步不能省。

6.2 从"按天连续"到"按周/月连续"

有些业务说的连续活跃不是每天都来，而是"每周至少来一次"。这时候可以把登录日期先归一到周，再判断周序列是否连续。Hive里可以用TRUNC(login_date, 'MM')或DATE_FORMAT(login_date, 'yyyy-MM')，MySQL里可以用DATE_FORMAT(login_date, '%Y-%m')，然后对"月粒度日期"做同样的日期减序号操作。

这里有个新坑：如果按周算，周一拉出来的用户可能周五周六活跃，周日没来，下周一来一次，中间隔了3天，但在周粒度上是连续的。要先定义好"一周的起点"是哪一天，再跨周判断。按周连续和按天连续的SQL结构完全一样，只是日期字段先做了粒度归并。

6.3 生产环境下的性能优化思路

先说Hive。窗口函数ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)在计算时需要把所有同用户的数据分到同一个Reduce里，如果某个超高频用户的数据量特别大，这个Reduce就会很慢，形成数据倾斜。常见的缓解办法是先按user_id和login_date做GROUP BY去重，减少数据量；再到外部过滤掉明显异常的超级用户，比如每天登录几十次的测试账号。如果倾斜依然严重，可以对user_id做哈希散列，把同一个用户拆到多个桶里，最后再把结果合并。

再说MySQL。给表加上(user_id, login_date)联合索引非常关键，因为第一步去重和窗口函数排序都依赖这个索引。数据量在百万级以内，这套SQL基本是毫秒到秒级；如果到千万级以上，还是考虑把数据同步到数仓用Hive/Spark跑，不要让在线库承担这种分析型查询。

还有一个小技巧：业务如果只需要看最近N天的连续登录，可以在最内层先加时间条件。比如只需要看近30天，就WHERE login_date >= DATE_SUB(CURDATE(), INTERVAL 29 DAY)，把数据量提前砍掉再开窗。但前面说的口径问题要自己把握好，跨窗口边界的连续段会不会被截断，一定要和业务确认。

最后说一点个人体会

这道题我前前后后给不同业务写过很多版本，最大的感受是：答案不难，难的是问清楚"连续"到底意味着什么。是先按天去重还是留着重复登录次数？连续3天是自然日还是活跃日？跨周怎么算？这些细节不确认，SQL写得再漂亮，产出的数字也是废的。我现在遇到类似需求，都会先把这两件事写在需求文档第一行：按什么粒度去重、连续按什么口径切分。确认完再写SQL，基本不会被返工。这个习惯，也是这道经典题真正教会我的东西。