本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
SQL 实战编程题（GitHub 高频题） · 查找类

找出连续两天都有登录、且第 2 天比第 1 天活跃时长多的用户。

Find users who logged in on two consecutive days and had a longer active duration on the second day

答案语言

考察要点

这道题面试官想看你是否掌握处理序列数据的核心能力。具体来说，考察两点：

  1. 现代 SQL 特性：是否熟练使用窗口函数（LAG / LEAD）来高效计算行与行之间的关系，这是衡量候选人 SQL 技能先进性的重要标志。
  2. 经典 SQL 思维：是否能用传统但通用的方法，如自连接（Self-Join），来解决同样的问题，并理解其与窗口函数的性能差异。

同时，这道题也附带考察了对日期函数的运用和索引优化意识。

核心概念

解决本题依赖于对以下概念的精准理解：

  1. 窗口函数 (Window Functions) 窗口函数对一组与当前行相关的表行（这个组称为窗口）执行计算。与聚合函数不同，它在返回计算结果的同时，不改变原始表的行数。

    • 核心函数 LAG(): LAG(expression, [offset], [default]) OVER (PARTITION BY ... ORDER BY ...)
      • expression: 要访问的列，如此处的 login_date 和 duration_minutes。
      • offset: 偏移量，默认为 1，即上一行。
      • default: 如果上一行不存在，返回的默认值。
      • PARTITION BY user_id: 将数据按用户分组，窗口函数在每个用户内部独立计算，不会跨用户。
      • ORDER BY login_date: 在每个用户分组内，按登录日期排序，确保 LAG() 获取的是前一天的数据。

    示例： 假设有登录表 user_logins：

    sql
     1CREATE TABLE user_logins (
     2    user_id INT,
     3    login_date DATE,
     4    duration_minutes INT,
     5    PRIMARY KEY (user_id, login_date)
     6);
     7INSERT INTO user_logins VALUES
     8(1, '2023-10-01', 60),
     9(1, '2023-10-02', 80),
    10(2, '2023-10-01', 120),
    11(2, '2023-10-02', 100);

    使用 LAG() 查询后，中间结果（CTE 中）会像这样：

    user_idlogin_dateduration_minutesprev_dateprev_duration
    12023-10-0160NULLNULL
    12023-10-02802023-10-0160
    22023-10-01120NULLNULL
    22023-10-021002023-10-01120

  2. 自连接 (Self-Join) 自连接是指将一张表与其自身进行连接。为了区分，必须为表使用两个不同的别名（如 t1 和 t2）。它适用于需要比较同一张表中不同行数据的场景。

    • 连接条件: ON t1.user_id = t2.user_id 保证是同一个用户。
    • 逻辑条件: ON ... AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY) 找到连续两天的记录。t1 代表第一天，t2 代表第二天。

  3. 日期函数

    • MySQL: DATE_ADD(date, INTERVAL expr unit) 或 DATE_SUB(...)。例如 DATE_ADD(t1.login_date, INTERVAL 1 DAY)。
    • PostgreSQL: t1.login_date + interval '1 day'。
    • DATEDIFF(date1, date2): 计算两个日期之间的天数差。DATEDIFF(t2.login_date, t1.login_date) = 1 也可以实现，但将函数应用于列可能影响索引效率，不如 t2.login_date = ... 的形式（SARGable）。

多种解法

解法 1：使用窗口函数 LAG()（推荐）

这种方法思路清晰，性能优越，是现代 SQL 的首选方案。

  • 版本: ✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 不支持窗口函数。
  • SQL:
sql
 1WITH LoginWithPrev AS (
 2    SELECT
 3        user_id,
 4        login_date,
 5        duration_minutes,
 6        LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login_date,
 7        LAG(duration_minutes, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_duration
 8    FROM
 9        user_logins
10)
11SELECT
12    user_id
13FROM
14    LoginWithPrev
15WHERE
16    -- 条件1: 保证是连续两天
17    prev_login_date IS NOT NULL AND DATEDIFF(login_date, prev_login_date) = 1
18    -- 条件2: 第2天活跃时长更长
19    AND duration_minutes > prev_duration;

注：DATEDIFF(login_date, prev_login_date) = 1 在这里是安全的，因为它作用于 CTE 的计算结果，不影响底层表的索引使用。也可以写成 login_date = DATE_ADD(prev_login_date, INTERVAL 1 DAY)。

  • 复杂度:
    • 时间复杂度: O(Nlog⁡N)O(N \log N)。主要是 PARTITION BY ... ORDER BY 产生的排序开销。如果数据已按 (user_id, login_date) 物理有序，则可降至 O(N)O(N)。
    • 空间复杂度: O(N)O(N)，需要存储 CTE 的中间结果。
  • 索引依赖: 强烈建议在 (user_id, login_date) 上建立复合索引。这能极大地加速 PARTITION BY 和 ORDER BY 操作，使其变为高效的索引扫描，而非全表扫描+文件排序。

解法 2：使用自连接 SELF JOIN

这是经典解法，兼容性好，在不支持窗口函数的老版本数据库中是唯一选择。

  • 版本: ✅ 所有主流数据库版本。
  • SQL:
sql
 1SELECT DISTINCT
 2    t2.user_id
 3FROM
 4    user_logins t1
 5JOIN
 6    user_logins t2 ON t1.user_id = t2.user_id
 7WHERE
 8    -- 条件1: 关联上连续的后一天
 9    t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
10    -- 条件2: 第2天活跃时长更长
11    AND t2.duration_minutes > t1.duration_minutes;

注：使用 DISTINCT 是为了防止 (user_id, login_date) 不唯一时产生重复的用户 ID。如果主键保证了其唯一性，则 DISTINCT 可省略。

  • 复杂度:
    • 时间复杂度: 理论上可达 O(N2)O(N^2)，但在有合适索引的情况下，通常为 O(Nlog⁡K)O(N \log K) 或类似，其中 K 是每个用户的平均登录天数。数据库的 Join 优化器会选择高效的算法（如 Index Nested Loop Join）。
    • 空间复杂度: O(M)O(M)，其中 M 是满足 Join 条件的中间结果集大小，通常远小于 N2N^2。
  • 索引依赖: 复合索引 (user_id, login_date) 至关重要。查询 t2 时，优化器可以利用该索引，通过 t1.user_id 和 DATE_ADD(t1.login_date, ...) 的值进行快速定位（ref 或 eq_ref 访问）。

常见低分回答

  1. 在 WHERE 子句中对列使用函数：

    sql
     1-- 反例
     2SELECT ... FROM user_logins t1 JOIN user_logins t2 ON t1.user_id = t2.user_id
     3WHERE DATE_ADD(t1.login_date, INTERVAL 1 DAY) = t2.login_date;

    这会导致 t2.login_date 上的索引可能失效，因为优化器需要对 t1 的每一行计算 DATE_ADD 的结果，再去 t2 中查找，而不是直接使用 t2 的索引。应将条件写成 t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)，将函数计算放在非索引侧。

  2. 窗口函数忘记 PARTITION BY：

    sql
     1-- 反例
     2LAG(login_date, 1) OVER (ORDER BY login_date) AS prev_login_date

    这会导致窗口函数在所有用户数据上进行计算，把用户 A 的最后一天和用户 B 的第一天错误地关联起来。

  3. 误用 DATEDIFF 作为连接条件：

    sql
     1-- 反例
     2FROM user_logins t1 JOIN user_logins t2 ON t1.user_id = t2.user_id AND DATEDIFF(t2.login_date, t1.login_date) = 1

    虽然逻辑正确，但 DATEDIFF 函数包裹了 t1 和 t2 的两列，使得优化器很难同时高效利用 (user_id, login_date) 索引进行连接。t2.login_date = DATE_ADD(...) 的形式是 SARGable（可作为搜索参数），对索引更友好。

  4. 使用相关子查询（Correlated Subquery）：

    sql
     1-- 性能较差的反例
     2SELECT u1.user_id FROM user_logins u1
     3WHERE u1.duration_minutes > (
     4    SELECT u2.duration_minutes FROM user_logins u2
     5    WHERE u2.user_id = u1.user_id AND u2.login_date = DATE_SUB(u1.login_date, INTERVAL 1 DAY)
     6)

    这种写法对于 u1 的每一行，都需要执行一次子查询，性能通常远低于 JOIN 和窗口函数。

