求每个用户的最长连续登录天数。

考察要点

这道题面试官想看你是否掌握在 SQL 中处理时间序列和连续性问题的能力，具体考察：

1. 窗口函数（Window Functions）：特别是 ROW_NUMBER() 的使用，这是解决此类“分组内排序”问题的标准利器。
2. “Gaps and Islands”问题：能否识别出这是经典的“间隙与孤岛”问题，并运用创造性的分组技巧来解决。
3. SQL 抽象与逻辑拆解能力：能否将“连续登录”这个业务概念，转化为 (日期 - 连续天数) 这个固定的差值，从而进行分组（GROUP BY）。

核心概念

本题的核心是识别出“连续”的日期序列。一个巧妙的方法是，如果我们将一个连续日期序列中的每个日期，都减去它在这个序列中的排名（天数），会得到一个恒定的值。这个恒定的值就可以作为这一段“连续”日期的唯一标识。

1. 核心思想：日期与行号的差值分组

假设一个用户 U1 的登录日期如下（已去重并排序）：

我们使用窗口函数 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) 来为每个用户的登录记录生成一个行号 rn。

接下来，计算 login_date 减去 rn 天后的日期，我们称之为 group_flag：

观察：

• 前 3 条记录的 group_flag 都是 2022-12-31，它们构成了一个连续登录区间。
• 第 4、5 条记录的 group_flag 都是 2023-01-02，它们构成了另一个连续登录区间。
• 第 6 条记录自己构成一个区间。

这样，我们成功地将业务上的“连续”，转化为了数据上的“同组”。接下来只需要按 user_id 和 group_flag 分组计数，就能得到每个连续区间的长度，最后再取最大值即可。

2. 窗口函数 ROW_NUMBER()

• 语法: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
• 作用: 为分组（PARTITION BY）内的记录，按照指定的排序（ORDER BY）生成一个从 1 开始的唯一、连续的整数排名。
• 与 RANK() / DENSE_RANK() 的区别:
  • ROW_NUMBER(): 始终是 1, 2, 3, 4...
  • RANK(): 遇到相同排序值的行，排名相同，但会跳过后续排名（如 1, 2, 2, 4）。
  • DENSE_RANK(): 遇到相同排序值的行，排名相同，且不跳过后续排名（如 1, 2, 2, 3）。 在本题中，由于 login_date 经过 DISTINCT 后是唯一的，三者效果等价。但 ROW_NUMBER() 语义最清晰。

多种解法

假设我们有表 login_records：

 1CREATE TABLE login_records (
 2    user_id INT,
 3    login_date DATE
 4);
 5-- 插入一些示例数据
 6INSERT INTO login_records VALUES
 7(1, '2023-01-01'), (1, '2023-01-02'), (1, '2023-01-03'), (1, '2023-01-05'),
 8(1, '2023-01-06'), (2, '2023-02-10'), (2, '2023-02-11'), (2, '2023-02-13');

预期结果：

• 用户 1 的最长连续登录是 3 天（1/1-1/3）和 2 天（1/5-1/6），所以结果是 3。
• 用户 2 的最长连续登录是 2 天（2/10-2/11），所以结果是 2。

解法 1：窗口函数法（推荐，标准解法）

• 版本: ✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 不支持。
• 思路: 使用 ROW_NUMBER() 制造分组标识，然后进行两次聚合。

 1WITH user_distinct_dates AS (
 2    -- 步骤1: 对每个用户，获取去重且排序的登录日期
 3    SELECT DISTINCT user_id, login_date
 4    FROM login_records
 5),
 6user_date_with_rank AS (
 7    -- 步骤2: 使用窗口函数，为每个用户的登录日期打上排名
 8    SELECT
 9        user_id,
10        login_date,
11        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS rn
12    FROM user_distinct_dates
13),
14user_streaks AS (
15    -- 步骤3: 计算日期和排名的差值，以此作为连续区间的标识，并计算每个区间的长度
16    SELECT
17        user_id,
18        COUNT(*) AS streak_length
19    FROM user_date_with_rank
20    GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) -- 核心：差值分组
21)
22-- 步骤4: 从每个用户的所有连续区间中，找出最长的那个
23SELECT
24    user_id,
25    MAX(streak_length) AS max_consecutive_days
26FROM user_streaks
27GROUP BY user_id;

