做过用户增长或数据运营的朋友，应该都接到过这类需求：报表里要统计每个用户最长连续登录了多少天；或者用户断签几天后又回来，后续连续登录的序号得重新从1算起。这俩需求在Hive SQL里其实是同一类问题，都属于“连续区间分组”。我一开始接触时也觉得绕，绕清楚之后发现核心逻辑就一句话：用登录日期减去行号，差值相同的就是同一段连续区间。这篇文章我把完整思路、能用得上的SQL、还有实际生产里踩过的坑一起整理出来，代码可以直接复制改表名使用。
1. 先拆需求：连续登录到底在算什么
1.1 两类问题的本质是一个“分段”问题
先看第一个需求：“计算用户的最大连续登录天数”。假设某用户的登录日期是1号、2号、3号、5号、6号、9号。直观上我们能看出，1号到3号是一段连续3天，5号到6号是连续2天，9号是单独1天，所以最大连续登录天数是3。但计算机不会像我这样“看一眼就分段”，它需要一条明确规则来识别“断点”。
第二个需求“创建断连后的连续序号”，本质也完全一样。比如上面这个用户，我们希望输出：
- 1号：序号1
- 2号：序号2
- 3号：序号3
- 5号：序号1（因为4号断签，重新计数）
- 6号：序号2
- 9号：序号1
这就意味着，5号、6号必须和1号、2号、3号划分到不同组里，组内重新编号。两个需求如果分开处理，你可能写两遍SQL；如果能先给每条记录打上“连续区间分组ID”，那么最大连续天数就是对每个分组做 COUNT，断连后的序号就是对每个分组做 ROW_NUMBER。一个分组ID同时解决两个问题。
1.2 为什么不能简单按用户 GROUP BY
很多人第一反应是：按用户 group by，然后 count 登录天数，这只能算出“总登录天数”，不是“连续登录天数”。总登录天数不关心日期之间是否断裂，7天里有3天连续、2天连续、1天独立的用户，和7天全部连在一起的用户，总天数可能都是7，但业务含义差别很大。用户价值、留存分析、签到活动奖励，都是用连续天数而不是总天数来衡量的。
所以问题的难点在于：如何在Hive SQL中给“连续区间”打上同一个分组标识。接下来看核心解法。
2. 核心解法：日期减行号的差值法
2.1 差值为什么能标出连续区间
这是整个思路的基石，值得花两分钟彻底理解。对每个用户按登录日期排序后，Hive会生成一个行号 rn，从1开始递增。关键观察是：如果用户连续登录，那么登录日期每天加1，行号也每天加1，所以“日期 - 行号”的结果保持不变；一旦中间断了，日期增长出现跳跃，而行号依然是连续加1，于是“日期 - 行号”的结果就发生变化。
看这个例子：用户A登录了1号、2号、3号、5号、6号、9号。按日期排序后行号为1到6：
| 登录日期 | 排序行号 rn | 日期 - rn（用 date_sub 计算） |
|---|---|---|
| 2024-01-01 | 1 | 2023-12-31 |
| 2024-01-02 | 2 | 2023-12-31 |
| 2024-01-03 | 3 | 2023-12-31 |
| 2024-01-05 | 4 | 2024-01-01 |
| 2024-01-06 | 5 | 2024-01-01 |
| 2024-01-09 | 6 | 2024-01-03 |
看到没有，前3条记录的差值都是 2023-12-31，说明它们属于同一段连续登录；5号、6号的差值变成 2024-01-01，说明新开了一段；9号只有1条，差值又变了。这就好比给每一段连续记录贴上了同一张标签，后面的聚合就有了抓手。
这个技巧之所以好用，是因为它不需要知道用户到底断了几次、断在哪里，也不需要用 LAG/LEAD 做逐行判断。行号天然抵消了日期每天递增1的效应，留下的是“断点偏移量”。偏移量一变，组就变了。
2.2 准备一份可直接执行的测试数据
为了不让文章变成“纸上谈兵”，我们先在Hive里建一张用户登录明细表，并插入测试数据。
CREATE TABLE IF NOT EXISTS tmp_user_login ( user_id STRING COMMENT '用户ID', login_date STRING COMMENT '登录日期,格式yyyy-MM-dd' ) COMMENT '用户登录明细测试表'; INSERT OVERWRITE TABLE tmp_user_login VALUES ('user_001', '2024-01-01'), ('user_001', '2024-01-02'), ('user_001', '2024-01-03'), ('user_001', '2024-01-05'), ('user_001', '2024-01-06'), ('user_001', '2024-01-09'), ('user_002', '2024-01-01'), ('user_002', '2024-01-04'), ('user_002', '2024-01-05'), ('user_002', '2024-01-07'), ('user_002', '2024-01-08'), ('user_002', '2024-01-09');注意这里 login_date 用 STRING 类型存储，这是很多数仓表的常见做法，也正好能演示后面类型处理时容易踩的坑。如果你在生产表里是 DATE 或 TIMESTAMP 类型，原理完全一致，只是 CAST 步骤可以省掉。
2.3 完整SQL：一次算清最大连续登录天数
直接给出最终可运行的版本，然后逐步拆解。
WITH login_dedup AS ( SELECT user_id, CAST(login_date AS DATE) AS login_date FROM tmp_user_login GROUP BY user_id, CAST(login_date AS DATE) ), login_with_flag AS ( SELECT user_id, login_date, DATE_SUB( login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) ) AS grp_date FROM login_dedup ), login_with_days AS ( SELECT user_id, grp_date, COUNT(*) AS continuous_days FROM login_with_flag GROUP BY user_id, grp_date ) SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM login_with_days GROUP BY user_id ORDER BY user_id;执行结果：
| user_id | max_continuous_days |
|---|---|
| user_001 | 3 |
| user_002 | 3 |
user_001是1号到3号连续3天，user_002是7号到9号连续3天。数据里我还故意把user_002的第2段连续日期设计成4号、5号2天，7号到9号3天，最终取MAX就是3，证明聚合没问题。
这段SQL里每一层都有存在的理由：
- 第一层 login_dedup：把同一天多条登录记录压成一条。不然后面COUNT(*)会把同一天重复登录当成多天，最大连续天数就会虚高。
- 第二层 login_with_flag：用DATE_SUB生成连续分组标签grp_date，这是全篇的关键。
- 第三层 login_with_days：按用户和分组标签统计每段的连续天数。
- 最后一层：再按用户取MAX，得到最大连续登录天数。
如果只需要“每段连续登录的起止日期和天数”，第三层可以稍作修改：
SELECT user_id, grp_date, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM login_with_flag GROUP BY user_id, grp_date这个扩展很实用。运营报表里经常要列出“用户最近一段连续登录从哪天开始、哪天结束”，把这一层结果直接落地成中间表就行。
3. 断连后的连续序号：同一个分组标签再开一窗
3.1 段内序号如何重新从1开始
“断连后的连续序号”这个需求，最佳做法是把上面生成的 grp_date 拿过来，再加一个开窗函数。因为 grp_date 本身就是“段”的标识，每段内部重新编号，自然就能在断连后从1开始。
WITH login_dedup AS ( SELECT user_id, CAST(login_date AS DATE) AS login_date FROM tmp_user_login GROUP BY user_id, CAST(login_date AS DATE) ), login_with_flag AS ( SELECT user_id, login_date, DATE_SUB( login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) ) AS grp_date FROM login_dedup ) SELECT user_id, login_date, grp_date, ROW_NUMBER() OVER (PARTITION BY user_id, grp_date ORDER BY login_date) AS seq_in_group FROM login_with_flag ORDER BY user_id, login_date;结果：
| user_id | login_date | grp_date | seq_in_group |
|---|---|---|---|
| user_001 | 2024-01-01 | 2023-12-31 | 1 |
| user_001 | 2024-01-02 | 2023-12-31 | 2 |
| user_001 | 2024-01-03 | 2023-12-31 | 3 |
| user_001 | 2024-01-05 | 2024-01-01 | 1 |
| user_001 | 2024-01-06 | 2024-01-01 | 2 |
| user_001 | 2024-01-09 | 2024-01-03 | 1 |
USER_001在5号这天重新从1开始编号，因为它的grp_date从 2023-12-31 变成了 2024-01-01，分区条件发生变化，ROW_NUMBER重新计数。这就是“断连后的连续序号”的标准实现。
3.2 不用差值法，能不能用LAG判断断点
除了差值法，还有一个更“显式”的思路：用LAG函数求出每条记录的上一次登录日期，如果两次日期相差超过1天，就认为这是一个新的连续段的开始，然后用SUM开窗累积分组ID。SQL长这样：
WITH login_dedup AS ( SELECT user_id, CAST(login_date AS DATE) AS login_date FROM tmp_user_login GROUP BY user_id, CAST(login_date AS DATE) ), login_with_gap AS ( SELECT user_id, login_date, CASE WHEN DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) ) > 1 THEN 1 ELSE 0 END AS is_new_segment FROM login_dedup ), login_with_group AS ( SELECT user_id, login_date, SUM(is_new_segment) OVER (PARTITION BY user_id ORDER BY login_date) AS grp_id FROM login_with_gap ) SELECT user_id, login_date, grp_id, ROW_NUMBER() OVER (PARTITION BY user_id, grp_id ORDER BY login_date) AS seq_in_group FROM login_with_group ORDER BY user_id, login_date;这种写法的好处是语义直白：第一行开窗没有前面的日期，LAG返回NULL，NULL和任何日期DATEDIFF都是NULL，不会被判定为断点，所以第一段依然从0开始。后续只要日期差>1就是断点。SUM开窗会把所有断点累加起来，形成不断递增的组ID。
两种方法本质等价。差值的优势是代码更短、不需要考虑LAG的边界NULL问题；LAG的优势是“断点”概念直白，方便业务理解。实际项目里我更推荐差值法，因为它天然把“断点”转化成了分组键，后续不管是算天数、算序号、还是筛选最长连续段，都用同一个grp_date，逻辑统一不容易出错。
4. 实操环节：从时间戳、去重到性能优化的细节
4.1 登录时间戳怎么处理
真实业务表里，登录记录未必是“日期”粒度，更多是完整的时间戳，比如 2024-01-01 10:23:45。这时候不能直接拿来算连续天数，必须先截断到日期粒度，否则同一天的不同时间会算成不同日期，连续到今天就会断裂。
时间戳如果已经是 TIMESTAMP 类型，直接用 TO_DATE 转换：
SELECT user_id, TO_DATE(login_ts) AS login_date FROM user_login_log GROUP BY user_id, TO_DATE(login_ts);如果时间戳是 BIGINT 类型的Unix秒数，先转成日期字符串再转日期：
SELECT user_id, TO_DATE(FROM_UNIXTIME(login_ts)) AS login_date FROM user_login_log GROUP BY user_id, TO_DATE(FROM_UNIXTIME(login_ts));如果原始日期是 20240101 这种紧凑格式的字符串，需要先格式化：
SELECT user_id, TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(login_dt, 'yyyyMMdd'))) AS login_date FROM user_login_log GROUP BY user_id, TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(login_dt, 'yyyyMMdd')));这一步常见的原因是：业务方给的表和我们的标准日期格式不统一。在进入开窗计算之前，保证“每个用户每天只有一条记录”，是整个SQL的第一步，也是最重要的一步。
4.2 去重为什么不推荐直接用 DISTINCT
很多新手会写 SELECT DISTINCT user_id, login_date FROM ...，但从Hive底层执行来看，DISTINCT在大表场景下会触发全局去重，数据倾斜概率不低。用 GROUP BY user_id, login_date 的效果相同，而且更可控。更重要的是，你很可能还要在去重的同时清洗格式、过滤掉异常登录时间，GROUP BY 的写法能把这些逻辑都并进去。
当然，小表无所谓。但如果这是几亿行的大表，建议把去重结果先落到一张中间表或者临时表，而不是每次跑报表都扫一遍原始日志。例如：
CREATE TABLE IF NOT EXISTS dws_user_login_day AS SELECT user_id, TO_DATE(login_ts) AS login_date FROM dwd_user_login_log WHERE dt >= '2024-01-01' GROUP BY user_id, TO_DATE(login_ts);后面的连续登录计算直接读 dws_user_login_day，每天刷一次，既不反复扫描大表，又能保证报表口径一致。
4.3 字段类型不同导致的坑
我见过不止一次因为 login_date 是STRING而直接 DATE_SUB 导致结果变成NULL的情况。Hive的DATE_SUB函数第一参数支持STRING，但要求字符串能被转换成日期，格式必须是 yyyy-MM-dd。如果你的字段是 20240101 这种格式，函数不会在前面报错，但在某些Hive版本里会返回NULL，后面积累下来整段都塌掉。
解决办法就是先统一转成DATE类型：
DATE_SUB(CAST(login_date AS DATE), rn)还有另一个容易忽略的点：DATE_SUB 的第二个参数是INT类型的“天数偏移”。如果行号很大，跨月、跨年都没有问题，因为函数内部处理的是真实日期。这比手动拼接字符串再截断要安全得多。
注意：SQL里先做 CAST(login_date AS DATE) 再做 DATE_SUB，顺序不要反过来。字符串直接减整数在某些版本会产生隐式转换，一旦格式不符合预期，结果就是NULL，排查起来很费时间。
4.4 大表场景下的性能优化思路
差值法需要按 user_id 分区排序，本质上是每个用户的数据都要做一次分布键处理。用户量特别大时，个别头部用户有海量登录记录，会产生一定程度的数据倾斜。我是这样处理的：
- WHERE 条件先在日期上过滤：多数连续登录统计只需要最近90天或180天的数据，提前过滤能大幅缩小输入量。历史用户已经断签很久的，没必要参与计算。
- 如果只需要“最大连续登录天数”，可以不用等全量开窗。先按 user_id 做MAP端预聚合，把同一天的记录先合并，再进入开窗排序阶段。这也是为什么我把去重独立成第一层子查询的原因。
- Hive执行引擎如果开启了CBO和向量化，开窗函数性能会有明显提升。如果你用的是较老版本，尝试把
set hive.exec.parallel=true打开，多个用户分区的处理并行度会更高。 - 对于超大用户量，可以把 user_id 先分桶存储，比如按 user_id 哈希分桶，让相同用户的数据落到同一个文件，开窗计算时数据本地性更好，shuffle量也小一些。
4.5 连续天数的业务口径要提前确认
“连续”这两个字在业务里可能被定义出不同版本。最常见的口径是“自然日连续”，也就是今天和昨天都登录了就算连续。但有的产品定义“工作日连续”，需要剔除周末；有的定义“活跃连续”，只要两次活跃间隔不超过48小时就算连续；还有的签到活动跨月时要把1号连接到上个月最后一天。不同口径下，判断连续的基准完全不一样。
差值法适用于“自然日连续”这个最常见的口径。如果业务要求剔除周末，你得先做一张日历维表，标记每个日期是否为工作日，然后只保留工作日记录，再套用差值法。如果业务要求“间隔不超过48小时”，那就是会话切分问题，不能用简单日期差去套。接到需求时先确认口径，是节约返工最有效的动作。
5. 常见报错与排查技巧
5.1 DATE_SUB结果全是NULL
现象：grp_date列出现大量NULL，后续分组全部错乱。
排查顺序：
- 先看 login_date 的原始格式，用
SELECT login_date, CAST(login_date AS DATE) AS d FROM ... LIMIT 100单独验证是否转换成功。 - 检查是否有个别行的日期格式异常，比如带时间、带中文、空字符串。这些脏数据在 CAST 时不会报错，只会变NULL。
- 在原始层把异常数据过滤掉，例如
WHERE login_date != '' AND login_date IS NOT NULL，再加CAST(login_date AS DATE)。
5.2 最大连续天数算出来比预期大
最常见的元凶是“同一天重复登录没去重”。一个用户当天登录10次，如果不去重，COUNT就会算成10天，最大值自然离谱。解决方案就是文章开头那一步 GROUP BY user_id, login_date，先把记录压到每天一条。
还有可能是日期过滤条件写错，比如把DATE_SUB(login_date, 1)当成当日字段去关联，导致同一日期关联出多天。排查时把中间表逐层输出，看到哪一层开始COUNT膨胀。
5.3 断连后的序号没有从1开始
如果 seq_in_group 出现了 4、5 这种不连续编号，说明 PARTITION BY 的分组键没写对。常见的错误是只按 user_id 分区，忘了把 grp_date 加进去。ROW_NUMBER 只有在分区内才会重新从1计数，漏掉 grp_date 等于全用户一个大分区，序号自然一直累加。
另一种情况是 grp_date 本身有重复或NULL，这通常又回到日期格式的问题。先单独跑一层输出 user_id、login_date、grp_date，肉眼确认同一段记录 grp_date 相同，下一段记录 grp_date 不同，问题基本就定位了。
5.4 函数不兼容或语法报错
Hive 1.x 对WITH语法的支持不完全，如果你的环境版本较旧，可以改成三层子查询嵌套：
SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM ( SELECT user_id, grp_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(CAST(login_date AS DATE), ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY CAST(login_date AS DATE))) AS grp_date FROM tmp_user_login ) t1 GROUP BY user_id, grp_date ) t2 GROUP BY user_id;这种写法在任何支持开窗函数的Hive版本里都能跑，兼容性最好。
6. 从“最大连续天数”还能延伸出去什么
6.1 筛选连续登录超过N天的用户
实际场景里，“求每个用户最大连续天数”只是第一步，运营常常还要筛人，比如“找出近30天有过连续7天登录的用户”。这时不需要在外面套一层MAX，直接在分段结果里加 HAVING：
SELECT user_id, grp_date, COUNT(*) AS continuous_days FROM login_with_flag GROUP BY user_id, grp_date HAVING COUNT(*) >= 7;如果想同时拿到这批用户名单，再加一层 DISTINCT user_id 去重。
6.2 最近连续登录天数
很多用户画像表格展示的是“该用户最近一次登录到现在的连续活跃天数”，注意顺序是从今天往过去倒推。实现方法：按 user_id、grp_date 分组后，取每组最大登录日期最大的那一条，也就是最新一段：
WITH segment_days AS ( SELECT user_id, grp_date, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM login_with_flag GROUP BY user_id, grp_date ), latest_segment AS ( SELECT user_id, start_date, end_date, continuous_days, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY end_date DESC) AS rn FROM segment_days ) SELECT user_id, start_date, end_date, continuous_days FROM latest_segment WHERE rn = 1;如果用户最近登录日期已经很遥远，这段数据反映的就是“最后一段连续活跃”，不是“至今为止的活跃连续”。真正要算“至今连续”，得先限定用户最近登录日期是今天或昨天，否则概念不同，需要跟业务对齐。
6.3 连续登录天数分布统计
如果把所有用户的“每段连续天数”都汇总起来，可以统计某一段时间内连续登录天数的分布情况，比如连续1天、2-3天、4-7天、8-14天、15天以上的用户各有多少。这个结果在留存分析和活动效果评估里很有价值。实现就是在分段结果基础上做分桶：
SELECT CASE WHEN continuous_days = 1 THEN '1天' WHEN continuous_days BETWEEN 2 AND 3 THEN '2-3天' WHEN continuous_days BETWEEN 4 AND 7 THEN '4-7天' WHEN continuous_days BETWEEN 8 AND 14 THEN '8-14天' ELSE '15天+' END AS days_bucket, COUNT(DISTINCT user_id) AS user_cnt FROM segment_days GROUP BY CASE WHEN continuous_days = 1 THEN '1天' WHEN continuous_days BETWEEN 2 AND 3 THEN '2-3天' WHEN continuous_days BETWEEN 4 AND 7 THEN '4-7天' WHEN continuous_days BETWEEN 8 AND 14 THEN '8-14天' ELSE '15天+' END;这套逻辑放在活动复盘里，能直接回答“这次签到活动到底拉动了多少用户进入连续活跃状态”。
7. 我平时写这类SQL的几个习惯
最后分享几个实操小习惯。第一，所有连续问题先统一到“日期粒度”，不管原始数据是时间戳还是字符串，第一步永远是清洗成每个用户每天一行，这一步做不干净，后面再精妙的算法都白搭。第二，把“分组标签”grp_date单独抽成一个字段落到中间表，这样既能算最大连续天数，也能算断连后序号，后续加需求不用重写整条链路。第三，写窗口函数时先验证排序字段类型一致，避免隐式转换带来莫名其妙的性能问题。
这个需求其实还能延伸很多，比如计算最大连续签到金额、连续消费间隔、连续活跃月份。核心都是同一个差值法。理解了“日期减行号”的物理意义，你在Hive里遇到任何连续区间问题，基本都能用同一套模板去套。
