企业官网建设流程全解析

做用户运营的同学，隔三差五就会丢过来一个需求："帮我算一下每个用户的最大连续登录天数。"听起来确实不难，但真正写 HiveSQL 的时候，不少人的第一反应是GROUP BY user_id然后COUNT(DISTINCT login_date)——这个写法只能告诉你"总共登录了多少天"，跟"连续"两个字完全不沾边。连续登录不是一个简单的累加值，它本质上是一种状态：要么和前一天接上了，要么断开了。这篇文章我从最基础的"断点识别"讲起，把最大连续登录天数和断连后的连续序号两件事一次讲透，里面的 SQL 思路可以直接套到你的业务表上，改个表名就能跑。

1. 先把"连续登录"的口径说死：日期差一个单位才算连续

1.1 业务口径决定了 SQL 怎么写

动手写代码之前，我强烈建议先确认清楚几个口径问题，不然代码写完了，运营和数仓对不上口径，返工成本很高。

第一，"连续"按自然日算还是按工作日算？绝大多数场景是自然日：1月1日、1月2日、1月3日三天都登录，算连续3天；如果是工作日口径，周末不登录不算断，那就得引入日历表，复杂度完全不一样。本文按自然日处理，这也是最常见、最不需要争论的场景。

第二，同一天多次登录算几次？通常算一次，但如果不先去重，后续所有逻辑都会出错。这个坑我在第 5 章单独展开，很多线上事故都是从这里来的。

第三，统计到当天还是截至昨天？如果用户今天还没登录，把今天算进去会得到一个虚假的连续天数，所以一般建议按"截至昨天的完整数据"计算，避免把"进行中的今天"和"已经结束的历史数据"混在一起。

1.2 先造一张演示表，方便后面手工验算

为了后面讲解不抽象，我建一张最简单的登录表，字段就三个：用户ID、登录日期、登录时间（用来模拟原始日志）。

CREATE TABLE user_login ( user_id STRING, login_date STRING COMMENT '格式: yyyy-MM-dd', login_time STRING ) COMMENT '用户登录记录';

造一批覆盖各种情况的演示数据。注意我故意加了"同一天登录两次"和"跨月连续"这两种容易被忽略的场景：

先手工算一下预期结果：u001 最大连续登录天数是 3（1月1日到3日一段，1月5日到7日一段，1月30日到2月1日跨月一段），u002 最大连续登录天数是 2（1月3日到4日）。注意到 u001 的第三段横跨了 1 月和 2 月，这说明"跨月连续"必须靠真正的日期运算去判断，不能拿天数字符串去硬减，否则年份一变就废了。

2. 为什么 date_sub(login_date, row_number) 能自动切出连续段

2.1 数学原理：行号永远加 1，日期只在连续时加 1

先看最经典的断点识别写法：

SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM user_login;

这里rn是关键：它是行号，永远严格递增，且每次只增加 1。而login_date什么时候增加 1？只有两天连续的时候，日期差值才等于 1；一旦中间断了（比如 1月3日之后跳到 1月5日），日期一下增加了 2。

把两者放在一起看：date_sub(login_date, rn)相当于"用实际日期减去行号"。连续的日子里，日期涨的幅度恰好被行号的增长抵消，差值保持不变；断开的当天，日期涨幅超过行号涨幅，差值整体被"顶"到一个新位置。结果就是：同一段连续日期算出来的date_sub值完全一样，不同段的值不一样。

这招把"是不是连续"变成了"分组是否相同"，而分组是 SQL 最擅长干的事。理解这一点之后，后面所有写法都是它的变体。

2.2 手工推演一遍，比看十遍文档都管用

下面用 u001 去掉重复日期后的数据手工推演：

看出来了吧：2023-12-31对应 1月1日到 3日这 3 天，2024-01-01对应 1月5日到 7日这 3 天，2024-01-23对应 1月30日到 2月1日这 3 天。三组，每组恰好是一段连续的登录区间，COUNT(*)就是这段区间的天数。

2.3 另一条思路：用 LAG 找断点，适合需要断点细节的场景

如果不用"日期减行号"，也可以逐行比较"上一条记录的日期"，一旦发现datediff(当前日期, 上一条日期) != 1，就认为新开一段：