• 时间复杂度: $O(N \log N)$，主要开销在于 ROW_NUMBER() 内部的排序（PARTITION BY ... ORDER BY）。$N$ 是去重后的记录总数。
• 空间复杂度: $O(N)$，需要存储 CTE 的中间结果。
• 索引依赖: 在 login_records(user_id, login_date) 上建立复合索引，可以极大地加速 DISTINCT 和 ROW_NUMBER() 的排序过程，甚至可能实现覆盖索引，避免回表。

解法 2：用户变量法（兼容老版本 MySQL）

• 版本: ✅ MySQL 5.7 及以下版本；❌ MySQL 8.0+ 不推荐（官方不保证变量在复杂查询中的求值顺序）。
• 思路: 模拟过程式编程，在 SQL 查询中通过变量来跟踪上一个用户的 ID、上一个登录日期以及当前的连续天数。

 1SELECT
 2    user_id,
 3    MAX(streak) AS max_consecutive_days
 4FROM (
 5    SELECT
 6        user_id,
 7        login_date,
 8        -- 核心逻辑：
 9        -- 如果用户ID和上一个相同，且日期是连续的，则连续天数+1
10        -- 否则，重置连续天数为1
11        @streak := IF(@prev_user_id = user_id AND DATEDIFF(login_date, @prev_date) = 1,
12                       @streak + 1,
13                       1) AS streak,
14        -- 更新用于下一次比较的变量
15        @prev_user_id := user_id,
16        @prev_date := login_date
17    FROM (
18        -- 必须先对数据进行严格排序，保证变量逻辑的正确性
19        SELECT DISTINCT user_id, login_date
20        FROM login_records
21        ORDER BY user_id, login_date
22    ) AS sorted_logins,
23    -- 初始化用户变量
24    (SELECT @prev_user_id := NULL, @prev_date := NULL, @streak := 0) AS vars
25) AS streaks_with_vars
26GROUP BY user_id;

• 时间复杂度: $O(N \log N)$，主要开销在于子查询中的 ORDER BY。
• 空间复杂度: $O(1)$（不考虑排序的临时空间），因为变量是逐行计算的。
• 说明: 这种写法非常依赖于 MySQL 的执行顺序，可读性和可维护性差，是特定历史时期的“奇技淫巧”。在面试中可以提及，以展示知识广度，但一定要强调在现代数据库中应首选窗口函数。

常见低分回答

1. 误用 LAG 函数：尝试用 LAG(login_date) 获取上一行日期，然后判断 DATEDIFF 是否为 1。这种方法只能识别出连续的“点对”，但无法直接计算出整个连续区间的长度，后续处理会非常复杂，容易出错。
2. 错误地聚合：SELECT user_id, DATEDIFF(MAX(login_date), MIN(login_date)) + 1 FROM ... 这种写法计算的是用户总的登录时间跨度，完全忽略了中间的“断档”，是典型的对题意理解不清。
3. 忘记去重 DISTINCT：如果原始表一天内有多条登录记录，不先用 DISTINCT 或 GROUP BY user_id, login_date 进行预处理，会导致 ROW_NUMBER() 对同一天的多条记录生成不同行号，整个逻辑就错了。
4. 窗口函数 PARTITION BY 或 ORDER BY 错误：PARTITION BY 忘记写 user_id，导致在全局进行 ROW_NUMBER；或者 ORDER BY 写了其他无关列，都会导致 rn 计算错误。
5. 用户变量法中忘记 ORDER BY：用户变量法的正确性强依赖于数据的处理顺序。如果在子查询中不显式地 ORDER BY user_id, login_date，结果将是不可预测且错误的。

工程实践与优化

