1. 从业务场景到技术需求：为什么需要统计连续次数？
在数据仓库和数据分析的日常工作中，我们经常会遇到一类看似简单、实则考验数据处理功底的场景：统计连续发生的次数。比如，一个用户连续登录的天数、一个设备连续报错的次数、一支股票连续上涨的交易日、一个用户连续购买同一商品的次数等等。这类需求的核心在于识别一个序列中，某个状态或事件“不间断”地持续了多久。
乍一看，这似乎是个简单的计数问题。但当你真正在 Hive 里动手写 SQL 时，就会发现传统的COUNT和GROUP BY组合拳在这里完全失效。因为它们只能统计总数，无法识别“连续性”这个关键约束。你可能会想到用窗口函数，但具体怎么用，才能把一堆离散的记录，变成一个个清晰的“连续区间”呢？这就是本篇要解决的核心问题。
我处理过很多类似的需求，从用户行为分析到系统监控告警，统计连续次数都是高频操作。掌握这个技巧，不仅能让你写 SQL 更得心应手，更能深刻理解窗口函数在解决复杂序列问题时的强大威力。下面，我们就从最基础的思路拆解开始，一步步构建出稳健的解决方案。
2. 核心思路拆解：如何用 SQL 思维定义“连续”？
要统计连续次数，首先得用 SQL 的语言把“连续”这个概念量化。我们面对的通常是一张事实表，至少包含三个关键字段：主体标识（如 user_id）、状态或事件（如 login_status）、序列依据（通常是时间戳或自增的序列号，如 event_date）。
假设我们有一张用户登录表user_login：
user_id | login_date | status --------|------------|------- 1001 | 2023-10-01 | 1 1001 | 2023-10-02 | 1 1001 | 2023-10-03 | 1 1001 | 2023-10-05 | 1 1001 | 2023-10-06 | 1 1002 | 2023-10-01 | 1 1002 | 2023-10-03 | 1我们的目标是找出每个用户连续登录的天数。从数据上看，用户1001在10月1、2、3号连续登录了3天，5、6号连续登录了2天。10月4号没有记录，所以连续性在3号中断了。
关键思路：制造一个“分组标签”解决这个问题的核心技巧，是为原本连续的记录人为地制造一个“断点标记”。如果所有日期是真正连续的（比如1，2，3，4，5...），那么日期减去一个自增的序号（比如1，2，3，4，5...），得到的差值会是一个常数。一旦序列中断，这个差值就会变化。这个变化的差值，就可以作为我们分组（即区分不同连续区间）的完美标签。
具体到上面的例子，如果我们为每个用户的登录记录按日期排序并赋予一个行号（rank），那么：
- 对于用户1001的第一个连续区间（1，2，3号）：
login_date - rank的结果都是2023-09-30。 - 对于第二个连续区间（5，6号）：
login_date - rank的结果都是2023-10-01（因为行号从4开始算了）。
这样，原本需要肉眼判断的连续区间，就被一个可计算的字段grp_key清晰地区分开了。后续我们只需要按user_id和这个grp_key分组，就能轻松统计出每个连续区间的长度。这就是整个方案的灵魂所在。
注意：这个方法的基石是“序列依据”字段必须是可排序和可计算的，通常是日期或数字。如果序列依据是字符串或其他复杂类型，需要先转换为可操作的格式。
3. 实战演练：使用窗口函数构建连续区间分组
理解了核心思路后，我们进入实战环节。Hive SQL 提供了强大的窗口函数，正是实现上述思路的利器。我们以统计用户连续登录天数为例，一步步写出完整的 SQL。
3.1 数据准备与假设
首先，创建示例表并插入数据：
CREATE TABLE IF NOT EXISTS user_login ( user_id STRING, login_date DATE, status INT COMMENT '1表示登录成功' ); INSERT INTO user_login VALUES ('1001', '2023-10-01', 1), ('1001', '2023-10-02', 1), ('1001', '2023-10-03', 1), ('1001', '2023-10-05', 1), ('1001', '2023-10-06', 1), ('1002', '2023-10-01', 1), ('1002', '2023-10-03', 1), ('1002', '2023-10-04', 1), ('1002', '2023-10-07', 1);3.2 分步SQL实现
第一步，我们需要为每个用户的登录记录，按日期生成一个连续的行号。这里使用ROW_NUMBER()窗口函数。
SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM user_login WHERE status = 1 -- 假设我们只关心成功的登录 ORDER BY user_id, login_date;这一步的结果会为每个用户的记录打上序号标签（rn）。
第二步，也是最关键的一步，生成分组标签grp_key。根据前面的思路，我们用登录日期减去对应的行号。在 Hive 中，日期可以直接减去整数（代表天数）。
WITH numbered_logins AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM user_login WHERE status = 1 ) SELECT user_id, login_date, rn, date_sub(login_date, rn) AS grp_key -- 核心操作：日期 - 行号 FROM numbered_logins ORDER BY user_id, login_date;查看中间结果，你会发现对于用户1001，1、2、3号的grp_key都是2023-09-30，而5、6号的grp_key变成了2023-10-01。分组已经完成了。
第三步，基于分组标签进行聚合，计算每个连续区间的开始日期、结束日期和持续天数。
WITH numbered_logins AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM user_login WHERE status = 1 ), grouped_logins AS ( SELECT user_id, login_date, date_sub(login_date, rn) AS grp_key FROM numbered_logins ) SELECT user_id, grp_key, MIN(login_date) AS start_date, -- 连续区间的开始日 MAX(login_date) AS end_date, -- 连续区间的结束日 COUNT(*) AS consecutive_days -- 连续天数 FROM grouped_logins GROUP BY user_id, grp_key HAVING COUNT(*) >= 1 -- 这里可以过滤，比如只显示连续登录大于3天的记录 ORDER BY user_id, start_date;执行最终的查询，我们将得到如下结果：
user_id | grp_key | start_date | end_date | consecutive_days --------|-------------|------------|------------|----------------- 1001 | 2023-09-30 | 2023-10-01 | 2023-10-03 | 3 1001 | 2023-10-01 | 2023-10-05 | 2023-10-06 | 2 1002 | 2023-09-30 | 2023-10-01 | 2023-10-01 | 1 1002 | 2023-09-30 | 2023-10-03 | 2023-10-04 | 2 1002 | 2023-10-03 | 2023-10-07 | 2023-10-07 | 1这个结果清晰地展示了每个用户所有的连续登录区间。用户1001有两个连续区间，分别是3天和2天。用户1002有三个区间，分别是1天、2天和1天。
3.3 方案的核心要点与变体
为什么用ROW_NUMBER而不用RANK或DENSE_RANK？这是新手容易混淆的地方。ROW_NUMBER()会生成唯一的、连续递增的序号（1,2,3,4...）。RANK()在值相同时会给相同的排名，并跳过后续序号（1,2,2,4...）。DENSE_RANK()也会给相同值相同排名，但序号连续（1,2,2,3...）。在我们的场景中，登录日期可能重复吗？如果业务上允许同一用户同一天多次登录，并且我们按天去重了，那么日期是唯一的，这三个函数结果一样。但如果有重复日期且未去重，使用RANK或DENSE_RANK会导致日期 - 排名的计算出现错误，因为相同的日期减去不同的排名值会得到不同的grp_key，从而破坏分组逻辑。因此，使用ROW_NUMBER是最稳妥的选择，它能保证序号严格连续递增，为“日期-序号”的减法逻辑提供坚实基础。
序列依据不是日期怎么办？如果序列依据是数字ID（比如自增的日志ID），那么方法更简单，直接将这个数字字段减去ROW_NUMBER()即可。如果序列依据是格式复杂的时间戳字符串，你需要先用FROM_UNIXTIME、UNIX_TIMESTAMP或DATE_FORMAT等函数将其转换为标准的日期或数字类型，再进行计算。核心原则不变：让“序列依据”变得可进行算术运算。
4. 进阶场景与常见陷阱处理
掌握了基础方法，我们来看看一些更复杂的实际场景和容易踩的坑。
4.1 场景一：统计最大连续次数
业务方往往不关心所有连续区间，只想知道最长的那个。比如“用户历史最长连续登录天数”。这在上一步的结果上很容易计算，只需要再用一次聚合即可。
WITH consecutive_intervals AS ( -- 这里是上面完整的、计算出每个连续区间的SQL，作为一个子查询 SELECT user_id, COUNT(*) AS consecutive_days FROM ( -- ... 完整的分组逻辑 ... ) t GROUP BY user_id, grp_key ) SELECT user_id, MAX(consecutive_days) AS max_consecutive_days FROM consecutive_intervals GROUP BY user_id;4.2 场景二：状态连续，而非事件存在
我们之前的例子是统计“事件发生”的连续性（登录）。还有一种常见场景是统计“状态持续”的连续性。比如一张设备状态表，每分钟上报一次状态（0正常，1故障），我们需要计算设备连续故障的分钟数。
表结构可能如下：
device_id | report_time | status ----------|---------------------|------- d001 | 2023-10-01 10:00:00 | 1 d001 | 2023-10-01 10:01:00 | 1 d001 | 2023-10-01 10:02:00 | 0 d001 | 2023-10-01 10:03:00 | 1 d001 | 2023-10-01 10:04:00 | 1这时，我们不能简单地针对所有记录计算，而应该先筛选出状态为故障（status=1）的记录，然后再套用连续区间算法。因为状态为0的正常记录会打断连续性，我们根本不考虑它们。
WITH fault_records AS ( SELECT device_id, report_time, ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY report_time) AS rn FROM device_status WHERE status = 1 -- 只关注故障状态 ), grouped_faults AS ( SELECT device_id, report_time, -- 注意：这里需要将时间戳转换为一个可减的数字，比如转换成分钟级的时间戳或序号 -- 假设report_time是标准时间戳，我们将其转换为分钟数（或使用UNIX时间戳） (UNIX_TIMESTAMP(report_time) / 60) - rn AS grp_key_minute FROM fault_records ) SELECT device_id, MIN(report_time) AS fault_start, MAX(report_time) AS fault_end, COUNT(*) AS consecutive_fault_minutes FROM grouped_faults GROUP BY device_id, grp_key_minute ORDER BY device_id, fault_start;这个例子的关键在于WHERE status = 1这个过滤条件。它确保了进入连续区间计算的序列，本身就是由“故障”状态点构成的。任何“正常”状态点都不会出现在序列中，因此自然就成为了连续性的“断点”。这种方法比在完整序列上做复杂的状态判断要清晰高效得多。
4.3 陷阱一：数据缺失与日期断层
业务数据常有缺失。比如我们想查连续登录，但源表只记录了登录成功的日子，没登录的日子压根没记录。我们之前的方法完美适配这种情况，因为它正是基于“存在的记录”来识别连续的。但是，如果你的需求是“在连续的自然日维度上统计”，比如即使没登录，日期也不能断，那这就是另一个问题了，通常需要借助一张“日历维度表”进行关联补全，然后再判断状态，复杂度会高一个数量级。务必在动手前和业务方确认清楚“连续”的定义边界。
4.4 陷阱二：性能优化与数据倾斜
当数据量巨大时（例如数亿用户行为记录），这个计算可能会比较耗时，因为它涉及全局排序（ORDER BY）。两个优化思路：
- 分区裁剪：确保
WHERE条件能有效利用表的分区字段（如dt），减少数据扫描量。 - 减少排序开销：如果
PARTITION BY user_id ORDER BY login_date中的login_date是分区字段或者有索引，性能会好很多。在 Hive 中，可以考虑使用DISTRIBUTE BY user_id SORT BY login_date来替代窗口函数中的PARTITION BY ... ORDER BY ...，有时在特定版本和场景下能获得更好的性能。但要注意语义的完全等价。
另外，如果某个user_id的记录特别多（极端用户），会导致数据倾斜。可以考虑在初步聚合或过滤后，再使用窗口函数，或者使用 Hive 的倾斜优化参数，如set hive.groupby.skewindata=true;，但要根据实际情况测试效果。
5. 从 Hive 到 Spark SQL：思路的迁移与语法微调
现在很多数据处理任务已经迁移到了 Spark SQL 上。好消息是，这个“日期/数字减去行号”的核心思路在 Spark SQL 中完全通用，因为窗口函数是标准 SQL 的一部分。语法几乎可以原样照搬。
不过，有一些细微的差别需要注意：
- 函数名和语法兼容性：
ROW_NUMBER()、date_sub这些函数在 Spark SQL 中同样存在，行为一致。 - 性能特性：Spark SQL 基于内存计算，对于这类需要全局排序的窗口函数，如果数据量极大且分区不当，可能会遇到 executor 内存不足的问题。合理设置
spark.sql.shuffle.partitions参数很重要。 - 代码示例：在 Spark SQL 中，你可以这样写（以 PySpark DataFrame API 为例）：
from pyspark.sql import Window from pyspark.sql.functions import col, row_number, date_sub, min, max, count window_spec = Window.partitionBy("user_id").orderBy("login_date") df_with_rn = df.filter(col("status") == 1) \ .withColumn("rn", row_number().over(window_spec)) df_group_key = df_with_rn.withColumn("grp_key", date_sub(col("login_date"), col("rn"))) result_df = df_group_key.groupBy("user_id", "grp_key") \ .agg( min("login_date").alias("start_date"), max("login_date").alias("end_date"), count("*").alias("consecutive_days") ) \ .filter(col("consecutive_days") >= 2) \ .orderBy("user_id", "start_date")思路和 Hive SQL 一模一样，只是 API 的调用方式不同。掌握了核心算法，在不同引擎间切换就能游刃有余。
6. 真实案例复盘：电商场景下的连续购买行为分析
最后，分享一个我实际做过的电商案例，需求是：“找出在过去30天内，连续3天以上每天都有下单的用户，以及他们的连续购买时段”。
这个需求比单纯统计登录要复杂一点，因为它包含了时间窗口（过去30天）和连续性阈值（连续3天以上）。但核心方法依然不变。
步骤拆解：
- 数据过滤：从订单事实表中，筛选出指定时间范围内的成功订单，并按用户和日期去重（因为用户一天可能下多单，我们只关心“是否下单”这个事件）。
WITH user_daily_order AS ( SELECT DISTINCT user_id, DATE(order_time) AS order_date FROM order_fact WHERE order_status = 'success' AND order_time >= DATE_SUB(CURRENT_DATE, 30) ) - 构建连续区间：对上面去重后的数据，应用我们的标准连续区间算法。
numbered AS ( SELECT user_id, order_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn FROM user_daily_order ), grouped AS ( SELECT user_id, order_date, DATE_SUB(order_date, rn) AS grp_key FROM numbered ), consecutive_intervals AS ( SELECT user_id, grp_key, MIN(order_date) AS start_date, MAX(order_date) AS end_date, COUNT(*) AS consecutive_days FROM grouped GROUP BY user_id, grp_key ) - 应用业务规则：从所有连续区间中，筛选出长度大于等于3天的。
SELECT user_id, start_date, end_date, consecutive_days FROM consecutive_intervals WHERE consecutive_days >= 3 ORDER BY user_id, start_date;
这个案例的启示是，复杂业务需求往往是基础模式的组合。先通过过滤和去重将原始数据加工成“事件发生日期序列”，然后套用连续区间算法这个“公式”，最后再根据业务规则对结果进行筛选。这种化繁为简、分而治之的思维，在数据处理中非常宝贵。
踩坑心得：在这个案例中，最初我忽略了“同用户同日多订单”的去重，直接对订单流水使用窗口函数，导致ROW_NUMBER序号膨胀，order_date - rn的计算完全错误，得到了离奇的结果。所以，在使用这个算法前，务必确认你的“序列依据”字段在分组键（PARTITION BY的键）内是唯一且有序的。如果原数据不满足，就像上面那样，先用DISTINCT或GROUP BY处理好。磨刀不误砍柴工，明确数据的形态是写好 SQL 的第一步。
