作为一个准备校招的同学，如果你还不会解决连续登录问题，那真的要抓紧了！这个问题在各大厂的面试中出现频率超高，特别是字节、阿里、腾讯这些互联网公司，几乎是必考题。

今天就来彻底搞懂这个经典问题，保证看完就能手撕代码，面试官问到直接秒杀！

为什么连续登录问题这么重要？

想象一下这个场景：你刚入职实习，产品经理找到你说："小李啊，帮我分析一下用户粘性，看看连续登录超过7天的用户有多少，他们的行为特征是什么？"

如果你不会，那就只能干瞪眼了。但掌握了今天的技巧，几分钟就能搞定！

连续登录问题其实是**"间隔与岛屿"问题**的经典应用，在数据分析中超级常见：

• 用户连续活跃天数分析
• 服务器连续运行时间统计
• 股票连续涨跌分析
• 学生连续签到统计

核心算法：日期减去排名的神奇公式

先看数据长什么样

sql

-- 用户登录记录表
CREATE TABLE user_logins (
    user_id INT,
    login_date DATE
);

-- 示例数据
INSERT INTO user_logins VALUES
(1001, '2024-01-01'),
(1001, '2024-01-02'),
(1001, '2024-01-03'),
(1001, '2024-01-05'), -- 注意这里中断了
(1001, '2024-01-06'),
(1001, '2024-01-07'),
(1002, '2024-01-01'),
(1002, '2024-01-03'),
(1002, '2024-01-04');

思路分析：为什么日期减排名能工作？

关键洞察：如果用户连续登录，那么登录日期 - 排名序号会得到相同的值！

让我们一步步来看：

sql

-- 第一步：给每个用户的登录记录排序
SELECT 
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as 排名
FROM user_logins
ORDER BY user_id, login_date;

结果：

user_id | login_date | 排名
--------|------------|------
1001    | 2024-01-01 | 1
1001    | 2024-01-02 | 2
1001    | 2024-01-03 | 3
1001    | 2024-01-05 | 4  ← 注意这里日期跳了，但排名连续
1001    | 2024-01-06 | 5
1001    | 2024-01-07 | 6

sql

-- 第二步：计算日期减去排名（魔法时刻！）
SELECT 
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as 排名,
    DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as 分组标识
FROM user_logins
ORDER BY user_id, login_date;

结果：

user_id | login_date | 排名 | 分组标识
--------|------------|------|----------
1001    | 2024-01-01 | 1    | 2023-12-31  ← 连续组1
1001    | 2024-01-02 | 2    | 2023-12-31  ← 连续组1  
1001    | 2024-01-03 | 3    | 2023-12-31  ← 连续组1
1001    | 2024-01-05 | 4    | 2024-01-01  ← 连续组2（分组标识变了！）
1001    | 2024-01-06 | 5    | 2024-01-01  ← 连续组2
1001    | 2024-01-07 | 6    | 2024-01-01  ← 连续组2

看懂了吗？ 相同的分组标识 = 连续登录的一段时间！

完整解决方案

sql

-- 查找所有连续登录记录
WITH login_groups AS (
    SELECT 
        user_id,
        login_date,
        -- 核心公式：日期减去排名
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_id
    FROM user_logins
)
SELECT 
    user_id as 用户ID,
    MIN(login_date) as 连续开始日期,
    MAX(login_date) as 连续结束日期,
    COUNT(*) as 连续天数,
    DATEDIFF(MAX(login_date), MIN(login_date)) + 1 as 跨度天数
FROM login_groups
GROUP BY user_id, group_id
HAVING COUNT(*) >= 3  -- 只看连续3天以上的
ORDER BY 用户ID, 连续开始日期;

进阶技巧：处理各种复杂情况

1. 处理一天多次登录

现实中用户可能一天登录好几次，我们只算一次：

sql

-- 先去重，再分析连续登录
WITH daily_unique_logins AS (
    -- 每个用户每天只保留一条记录
    SELECT DISTINCT 
        user_id, 
        DATE(login_timestamp) as login_date
    FROM user_login_logs
),
login_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_id
    FROM daily_unique_logins
)
SELECT 
    user_id,
    MIN(login_date) as 开始日期,
    MAX(login_date) as 结束日期,
    COUNT(*) as 连续天数