SELECT user_id, login_date, CASE WHEN LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) IS NULL OR datediff(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) <> 1 THEN 1 ELSE 0 END AS is_new_group FROM ( SELECT user_id, login_date FROM user_login GROUP BY user_id, login_date ) t;

注意LAG取出来的上一行如果为空（每组第一行），必须当成新段处理，这个IS NULL判断漏掉的话，第一段会被直接吞掉。这个思路的优点是直观，还能顺便知道断开的间隔天数；缺点是同一个窗口函数要写两遍LAG，后续还要再套一层SUM() OVER (...)才能生成段号，效率略低。所以我的习惯是：纯算连续天数优先用date_sub减行号；需要分析断连原因、计算断了几次 的时候，才用LAG方案。

3. 最大连续登录天数：完整实现与三种写法取舍

3.1 主实现：五步走，两层嵌套

把上面的思路串完整，按"去重 → 打行号 → 减出分组键 → 按用户和分组键计数 → 取最大值"执行：

WITH dedup AS ( SELECT user_id, login_date FROM user_login GROUP BY user_id, login_date ), rn_data AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ), grp_data AS ( SELECT user_id, login_date, date_sub(login_date, rn) AS grp_date FROM rn_data ), cnt_data AS ( SELECT user_id, grp_date, COUNT(*) AS consecutive_days FROM grp_data GROUP BY user_id, grp_date ) SELECT user_id, MAX(consecutive_days) AS max_consecutive_days FROM cnt_data GROUP BY user_id;

对照第 1.2 节的预期：u001 三段都是 3 天，最大连续 3；u002 只有 1月1日 是孤立一天（连续值 1），1月3日和 4日是连续两天（连续值 2），最大连续 2。和你手工数出来的结果一致，说明逻辑闭环了。

3.2 顺手把"每一段连续区间的起止日期"也输出

运营很多时候不光要知道最大连续天数，还想看"用户最长的那段是从哪天到哪天"。把倒数第二步的结果稍微改一下即可：

WITH dedup AS (...), rn_data AS (...), grp_data AS (...) SELECT user_id, grp_date, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM grp_data GROUP BY user_id, grp_date HAVING COUNT(*) >= 3;

HAVING COUNT(*) >= 3可以直接筛出"连续 3 天及以上"的所有区间，在活动奖励、连续签到打卡场景里很常用。这里要注意，grp_date本身没有业务含义，它只是分组键，不要试图去解释"2023-12-31 这个日期代表什么"，它唯一的作用就是区分不同的连续段。

3.3 三种实现方式的取舍

我把几条主流路线放在一起对比，方便你按场景选：

在 Hive 里，递归 CTE 的支持一直偏弱，而且递归在分布式引擎上的性能很差，除非业务规则复杂到窗口函数实在绕不过去，否则不要轻易上。能在两个窗口步骤内解决的问题，没必要引入额外的计算模型。

4. 断连后的连续序号：段编号与段内第 N 天怎么同时生成

4.1 先弄清楚"断连后的连续序号"指什么

这类需求在运营后台里通常有两种表现：一种是"给每一段连续登录打段号"，比如 u001 的三段分别编号为 1、2、3；另一种是"在每一段内部给天数编号"，段内第 1 天、第 2 天、第 3 天，一旦断开重新从 1 开始。标题里说的"断连后的连续序号"，我理解更偏向第二种：序号只在连续段内递增，换个段就重置。

这两种需求经常一起出现，一个标注"这是第几段"，一个标注"这一段里的第几天"，同时输出是最实用的做法。

4.2 段内序号：分组键上再做一次 row_number

核心做法是在date_sub生成的分组键基础上，再按user_id + grp_date做一次ROW_NUMBER()：

WITH dedup AS ( SELECT user_id, login_date FROM user_login GROUP BY user_id, login_date ), rn_data AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM dedup ) SELECT user_id, login_date, date_sub(login_date, rn) AS grp_date, ROW_NUMBER() OVER (PARTITION BY user_id, date_sub(login_date, rn) ORDER BY login_date) AS seq_in_streak FROM rn_data ORDER BY user_id, login_date;

我在PARTITION BY里直接写了date_sub(login_date, rn)表达式，多数 Hive 版本是支持的；如果担心兼容性，就先用 CTE 把grp_date算出来再引用，可读性也更好。输出结果大致如下：

