在用户行为分析、活跃度统计等业务场景中，判断用户是否连续登录是非常常见的需求，比如计算用户的最大连续登录天数、筛选连续登录超过N天的用户等。这类需求看似复杂，其实通过SQL的窗口函数配合日期处理逻辑就能轻松实现，下面介绍两种主流的解法。
一、基础数据准备
首先我们需要准备测试用的用户登录记录表，表结构如下：
-- 创建用户登录记录表
CREATE TABLE user_login_log (
user_id INT COMMENT '用户ID',
login_date DATE COMMENT '登录日期'
) COMMENT '用户登录记录表';
-- 插入测试数据
INSERT INTO user_login_log (user_id, login_date) VALUES
(1, '2024-05-01'),
(1, '2024-05-02'),
(1, '2024-05-03'),
(1, '2024-05-05'),
(1, '2024-05-06'),
(2, '2024-05-01'),
(2, '2024-05-03'),
(2, '2024-05-04'),
(2, '2024-05-05');
二、解法一：基于ROW_NUMBER窗口函数
实现逻辑
该解法的核心思路是：先对每个用户的登录日期按升序排序生成行号，然后用登录日期减去行号对应的天数，如果登录日期是连续的，那么减去行号后的日期结果会相同，通过这个相同的日期分组就能统计连续登录的天数。
完整SQL代码
WITH login_rank AS (
-- 对每个用户的登录日期排序生成行号
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM user_login_log
),
date_group AS (
-- 计算日期减去行号后的结果，连续日期的结果相同
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS group_date
FROM login_rank
)
-- 统计每个用户的连续登录分组信息
SELECT
user_id,
MIN(login_date) AS start_login_date,
MAX(login_date) AS end_login_date,
COUNT(*) AS continuous_days
FROM date_group
GROUP BY user_id, group_date
ORDER BY user_id, start_login_date;
结果说明
执行上述代码后，用户1的连续登录分组为：2024-05-01到2024-05-03（连续3天）、2024-05-05到2024-05-06（连续2天）；用户2的连续登录分组为：2024-05-01（1天）、2024-05-03到2024-05-05（连续3天）。
三、解法二：基于LAG窗口函数
实现逻辑
该解法通过LAG函数获取每个用户上一次的登录日期，计算当前登录日期和上一次登录日期的差值，如果差值为1则说明是连续登录，否则是新的连续登录周期的开始，最后通过累加标记的方式分组统计连续登录天数。
完整SQL代码
WITH login_lag AS (
-- 获取每个用户上一次的登录日期
SELECT
user_id,
login_date,
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS last_login_date
FROM user_login_log
),
login_flag AS (
-- 标记连续登录的开始位置，差值为1则连续，否则为新周期
SELECT
user_id,
login_date,
CASE
WHEN DATEDIFF(login_date, last_login_date) = 1 THEN 0
ELSE 1
END AS is_new_group
FROM login_lag
),
group_mark AS (
-- 累加标记得到每个连续登录分组的唯一标识
SELECT
user_id,
login_date,
SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id
FROM login_flag
)
-- 统计每个连续登录分组的详细信息
SELECT
user_id,
MIN(login_date) AS start_login_date,
MAX(login_date) AS end_login_date,
COUNT(*) AS continuous_days
FROM group_mark
GROUP BY user_id, group_id
ORDER BY user_id, start_login_date;
四、两种方法对比
| 对比维度 | ROW_NUMBER解法 | LAG解法 |
|---|---|---|
| 实现复杂度 | 较低，逻辑更简洁 | 稍高，需要多一层标记累加 |
| 适用场景 | 仅需判断连续登录天数，无特殊日期校验需求 | 需要额外校验登录日期间隔，可扩展更多间隔规则 |
| 性能表现 | 窗口函数计算量小，大数据量下性能更优 | 多一层计算，大数据量下性能略低 |
五、扩展需求实现
如果需要筛选连续登录超过3天的用户，可以在上述分组结果的基础上添加过滤条件：
-- 基于ROW_NUMBER解法的结果筛选连续登录超过3天的用户
WITH login_rank AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM user_login_log
),
date_group AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS group_date
FROM login_rank
),
continuous_group AS (
SELECT
user_id,
MIN(login_date) AS start_login_date,
MAX(login_date) AS end_login_date,
COUNT(*) AS continuous_days
FROM date_group
GROUP BY user_id, group_date
)
SELECT
user_id,
start_login_date,
end_login_date,
continuous_days
FROM continuous_group
WHERE continuous_days > 3
ORDER BY user_id, start_login_date;
通过以上两种方法，就能灵活实现SQL中用户连续登录状态的判断，开发者可以根据实际业务场景选择合适的解法。如果业务中存在同一用户一天多次登录的情况，需要先对登录日期做去重处理，再使用上述方法计算，避免重复数据影响统计结果。