FROM login_groups
GROUP BY user_id, group_id
HAVING COUNT(*) >= 7  -- 连续7天登录
ORDER BY 连续天数 DESC;

2. 找出每个用户的最长连续登录

sql

-- 找出每个用户历史上最长的连续登录记录
WITH login_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_id
    FROM (SELECT DISTINCT user_id, DATE(login_time) as login_date FROM user_logins) t
),
user_streaks AS (
    SELECT 
        user_id,
        group_id,
        MIN(login_date) as streak_start,
        MAX(login_date) as streak_end,
        COUNT(*) as streak_days,
        -- 给每个用户的连续登录段排名
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) as streak_rank
    FROM login_groups
    GROUP BY user_id, group_id
)
SELECT 
    user_id as 用户ID,
    streak_start as 最长连续开始日期,
    streak_end as 最长连续结束日期,
    streak_days as 最长连续天数
FROM user_streaks
WHERE streak_rank = 1  -- 每个用户最长的那段
ORDER BY 最长连续天数 DESC;

3. 分析用户活跃度等级

sql

-- 根据连续登录天数给用户分等级
WITH login_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_id
    FROM (SELECT DISTINCT user_id, DATE(login_time) as login_date FROM user_logins) t
),
user_activity AS (
    SELECT 
        user_id,
        MAX(COUNT(*)) OVER (PARTITION BY user_id) as 最长连续天数,
        COUNT(DISTINCT group_id) as 连续登录段数,
        COUNT(*) as 总登录天数
    FROM login_groups
    GROUP BY user_id, group_id
)
SELECT 
    user_id,
    最长连续天数,
    连续登录段数,
    总登录天数,
    CASE 
        WHEN 最长连续天数 >= 30 THEN '超级活跃用户'
        WHEN 最长连续天数 >= 14 THEN '高活跃用户'
        WHEN 最长连续天数 >= 7 THEN '中等活跃用户'
        WHEN 最长连续天数 >= 3 THEN '低活跃用户'
        ELSE '偶尔登录用户'
    END as 用户等级
FROM (
    SELECT user_id, 
           MAX(最长连续天数) as 最长连续天数,
           MAX(连续登录段数) as 连续登录段数,
           MAX(总登录天数) as 总登录天数
    FROM user_activity 
    GROUP BY user_id
) final_stats
ORDER BY 最长连续天数 DESC;

其他解法：LAG/LEAD函数方法

除了"日期减排名"，还有另一种思路：

sql

-- 用LAG函数判断相邻日期的间隔
WITH date_gaps AS (
    SELECT 
        user_id,
        login_date,
        LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) as prev_date,
        CASE 
            WHEN DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) = 1 
            THEN 0  -- 连续
            ELSE 1  -- 不连续
        END as is_new_streak
    FROM (SELECT DISTINCT user_id, DATE(login_time) as login_date FROM user_logins) t
),
streak_groups AS (
    SELECT 
        user_id,
        login_date,
        SUM(is_new_streak) OVER (PARTITION BY user_id ORDER BY login_date) as streak_group
    FROM date_gaps
)
SELECT 
    user_id,
    MIN(login_date) as 开始日期,
    MAX(login_date) as 结束日期,
    COUNT(*) as 连续天数
FROM streak_groups
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY user_id, 开始日期;

对比两种方法：

• 日期减排名：思路更巧妙，代码更简洁
• LAG函数：逻辑更直观，容易理解

性能优化建议

1. 建立合适的索引

sql

-- 为连续登录查询优化的索引
CREATE INDEX idx_user_login_date ON user_logins(user_id, login_date);

-- 如果经常按时间范围查询，可以考虑复合索引
CREATE INDEX idx_login_date_user ON user_logins(login_date, user_id);

2. 数据量大时的处理策略

sql

-- 大表查询：先按时间过滤，再计算连续登录
WITH recent_logins AS (
    SELECT user_id, login_date
    FROM user_logins
    WHERE login_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)  -- 只看最近90天
),
login_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_id
    FROM recent_logins
)
SELECT 
    user_id,
    COUNT(*) as 最近连续登录天数
FROM login_groups
GROUP BY user_id, group_id
ORDER BY 最近连续登录天数 DESC
LIMIT 100;  -- 只看TOP 100

实际应用场景

1. 用户留存分析

sql

