进阶查询（窗口函数 / CTE / 递归 / 子查询） · 窗口函数（Window Functions）

如何用窗口函数实现连续登录 N 天的用户检测？(row_number 与日期相减)

How to find users with N consecutive login days using window functions? (by subtracting date from row_number

答案语言

考察要点

这道题面试官想看你是否精通 SQL 窗口函数，并能创造性地解决复杂业务问题。它重点考察两个方面：

窗口函数的熟练应用：是否理解 ROW_NUMBER()、PARTITION BY 和 ORDER BY 的组合用法。
分析性思维：能否想到通过“日期序列”与“整数序列”相减，构造出一个常量来标识“连续”这个状态，这是解决此类“连续性问题”的经典技巧。

核心概念

本题的核心是利用窗口函数 ROW_NUMBER() 生成一个自增序列，再与日期进行运算，从而将连续的日期“分组”到一起。

窗口函数 (Window Function) 窗口函数对与当前行相关的行集（即“窗口”）进行计算。与 GROUP BY 聚合函数不同，它不会将多行压缩为一行，而是在保留原始行数的基础上，为每一行计算出一个附加值。基本语法：FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ...)
ROW_NUMBER() 为窗口分区中的每一行分配一个从 1 开始的唯一连续整数。
- PARTITION BY user_id：将数据按用户ID分成多个独立的“窗口”或“分区”。ROW_NUMBER() 会在每个用户的分区内独立计算。
- ORDER BY login_date：在每个分区内，按登录日期对行进行排序，ROW_NUMBER() 据此分配序号。
日期相减分组技巧 (The Core Trick) 这是本题的精髓。对于一个连续的日期序列，日期本身在递增，我们用 ROW_NUMBER() 生成的序号也在以相同的步长（1）递增。因此，用日期减去这个序号，会得到一个恒定的值。当中断发生时，这个恒定值会改变。

数据示例： 假设用户 101 的登录记录如下（已去重并按日期排序）：

user_id	login_date	`ROW_NUMBER()` (rnum)	`login_date - INTERVAL rnum DAY` (grouping_key)
101	2023-01-01	1	`2022-12-31`
101	2023-01-02	2	`2022-12-31`
101	2023-01-03	3	`2022-12-31`
101	2023-01-05	4	`2023-01-01`
101	2023-01-06	5	`2023-01-01`

text

 1观察 `grouping_key` 列：
 2- 前三条记录（1/1, 1/2, 1/3）是连续的，它们计算出的 `grouping_key` 都是 `2022-12-31`。
 3- 登录中断后，第四条记录（1/5）的 `rnum` 是 4，`2023-01-05 - 4天` 得到一个新的 `grouping_key` `2023-01-01`。
 4- 后两条记录（1/5, 1/6）是连续的，它们的 `grouping_key` 相同。
 5
 6这个 `grouping_key` 成为了我们识别每一个“连续登录周期”的完美标识。我们只需按 `user_id` 和 `grouping_key` 分组计数，即可得到每个连续周期的天数。

多种解法

假设我们有登录日志表 login_logs：

sql

 1CREATE TABLE login_logs (
 2    user_id INT,
 3    login_time DATETIME
 4);
 5-- 插入一些数据
 6INSERT INTO login_logs VALUES
 7(101, '2023-01-01 08:00:00'), (101, '2023-01-01 10:00:00'),
 8(101, '2023-01-02 09:00:00'), (101, '2023-01-03 11:00:00'),
 9(101, '2023-01-05 08:30:00'), (101, '2023-01-06 10:00:00'),
10(102, '2023-02-10 09:00:00'), (102, '2023-02-11 09:00:00'),
11(102, '2023-02-12 09:00:00'), (102, '2023-02-13 09:00:00');

我们的目标是找出连续登录 3 天及以上 的用户。

解法 1：窗口函数 `ROW_NUMBER()` + `CTE`（主流高分方案）

这是最优雅、最高效、扩展性最好的解法。

版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server / Oracle；❌ MySQL 5.7 不支持窗口函数。
SQL 代码：

sql

 1WITH
 2-- Step 1: 对每日登录记录去重，得到每个用户每天唯一的登录日期
 3unique_logins AS (
 4    SELECT DISTINCT
 5        user_id,
 6        DATE(login_time) AS login_date
 7    FROM login_logs
 8),
 9-- Step 2: 使用 ROW_NUMBER() 和日期相减技巧，创建分组键
