一、题目背景

这道题是 SQL 面试中"连续问题"的终极形态，字节、阿里、腾讯的面试中反复出现。它的核心技巧——日期减 ROW_NUMBER 差值法——可以用来解决所有"连续XXX"类问题。

二、题目

现有一张用户登录日志表 t17_zj_user_login，记录了用户每天的登录情况（每天每用户至多一条记录）。请找出每个用户的最大连续登录天数。

t17_zj_user_login 表

复制

---------- ------------- | user_id| login_date| ---------- ------------- | 1| 2025-01-01|| 1| 2025-01-02|| 1| 2025-01-03|| 1| 2025-01-05|| 1| 2025-01-06|| 1| 2025-01-07|| 2| 2025-01-01|| 2| 2025-01-02|| 2| 2025-01-04|| 2| 2025-01-05|| 3| 2025-01-03|| 3| 2025-01-04| ---------- -------------

期望输出：每个用户的最大连续登录天数。

三、思路分析

核心是日期 - ROW_NUMBER 差值法：

原理图解：

复制

用户1 的日期:01-01 → ROW_NUMBER=1 → 差值=01-01 - 1 = 12-3101-02 → ROW_NUMBER=2 → 差值=01-02 - 2 = 12-31← 相同的差值！01-03 → ROW_NUMBER=3 → 差值=01-03 - 3 = 12-31← 相同的差值！01-05 → ROW_NUMBER=4 → 差值=01-05 - 4 = 01-01← 断了一天，差值变了01-06 → ROW_NUMBER=5 → 差值=01-06 - 5 = 01-0101-07 → ROW_NUMBER=6 → 差值=01-07 - 6 = 01-01分组1 (差值12-31): 3天|分组2 (差值01-01): 3天→ 最大3天

四、逐步推导

步骤1：生成 ROW_NUMBER

Spark SQL

复制

SELECTuser_id,login_date,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rnFROM t17_zj_user_login;

执行结果

复制

---------- ------------- ----- | user_id| login_date| rn| ---------- ------------- ----- | 1| 2025-01-01| 1 || 1| 2025-01-02| 2 || 1| 2025-01-03| 3 || 1| 2025-01-05| 4 || 1| 2025-01-06| 5 || 1| 2025-01-07| 6 || 2| 2025-01-01| 1 || 2| 2025-01-02| 2 || 2| 2025-01-04| 3 || 2| 2025-01-05| 4 || 3| 2025-01-03| 1 || 3| 2025-01-04| 2 | ---------- ------------- ----- 12 rows selected (8.494 seconds)(https://www.dwsql.com)

步骤2：日期减 ROW_NUMBER

Spark SQL

复制

SELECTuser_id,login_date,DATE_SUB(login_date, rn) AS grpFROM (SELECT user_id, login_date,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rnFROM t17_zj_user_login) t;

执行结果

复制

---------- ------------- ------------- | user_id| login_date| grp | ---------- ------------- ------------- | 1| 2025-01-01| 2024-12-31|| 1| 2025-01-02| 2024-12-31|| 1| 2025-01-03| 2024-12-31|| 1| 2025-01-05| 2025-01-01|| 1| 2025-01-06| 2025-01-01|| 1| 2025-01-07| 2025-01-01|| 2| 2025-01-01| 2024-12-31|| 2| 2025-01-02| 2024-12-31|| 2| 2025-01-04| 2025-01-01|| 2| 2025-01-05| 2025-01-01|| 3| 2025-01-03| 2025-01-02|| 3| 2025-01-04| 2025-01-02| ---------- ------------- ------------- 12 rows selected (0.409 seconds)(https://www.dwsql.com)

步骤3：按差值分组，求最长连续天数

Spark SQL

复制

SELECTuser_id,MAX(consecutive_days) AS max_consecutive_daysFROM (SELECTuser_id,grp,COUNT(*) AS consecutive_daysFROM (SELECTuser_id,login_date,DATE_SUB(login_date, ROW_NUMBER() OVER (PARTITIONBY user_id ORDERBY login_date)) AS grpFROM t17_zj_user_login) tGROUPBY user_id, grp) t2GROUPBY user_idORDERBY user_id;

最终结果：

复制

---------- ----------------------- | user_id| max_consecutive_days| ---------- ----------------------- | 1| 3 || 2| 2 || 3| 2 | ---------- ----------------------- 3 rows selected (0.805 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：DATE_SUB 的参数顺序

DATE_SUB(date, days) —— 第一个是日期，第二个是天数。如果用错成 DATE_SUB(rn, login_date) 会得到错误结果或者报错。

坑2：Spark SQL 的 DATE_SUB

Spark SQL 中 DATE_SUB(login_date, rn) 正确处理整数 rn。但在 Hive 中可能需要 DATE_SUB(login_date, CAST(rn AS INT))。

坑3：同一天多条登录记录

本例假设每天每用户只有一条记录。如果有重复，需要先 SELECT DISTINCT user_id, login_date 去重，否则 ROW_NUMBER 会编号到同一天的重复记录，差值计算会出错。

六、举一反三

七、知识点总结

八、建表语句和数据插入

复制

CREATE TABLE IF NOT EXISTS t17_zj_user_login (user_id INT,login_date STRING);INSERT INTO t17_zj_user_login VALUES(1, '2025-01-01'),(1, '2025-01-02'),(1, '2025-01-03'),(1, '2025-01-05'),(1, '2025-01-06'),(1, '2025-01-07'),(2, '2025-01-01'),(2, '2025-01-02'),(2, '2025-01-04'),(2, '2025-01-05'),(3, '2025-01-03'),(3, '2025-01-04');本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。原始发表：2026-06-21，如有侵权请联系[email protected] 删除