seq_in_streak就是你要的"断连后重新开始的连续序号"：1、2、3、1、2、3、1、2、3。这个字段可以直接用于"连续签到第 N 天给奖励"这类逻辑，读出的 N 就是用户当前处在连续态势里的位置。

4.3 段编号 + 段内序号一起输出

如果还想知道"当前是第几段"，可以按grp_date排序后取DENSE_RANK()：

WITH base 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 ( SELECT user_id, login_date FROM user_login GROUP BY user_id, login_date ) t ) SELECT user_id, login_date, DENSE_RANK() OVER (PARTITION BY user_id ORDER BY grp_date) AS streak_no, ROW_NUMBER() OVER (PARTITION BY user_id, grp_date ORDER BY login_date) AS seq_in_streak FROM base ORDER BY user_id, login_date;

streak_no的语义是"用户的第几段连续期"，seq_in_streak是"该段内的第几天"。这两个字段配合使用时，还能做不少分析，比如筛出seq_in_streak = 1的行，观察用户每次重新开始连续登录的时间点，往往能对应上活动拉新或流失召回的动作节点。

5. 去重、跨月、数据倾斜：这几个坑建议提前排掉

5.1 同一天多次登录产生的虚假连续段

这是我在实际数仓作业里见过最多的错误，没有之一。一旦表里有重复日期，ROW_NUMBER()会把同一天当成多个行号，date_sub(login_date, rn)就会算歪，凭空造出一个不存在的连续段。

举例子：只有 1月1日登录两次、1月2日登录一次。不去重的话，行号分别是 1、2、3，日期减行号得到 2023-12-31、2023-12-30、2023-12-30，后两行被归成一组，计数为 2 天——而实际上用户在 1月1日到 2日只有 2 个自然日。更隐蔽的是，重复登录还可能让"1月1日两次 + 1月2日一次"被算成一段 2 天的连续，看起来答案没错，但中间的分组逻辑已经乱了。所以第一步永远是先GROUP BY user_id, login_date去重，或者用SELECT DISTINCT，效果一样。

5.2 日期类型与跨年判断

Hive 的date_sub和datediff都要求日期参数能被正确解析。如果你的表里存的是login_time这种带时分秒的时间戳，必须先TO_DATE(login_time)转成日期再参与运算，否则同一天不同时段的记录会被当成不同日期，连续天数被明显拉高。

跨年场景（比如 2024-12-31 和 2025-01-01）只要用真正的日期函数就天然正确，date_sub内部会处理月份和年份的进位，不需要手动判断年份变化。但如果偷懒把日期当字符串去截取和相减，跨年必翻车。这是我在评审别人 SQL 时一定会检查的点。

5.3 窗口函数的性能与数据倾斜

PARTITION BY user_id会把同一个用户的全部记录发送到同一个节点去排序，如果某个超级活跃用户有上亿条登录记录，而其他用户只有几条，就会出现典型的倾斜问题：整张大表的大任务是卡在少数几个节点上。遇到这种情况，我常用的应急手段是先用日期条件裁剪数据，比如只统计最近 90 天；如果倾斜依然严重，再考虑按天分桶打散。日常排查时，先看任务日志里是否有某个 Container 长时间跑不完，多半就是倾斜。

另外，ORDER BY login_date会触发全局排序，数据量大时尽量利用分桶表或让源数据本身按用户和日期有序，能省下不少时间。

5.4 上线前的人工抽验

最后分享一个我自己的习惯：任何窗口函数的计算结果，上线前先拿一小批用户手工核算一遍。比如用上面的演示数据，先人工算出 u001 最大连续天数是 3，再跑 SQL 看输出是不是 3；用 u002 验证孤立一天和连续两天的边界。连续登录这类逻辑一旦出错，往往不是整体报错，而是"看起来差不多但数字差一点"，这种问题排查成本非常高，人工抽验是成本最低的兜底手段。

如果跑出来的结果和预期不一致，优先检查两件事：查重没有，以及时间戳转日期了没有。我处理过的线上问题里，这两个检查项大约能覆盖八成以上的情况。剩下的两成，再看数据分区是否完整、有没有历史补数和回溯更新造成的重复记录，基本就都能定位了。