10ranked_logins AS (
11    SELECT
12        user_id,
13        login_date,
14        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grouping_key
15    FROM unique_logins
16)
17-- Step 3: 按分组键聚合，计算每个连续周期的长度，并筛选出长度 >= 3 的
18SELECT
19    user_id,
20    MIN(login_date) AS streak_start,
21    MAX(login_date) AS streak_end,
22    COUNT(*) AS consecutive_days
23FROM ranked_logins
24GROUP BY user_id, grouping_key
25HAVING COUNT(*) >= 3;

时间/空间复杂度：时间复杂度主要由 ROW_NUMBER() 中的排序决定，为 $O(N \log N)$ ，其中 N 是去重后的记录数。空间复杂度为 $O(N)$ ，用于存储 CTE 的中间结果。
索引依赖：在 login_logs(user_id, login_time) 上建立复合索引是至关重要的。该索引可以被 PARTITION BY user_id 和 ORDER BY login_date 高效利用，避免全表扫描和额外的 filesort 操作。

解法 2：递归 `CTE`（Recursive Common Table Expression）

这种方法模拟了逐天“延伸”连续记录的过程，更能体现逻辑递推关系。

版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server；❌ MySQL 5.7 不支持 CTE。
SQL 代码：

sql

 1WITH RECURSIVE
 2-- 准备去重后的每日登录数据
 3unique_logins AS (
 4    SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM login_logs
 5),
 6-- 递归部分
 7streaks AS (
 8    -- Anchor Member: 递归的起点，即每个用户的每次登录，作为长度为1的连续序列的开始
 9    SELECT
10        user_id,
11        login_date,
12        login_date AS streak_start,
13        1 AS streak_length
14    FROM unique_logins
15
16    UNION ALL
17
18    -- Recursive Member: 尝试将下一天的登录记录连接到现有序列上
19    SELECT
20        s.user_id,
21        u.login_date,
22        s.streak_start,
23        s.streak_length + 1
24    FROM streaks s
25    JOIN unique_logins u
26        ON s.user_id = u.user_id AND u.login_date = DATE_ADD(s.login_date, INTERVAL 1 DAY)
27)
28-- 从所有生成的序列中，找出每个用户、每个起始点所能达到的最大长度，并筛选
29SELECT
30    user_id,
31    streak_start,
32    MAX(login_date) AS streak_end,
33    MAX(streak_length) AS consecutive_days
34FROM streaks
35GROUP BY user_id, streak_start
36HAVING MAX(streak_length) >= 3;

时间/空间复杂度：递归查询的性能依赖于数据库的实现和数据分布，通常比窗口函数方案要差，因为它可能产生大量中间行。
索引依赖：同样，unique_logins 的 (user_id, login_date) 索引对于 JOIN 操作的性能至关重要。

何时使用：解法 1 是首选。解法 2 作为备选方案，可以展示你对递归查询的掌握，但在性能上通常不占优势。

常见低分回答

忘记对每日登录去重：直接在 login_logs 上使用 ROW_NUMBER()。如果一个用户一天登录多次，ROW_NUMBER() 会错误地递增，导致 grouping_key 计算错误，无法识别连续天数。
使用 LAG()/LEAD() 但逻辑不完整：LAG(login_date) 只能检查前一天是否存在，可以用来识别连续的“对”，但要统计长度为 N 的序列，需要复杂的、类似状态机的逻辑，很容易出错。

使用相关子查询进行暴力计数：对于每一行，都用一个子查询去数它之后连续的天数。这种方法性能极差，时间复杂度接近

O(N^2)

。

sql

 1-- 性能极差的反例
 2SELECT DISTINCT l1.user_id
 3FROM login_logs l1
 4WHERE (
 5    SELECT COUNT(DISTINCT DATE(l2.login_time))
 6    FROM login_logs l2
 7    WHERE l2.user_id = l1.user_id
 8      AND DATE(l2.login_time) BETWEEN DATE(l1.login_time) AND DATE_ADD(DATE(l1.login_time), INTERVAL 2 DAY)
 9) = 3;

