找出连续登录 7 天的用户。

考察要点

这道题面试官想看你是否懂 窗口函数 的高级用法、日期处理 和 将连续性问题转化为分组问题的逻辑思维能力。核心是考察你如何巧妙地构造一个分组标识，来识别出连续的日期区间，这是衡量 SQL 熟练度的重要标志。

核心概念

解决本题的核心是利用 “差值分组” 技巧。即通过一个单调递增的序列（如日期）减去另一个同步单调递增的序列（如行号），得到的差值在连续区间内是恒定的。

假设我们有以下用户登录数据（已对 user_id 和 login_date 去重）：

1. 生成行号: 使用窗口函数 ROW_NUMBER() 或 DENSE_RANK() 为每个用户的登录记录生成一个连续的序号。DENSE_RANK() 在处理一天多次登录（去重后）的场景下更稳健。

   sql

   ⧉⛶

    1-- 使用 DENSE_RANK() 为每个用户的登录日期排序
    2DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date) AS rnk

2. 计算差值生成分组键: 用 login_date 减去上一步生成的行号 rnk（天数）。如果日期是连续的，这个差值将是一个常量。

   user_id	login_date	rnk	DATE_SUB(login_date, INTERVAL rnk DAY)	说明
   101	2023-01-01	1	2022-12-31	连续区间的开始
   101	2023-01-02	2	2022-12-31	日期+1, rnk+1, 差值不变
   101	2023-01-03	3	2022-12-31	日期+1, rnk+1, 差值不变
   101	2023-01-05	4	2023-01-01	间隔断开，产生新的差值，新的分组
   101	2023-01-06	5	2023-01-01	日期+1, rnk+1, 差值不变

   可以看到，2023-01-01 到 2023-01-03 这个连续区间，它们的差值都是 2022-12-31。这个差值就可以作为分组的键（group_key）。

3. 分组计数: 按 user_id 和这个 group_key 进行分组，然后计数。如果某个组的计数大于等于 7，就说明该用户存在一个长度至少为 7 的连续登录区间。

多种解法

假设我们有一张登录日志表 login_log：

 1CREATE TABLE login_log (
 2    id INT PRIMARY KEY AUTO_INCREMENT,
 3    user_id INT,
 4    login_date DATE
 5);
 6-- 插入一些样本数据

解法 1：窗口函数法（推荐，最优解）