• 数据量级与性能：当 login_records 表达到亿级甚至百亿级时，直接在线查询（OLTP）执行此 SQL 会非常慢，可能导致数据库主库CPU飙升，甚至慢查询超时。$O(N \log N)$ 的排序成本是无法接受的。
• 生产方案：
  1. 离线计算：这是处理大规模数据的标准做法。将数据同步到数据仓库（如 Hive, Spark, ClickHouse）中，通过每日/每小时的批处理任务（如 Spark SQL）来计算。计算逻辑与解法 1 完全相同，但利用了分布式计算的水平扩展能力。
  2. 预聚合/物化视图：如果这个指标需要频繁查询，可以将计算结果（user_id, max_consecutive_days）存入一张结果表或物化视图中。前端应用直接查询结果表，实现毫秒级响应。
• 执行计划分析 (EXPLAIN)：
  • 对于解法 1，关注 EXPLAIN 结果。理想情况下，user_distinct_dates 这一步应该能利用 (user_id, login_date) 索引，Extra 字段显示 Using index for group-by 或 Using index。
  • ROW_NUMBER() 的计算通常会引入 Window aggregate 操作。如果排序键和分区键与索引匹配，性能会好很多。若不匹配，可能会出现 filesort，这是性能瓶颈。
• 工具使用：
  • EXPLAIN ANALYZE (MySQL 8.0.18+)：可以实际执行查询并显示每一步的真实耗时和行数，比 EXPLAIN 更精确。
  • pt-query-digest：对于分析慢查询日志，定位这类复杂查询的性能问题非常有帮助。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到百亿级别，你的 SQL 还能用吗？怎么优化？

   • 回答要点：
     • 不能直接用：直接在生产主库上运行此 SQL 会导致灾难。百亿级别的 ORDER BY 会消耗巨大的 CPU 和 IO 资源，并可能产生巨大的临时文件，严重影响数据库性能。
     • 架构级优化：必须将计算任务从 OLTP 数据库转移到 OLAP 系统。
     • 具体方案：将登录日志实时或准实时地同步到数据湖（如 HDFS）或数据仓库（如 Hive/ClickHouse）。使用 Spark 或 Flink 等分布式计算引擎，编写批处理或流处理任务来执行相同的窗口函数逻辑。计算结果存储在专门的结果表中，供上层应用查询。这实现了计算和存储的分离，保证了主库的稳定。

2. 追问：除了最长连续登录天数，如果要看“每个用户当前的连续登录天数”，该如何实现？

   • 回答要点：
     • 思路转变：这个问题关注的是“当下”的状态，而不是历史的最大值。
     • 实现：可以修改解法 1 的逻辑。首先，判断用户今天是否登录。如果今天没登录，当前连续天数为 0。如果今天登录了，就计算从今天开始往前追溯的连续天数。
     • SQL 方案：仍然可以使用“差值分组”法。计算出每个用户的每个连续区间的起止日期和长度。然后，对于每个用户，检查 CURDATE() 是否落在他最新的那个连续区间内。如果是，该区间的长度就是当前连续天数。这比求最大值要多一些判断逻辑。

3. 追问：如果定义“连续”为“登录间隔不超过1天”（即允许断一天）？如何求最长“活跃”天数？

   • 回答要点：
     • 问题变种：这是对“连续”定义的扩展，原先的 DATE_SUB(login_date, INTERVAL rn DAY) 技巧失效了，因为日期间隔不再是严格的 1。
     • 新思路：这需要一个新的分组标识。可以这样思考：当一个登录记录与上一条记录的间隔大于 2 天时，一个新的“活跃区间”开始了。
     • SQL 实现：
       1. 使用 LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) 获取上一条登录日期。
       2. 计算 DATEDIFF(login_date, lag_date)。
       3. 当 DATEDIFF > 2 或者 lag_date 为 NULL 时，标记为新活跃区间的开始（例如，设置一个标志位为 1，否则为 0）。
       4. 使用 SUM(flag) OVER (PARTITION BY user_id ORDER BY login_date) 这个累加和，来作为新的分组 ID。同一个活跃区间内的累加和是相同的。
       5. 后续步骤就和解法 1 类似了：按这个新的分组 ID 进行 GROUP BY，计算每个区间的长度（MAX(date) - MIN(date) + 1），最后取最大值。这展示了对窗口函数更深层次的组合运用能力。