工程实践与优化

  • 索引选择：PRIMARY KEY (user_id, login_date) 或 UNIQUE INDEX (user_id, login_date) 是最优解。它不仅保证了数据唯一性，还为 JOIN 和 PARTITION BY 提供了完美的覆盖索引，避免了回表和文件排序。
  • 执行计划分析：
    • 解法 1 (窗口函数)：使用 EXPLAIN 查看。在 MySQL 8.0+ 中，Extra 字段会显示 Using window function。关键是看它是否利用了 (user_id, login_date) 索引来避免 Using filesort。
    • 解法 2 (自连接)：EXPLAIN 的输出至关重要。对 t1 的访问应该是 type: index 或 ALL。对 t2 的访问类型应该是 type: ref 或 eq_ref，key 字段应显示 (user_id, login_date) 索引，ref 字段会显示 db.t1.user_id 和一个 func（代表 DATE_ADD 的结果）。这表明对 t2 的每次查找都是高效的索引查找。
  • 数据倾斜：如果少数超级用户的登录记录远超其他用户，PARTITION BY 可能会导致内存压力。在分布式环境（如 Spark, Flink）中，这可能导致单个任务节点成为瓶颈。此时需要考虑对这些大 user_id 进行特殊处理或增加资源。
  • 时间戳与时区：如果列是 DATETIME 或 TIMESTAMP 而非 DATE，问题会复杂化。DATE(login_timestamp) 会导致索引失效。正确的处理方式是使用范围查询：t2.login_timestamp >= DATE_ADD(DATE(t1.login_timestamp), INTERVAL 1 DAY) AND t2.login_timestamp < DATE_ADD(DATE(t1.login_timestamp), INTERVAL 2 DAY)。同时必须注意时区问题，所有计算最好在统一时区（如 UTC）下进行。

常见追问与延伸题目

  1. 追问：如果数据量增长 100 倍（达到数十亿级别），这条 SQL 性能怎样？怎么优化？

    • 回答要点 1 (瓶颈分析)：窗口函数解法通常优于自连接。但当单个分区（单个用户的数据）过大无法完全载入内存时，排序会溢出到磁盘，性能急剧下降。自连接的性能则更依赖于 Join 算法和缓存命中率。在数十亿级别，两种方案都可能超时或占用过多数据库资源。
    • 回答要点 2 (优化方案)：
      • 离线预计算：不应在 OLTP 库上实时运行此查询。应使用数据仓库（如 Hive, ClickHouse）或大数据平台（如 Spark, Flink）进行离线计算。每天运行一个批处理作业，将结果存入一个结果表中，供业务方查询。
      • 流式计算：如果需要近实时结果，可以使用 Flink 等流计算引擎。通过 keyedStream 按 user_id 分区，并使用状态（State）来存储用户上一次的登录信息，当新事件到来时，直接与状态中的数据比较，完成计算并输出结果。

  2. 追问：如果要找的是“连续 3 天登录，且活跃度逐日递增”的用户呢？

    • 回答要点 1 (窗口函数扩展)：LAG() 函数非常适合扩展。我们可以同时获取前一天（offset=1）和前两天（offset=2）的数据。
      sql
       1-- 伪代码示意
       2WITH LoginWithPrev2 AS (
       3    SELECT ...,
       4           LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS prev1_date,
       5           LAG(duration, 1) OVER (...) AS prev1_duration,
       6           LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS prev2_date,
       7           LAG(duration, 2) OVER (...) AS prev2_duration
       8    FROM ...
       9)
      10SELECT ... WHERE DATEDIFF(login_date, prev1_date) = 1
      11              AND DATEDIFF(prev1_date, prev2_date) = 1
      12              AND duration > prev1_duration
      13              AND prev1_duration > prev2_duration;
    • 回答要点 2 (自连接扩展)：需要进行两次自连接 t1 JOIN t2 JOIN t3，连接条件会变得更复杂，可读性和维护性下降，性能也可能更差。这凸显了窗口函数在处理复杂序列问题上的优越性。

  3. 追问：如果换成 ClickHouse 这种列式存储数据库，答案有什么不同？

    • 回答要点 1 (利用列存特性)：ClickHouse 极其擅长处理此类分析查询。虽然它也支持标准 SQL 的窗口函数和 JOIN，但更地道的解法是利用其特有的数组函数。
    • 回答要点 2 (数组函数解法)：可以先用 GROUP BY user_id 将每个用户的登录日期和活跃时长分别聚合为两个数组（groupArray(login_date) 和 groupArray(duration_minutes)），并确保数组是按日期排序的。然后使用 arrayDifference 或 arraySlice 等函数对数组进行操作。例如，计算日期数组的差分，找到差值为 1 的位置，再对比对应位置的时长数组，整个过程在内存中批量向量化执行，性能极高。这种思路充分利用了列存和向量化计算的优势。
← Previous
找出连续登录 7 天的用户。
Next →
找出某段时间内连续 N 次失败的支付订单。