• 思路：利用 DENSE_RANK() 和日期函数 DATE_SUB() 实现“差值分组”，然后通过 GROUP BY 和 HAVING 筛选出符合条件的连续天数。
• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 不支持窗口函数。
• SQL 代码：
  sql

  ⧉⛶

   1WITH user_login_streaks AS (
   2    SELECT
   3        user_id,
   4        login_date,
   5        -- 计算日期与排名之间的差值，作为连续登录的分组标识
   6        DATE_SUB(login_date, INTERVAL DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS group_key
   7    FROM
   8        -- 首先对用户和日期去重，防止一天多次登录影响计算
   9        (SELECT DISTINCT user_id, login_date FROM login_log) AS distinct_logins
  10)
  11SELECT
  12    user_id
  13FROM
  14    user_login_streaks
  15GROUP BY
  16    user_id, group_key
  17HAVING
  18    -- 统计每个连续登录区间的天数
  19    COUNT(*) >= 7;

• 时间/空间复杂度：时间复杂度为 $O(N \log N)$，主要开销在于窗口函数中的排序 (ORDER BY)。空间复杂度为 $O(N)$，因为 CTE 需要物化中间结果。
• 索引依赖：在 login_log(user_id, login_date) 上建立复合索引，可以极大地加速 PARTITION BY 和 ORDER BY 操作，甚至可能利用索引顺序避免显式排序，实现 Using index for window function 的优化。

解法 2：自连接法（兼容老版本）

• 思路：通过将表与自身进行 6 次 JOIN 来查找连续 7 天的记录。t1 代表第一天，t2 代表第二天，以此类推，直到 t7 代表第七天。
• 版本：✅ 所有 SQL 版本通用。
• SQL 代码：
  sql

  ⧉⛶

   1SELECT DISTINCT
   2    t1.user_id
   3FROM
   4    (SELECT DISTINCT user_id, login_date FROM login_log) AS t1
   5JOIN
   6    (SELECT DISTINCT user_id, login_date FROM login_log) AS t2
   7    ON t1.user_id = t2.user_id AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
   8JOIN
   9    (SELECT DISTINCT user_id, login_date FROM login_log) AS t3
  10    ON t1.user_id = t3.user_id AND t3.login_date = DATE_ADD(t1.login_date, INTERVAL 2 DAY)
  11JOIN
  12    (SELECT DISTINCT user_id, login_date FROM login_log) AS t4
  13    ON t1.user_id = t4.user_id AND t4.login_date = DATE_ADD(t1.login_date, INTERVAL 3 DAY)
  14JOIN
  15    (SELECT DISTINCT user_id, login_date FROM login_log) AS t5
  16    ON t1.user_id = t5.user_id AND t5.login_date = DATE_ADD(t1.login_date, INTERVAL 4 DAY)
  17JOIN
  18    (SELECT DISTINCT user_id, login_date FROM login_log) AS t6
  19    ON t1.user_id = t6.user_id AND t6.login_date = DATE_ADD(t1.login_date, INTERVAL 5 DAY)
  20JOIN
  21    (SELECT DISTINCT user_id, login_date FROM login_log) AS t7
  22    ON t1.user_id = t7.user_id AND t7.login_date = DATE_ADD(t1.login_date, INTERVAL 6 DAY);

• 时间/空间复杂度：性能极差。如果 user_id 和 login_date 上没有合适的索引，复杂度可能接近 $O(N^7)$。即使有 (user_id, login_date) 索引，多次连接也会产生巨大的中间结果集，导致性能瓶颈。
• 索引依赖：必须在 login_log(user_id, login_date) 上建立复合索引，否则此查询无法在生产环境运行。

常见低分回答

1. 忘记对登录日期去重：如果一个用户在一天内登录多次，ROW_NUMBER() 会为每次登录生成不同序号，导致“差值分组”逻辑失效。低分回答会直接在原表上使用窗口函数，而不是在 SELECT DISTINCT user_id, login_date 的子查询上。
2. 错误使用 COUNT(*) 或 COUNT(login_date)：在最后的分组计数阶段，如果前面的步骤没有完美去重，使用 COUNT(*) 可能会导致计数错误。正确的做法是 COUNT(DISTINCT login_date) 或确保上游数据源已去重后使用 COUNT(*)。
3. 在 WHERE 子句中使用窗口函数：窗口函数只能在 SELECT 或 ORDER BY 子句中使用，不能直接用于 WHERE 或 GROUP BY。
   sql

   ⧉⛶

    1-- 错误示例
    2SELECT user_id, login_date
    3FROM login_log
    4WHERE DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date) > 1; -- 这是非法的

4. 混淆 ROW_NUMBER(), RANK(), DENSE_RANK()：
   • ROW_NUMBER(): 无论值是否相同，都给出唯一排名。
   • RANK(): 值相同时排名相同，但后续排名会跳跃。
   • DENSE_RANK(): 值相同时排名相同，后续排名连续。 在本题中，DENSE_RANK() 是最合适的，因为它能正确处理一天多次登录（去重后）的情况，并保证序号是连续的。

工程实践与优化