混淆 COUNT(*) 和 COUNT(column)：在 HAVING 子句中使用 COUNT(login_date)，虽然在这里结果一样，但在处理 NULL 值时会有差异，COUNT(*) 是更通用的行计数方式。
在 WHERE 子句中使用窗口函数：窗口函数在 WHERE 子句之后才执行，不能直接用于 WHERE 过滤。必须像解法1那样，在子查询或 CTE 中计算完毕后，再在外层查询中进行过滤。

工程实践与优化

索引是性能的生命线：对于 login_logs 表，必须建立 (user_id, login_time) 的复合索引。EXPLAIN 的结果中，key 字段应显示此索引，Extra 字段应尽量避免 Using filesort。
预聚合 (Pre-aggregation)：当 login_logs 表达到十亿、百亿级别时，实时查询会非常慢。最佳实践是创建一个每日的用户活跃表 user_daily_active (user_id, active_date)。通过离线任务（如 Spark/Flink）或数据库定时任务，每天凌晨将前一天的活跃用户（DISTINCT user_id, DATE(login_time)）写入此表。这样，我们的分析查询就可以基于这个规模小得多的聚合表进行，速度提升几个数量级。
执行计划分析：使用 EXPLAIN (或 EXPLAIN ANALYZE in MySQL 8.0+/PostgreSQL) 来分析查询计划。
- type: 理想情况下是 range 或 index。
- key: 确认是否用上了我们创建的 (user_id, login_time) 索引。
- rows: 估算的扫描行数，越小越好。
- Extra: Using index 表示覆盖索引，性能最佳。Using temporary; Using filesort 表示需要创建临时表并进行外部排序，这是窗口函数在索引不佳时的常见性能瓶颈。
避免大事务：如果此查询是某个更新流程的一部分，确保它不会锁住 login_logs 表太久，影响线上业务的写入。通常这类分析查询会走只读的从库。

常见追问与延伸题目

追问：如果数据量增长 100 倍，达到百亿级别，这条 SQL 性能怎样？怎么优化？
- 回答要点 1 (瓶颈分析)：直接在原始表上执行，性能会急剧下降。主要瓶颈在于 ROW_NUMBER() 对海量数据进行分区排序，会导致巨大的 I/O 开销和内存压力，甚至可能因为排序数据量超过 sort_buffer_size 而使用磁盘临时文件（filesort），性能会非常差。
- 回答要点 2 (架构级优化)：
  - 预聚合：这是工业界的标准答案。如上所述，建立一个 user_daily_active 表，将查询负载从百亿级的日志表转移到亿级的日活表上，查询性能将得到质的飞跃。
  - 分布式计算：使用 Spark SQL、Hive 或 Flink 等大数据处理引擎。这些引擎天生为大规模数据并行处理而设计，可以将数据按 user_id 分发到不同节点上并行计算窗口函数，最后再汇总结果。SQL 逻辑基本不变。

追问：如何找出每个用户的“最长”连续登录天数？

回答要点：在解法 1 的基础上稍作修改即可。我们已经计算出了每个用户的所有连续周期及其长度。只需在最外层按 user_id 分组，然后取 consecutive_days 的最大值。

sql

 1-- 沿用解法1中的 unique_logins 和 ranked_logins CTE
 2WITH ...
 3streaks AS (
 4    SELECT
 5        user_id,
 6        COUNT(*) AS consecutive_days
 7    FROM ranked_logins
 8    GROUP BY user_id, grouping_key
 9)
10SELECT
11    user_id,
12    MAX(consecutive_days) AS max_consecutive_days
13FROM streaks
14GROUP BY user_id;

追问：如果换成 PostgreSQL / TiDB，答案有什么不同？
- PostgreSQL：SQL 语法完全兼容，甚至可以说 PostgreSQL 的窗口函数实现和优化器比 MySQL 更成熟。它同样支持递归 CTE。此外，PostgreSQL 支持物化视图 (Materialized View)，可以将预聚合的日活表创建为物化视图，自动或手动刷新，简化数据维护。
- TiDB / OceanBase (分布式数据库)：SQL 语法同样兼容（它们兼容 MySQL 协议）。核心区别在于执行层。TiDB 会将 PARTITION BY user_id 这个操作进行分布式计算，将不同用户的数据发送到不同的 TiKV 节点上并行执行排序和计算，最后由 TiDB Server 聚合结果。对于海量数据，其性能和扩展性远超单机 MySQL。开发者写的 SQL 几乎一样，但底层架构的并行处理能力带来了巨大的性能优势。