-- 分析不同连续登录天数的用户占比
WITH user_max_streaks AS (
    -- 计算每个用户的最长连续登录天数
    SELECT 
        user_id,
        MAX(COUNT(*)) OVER (PARTITION BY user_id) as max_streak
    FROM (
        SELECT 
            user_id,
            DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_id
        FROM user_logins
    ) t
    GROUP BY user_id, group_id
)
SELECT 
    CASE 
        WHEN max_streak >= 30 THEN '30天以上'
        WHEN max_streak >= 14 THEN '14-29天'
        WHEN max_streak >= 7 THEN '7-13天'
        WHEN max_streak >= 3 THEN '3-6天'
        ELSE '1-2天'
    END as 连续登录区间,
    COUNT(*) as 用户数量,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as 占比百分比
FROM (SELECT user_id, MAX(max_streak) as max_streak FROM user_max_streaks GROUP BY user_id) t
GROUP BY 
    CASE 
        WHEN max_streak >= 30 THEN '30天以上'
        WHEN max_streak >= 14 THEN '14-29天'
        WHEN max_streak >= 7 THEN '7-13天'
        WHEN max_streak >= 3 THEN '3-6天'
        ELSE '1-2天'
    END
ORDER BY MIN(max_streak) DESC;

2. 活动效果分析

sql

-- 分析某个活动期间用户的连续登录情况
WITH activity_period AS (
    SELECT user_id, login_date
    FROM user_logins
    WHERE login_date BETWEEN '2024-01-01' AND '2024-01-31'  -- 活动期间
),
streak_analysis AS (
    SELECT 
        user_id,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_id
    FROM activity_period
)
SELECT 
    '活动期间连续登录分析' as 分析类型,
    COUNT(DISTINCT user_id) as 参与用户数,
    AVG(consecutive_days) as 平均连续天数,
    MAX(consecutive_days) as 最长连续天数,
    COUNT(CASE WHEN consecutive_days >= 7 THEN 1 END) as 连续7天以上用户数
FROM (
    SELECT user_id, group_id, COUNT(*) as consecutive_days
    FROM streak_analysis
    GROUP BY user_id, group_id
) streak_stats;

不同数据库的差异

MySQL (8.0+)

sql

-- MySQL语法，使用DATE_SUB
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (...) DAY)

PostgreSQL

sql

-- PostgreSQL语法，使用减法
login_date - ROW_NUMBER() OVER (...) * INTERVAL '1 day'

SQL Server

sql

-- SQL Server语法，使用DATEADD
DATEADD(day, -ROW_NUMBER() OVER (...), login_date)

Oracle

sql

-- Oracle语法
login_date - ROW_NUMBER() OVER (...)

面试常见问题和回答技巧

Q1: 为什么日期减去排名能识别连续登录？ A: 因为对于连续的日期序列，每个日期与它在序列中的位置（排名）之间的差值是固定的。一旦日期不连续，这个差值就会发生变化，从而可以识别出不同的连续段。

Q2: 如果用户一天登录多次怎么处理？ A: 首先要对数据去重，确保每个用户每天只有一条记录，然后再进行连续登录分析。可以使用DISTINCT或GROUP BY来实现。

Q3: 这个方法的时间复杂度是多少？ A: 主要是窗口函数的复杂度，通常是O(n log n)，其中n是登录记录的数量。如果有合适的索引，性能会更好。

Q4: 除了ROW_NUMBER，还能用其他窗口函数吗？ A: 可以用DENSE_RANK()，特别是当需要处理同一天多次登录的情况时。但不建议用RANK()，因为它在处理并列时会跳过序号。

总结

连续登录问题看起来复杂，但掌握了"日期减排名"这个核心思想后，其实很简单：

1. 核心公式：日期 - ROW_NUMBER() = 分组标识
2. 关键理解：连续日期的差值固定，中断后差值改变
3. 实际应用：用户留存、活动分析、系统监控等场景
4. 性能优化：合理建索引，大数据量时先过滤再计算

这个技巧不仅仅是为了应付面试，在实际工作中用处很大。特别是做数据分析的时候，连续性问题经常遇到。

建议大家多练习，可以在LeetCode上搜索"consecutive"相关的题目，或者自己造一些数据来验证理解。记住，熟能生巧！

最后提醒：面试时不要只写代码，一定要解释思路，特别是为什么日期减排名能work，这样能体现你的逻辑思维能力！