• 索引是性能的关键：对于解法 1，login_log(user_id, login_date) 上的复合索引是必须的。MySQL 8.0.2+ 对窗口函数有专门优化，如果 PARTITION BY 和 ORDER BY 的列与索引前缀匹配，可以避免全表扫描和文件排序，执行计划的 Extra 字段会显示 Using index for window function。
• 数据预处理/物化视图：在真实的数据仓库或大数据场景中，用户行为日志量巨大。直接在原始日志上跑这种查询是不现实的。通常会有一个前置的 ETL/ELT 任务，每天计算一次用户的日活状态，并存入一张更小的日活表 user_daily_active(user_id, active_date)，并以 (user_id, active_date) 作为主键。所有后续的连续性分析都基于这张预处理过的表，查询会变得极其高效。
• 执行计划分析：
  • 对于解法 1，EXPLAIN 的关键是看是否能利用索引进行排序。Extra 中出现 Using temporary; Using filesort 是性能红灯，说明需要优化索引。
  • 对于解法 2，EXPLAIN 会显示多层嵌套的 JOIN，需要确保每次 JOIN 的 type 都是 ref 或 eq_ref，并且 key 列显示 (user_id, login_date) 索引。即便如此，rows 列的估算值相乘后会非常大，表明其低效性。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，这条 SQL 性能怎样？怎么优化？

   • 回答要点 1 (性能评估)：窗口函数解法（解法 1）的性能大致呈 $O(N \log N)$ 增长，性能下降是可接受的。而自连接解法（解法 2）的性能会急剧恶化，甚至无法在规定时间内跑出结果，因为其计算复杂度接近指数级增长。
   • 回答要点 2 (优化方案)：
     • 垂直优化：确保 (user_id, login_date) 复合索引存在且最优。
     • 架构优化：引入数据预处理，如上文提到的，每天定时任务生成一张 user_daily_active 表，将问题从对海量原始日志的即时查询，转变为对中等规模结果表的查询。
     • 水平扩展：当单机 MySQL 无法满足需求时，将数据迁移到分布式数据库（如 TiDB, OceanBase）或大数据平台（如 Hive, Spark SQL, ClickHouse）。这些平台内置了高效的分布式窗口函数实现，可以通过增加计算节点来水平扩展处理能力。

2. 追问：如果要找出每个用户的最长连续登录天数，该怎么写？

   • 回答要点：这个问题是原问题的自然延伸。在解法 1 的 CTE 基础上再做一次聚合即可。
     sql

     ⧉⛶

      1WITH user_login_streaks AS (
      2    SELECT
      3        user_id,
      4        DATE_SUB(login_date, INTERVAL DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS group_key
      5    FROM (SELECT DISTINCT user_id, login_date FROM login_log) AS distinct_logins
      6),
      7streaks_length AS (
      8    SELECT
      9        user_id,
     10        group_key,
     11        COUNT(*) AS length
     12    FROM user_login_streaks
     13    GROUP BY user_id, group_key
     14)
     15SELECT
     16    user_id,
     17    MAX(length) AS max_consecutive_days
     18FROM
     19    streaks_length
     20GROUP BY
     21    user_id;

3. 追问：如果 MySQL 版本是 5.7，不支持窗口函数，除了自连接还有其他方案吗？

   • 回答要点：可以提一下 用户变量（User Variables） 的方法。这是一种在 MySQL 8.0 之前模拟窗口函数行为的技巧，通过在查询过程中设置和更新变量来跟踪状态。
     sql

     ⧉⛶

      1-- 注意：用户变量在 MySQL 8.0+ 中行为不可靠，不推荐使用
      2SELECT user_id, MAX(streak) AS max_streak
      3FROM (
      4    SELECT
      5        user_id,
      6        login_date,
      7        @streak := IF(@prev_user = user_id AND DATEDIFF(login_date, @prev_date) = 1, @streak + 1, 1) AS streak,
      8        @prev_user := user_id,
      9        @prev_date := login_date
     10    FROM
     11        (SELECT DISTINCT user_id, login_date FROM login_log ORDER BY user_id, login_date) AS sorted_log,
     12        (SELECT @streak := 0, @prev_user := NULL, @prev_date := NULL) AS vars
     13) AS streaks
     14WHERE streak >= 7
     15GROUP BY user_id;

   • 强调这种方法的局限性：语法晦涩、非标准 SQL、在 MySQL 8.0+ 中因优化器策略改变而结果可能不准确，应优先考虑升级数据库或使用窗口函数。