在用户行为分析或游戏运营中，累计连续登录天数是一个常见且重要的指标。例如需要统计每个用户历史上最长的连续登录天数，或者计算用户从注册至今的累计连续天数。这类问题本质上属于“连续区间识别”问题，而SQL作为集合操作语言，实现起来需要一定的技巧。下面通过一个典型的每日登录日志表结构，介绍两种实用算法：差值法和窗口函数排序法。
准备数据表结构
首先假设有一张登录日志表 login_log，包含用户ID和登录日期（去重后的）。实际场景中一个用户一天可能有多次登录，因此需要先做去重处理。表结构如下：
CREATE TABLE login_log (
user_id INT NOT NULL,
login_date DATE NOT NULL,
PRIMARY KEY (user_id, login_date)
);
为了演示方便，插入一些示例数据：
INSERT INTO login_log VALUES (1, '2024-01-01'), (1, '2024-01-02'), (1, '2024-01-03'), (1, '2024-01-05'), (1, '2024-01-06'), (1, '2024-01-07'), (2, '2024-01-01'), (2, '2024-01-03'), (2, '2024-01-04'), (2, '2024-01-05');
算法一：差值法（基于日期排序与差值）
核心思想：如果用户连续登录，那么登录日期和日期排序序号（row_number）之间的差值（login_date - row_number()）会是同一个值。属于同一个差值的日期就是连续的一段。然后对每个差值分组计数即可得到每个连续段的长度。
具体SQL如下：
WITH ordered AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM login_log
),
diff_group AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS group_id
FROM ordered
)
SELECT
user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS consecutive_days
FROM diff_group
GROUP BY user_id, group_id
ORDER BY user_id, start_date;
这段查询会输出每个用户每一段连续登录的起止日期及天数。如果再要取最长连续天数，只需在外层按用户分组取MAX(consecutive_days)即可。
算法二：窗口函数排序法（lag/lead判断相邻行）
另一种思路是使用 LAG 函数获取上一条登录日期，通过比较日期差是否为1来标记连续分段的开始，然后累加分组。
WITH ordered AS (
SELECT
user_id,
login_date,
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date
FROM login_log
),
group_start AS (
SELECT
user_id,
login_date,
CASE
WHEN prev_date IS NULL OR DATEDIFF(login_date, prev_date) > 1
THEN 1 ELSE 0
END AS new_group_flag
FROM ordered
),
group_id AS (
SELECT
user_id,
login_date,
SUM(new_group_flag) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id
FROM group_start
)
SELECT
user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS consecutive_days
FROM group_id
GROUP BY user_id, group_id
ORDER BY user_id, start_date;
该算法通过new_group_flag标记每个新连续段的开始（遇到间隔大于1天时标记为1），然后累计求和得到分组ID，最后分组聚合。
计算累计连续登录天数（截至当前）
有时需要计算用户“到目前为止”连续登录了多少天（例如用户今天登录了，需要看从什么时候开始一直没断过）。这其实是取最近一段连续登录的天数。可以利用上述分组结果，取每个用户分组ID最大的那段（即最新的一段），如果最新一段的结束日期是今天（或某个截止日期），则天数即为累计连续天数。
以下SQL计算每个用户截至昨天的累计连续登录天数：
WITH ordered AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM login_log
WHERE login_date <= CURDATE() -- 截止日期
),
diff_group AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS group_id
FROM ordered
),
group_agg AS (
SELECT
user_id,
group_id,
MAX(login_date) AS max_date,
COUNT(*) AS days
FROM diff_group
GROUP BY user_id, group_id
),
latest_group AS (
SELECT
user_id,
days
FROM (
SELECT
user_id,
days,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY max_date DESC) AS rn2
FROM group_agg
) t
WHERE rn2 = 1
)
SELECT * FROM latest_group;
这个查询会返回每个用户最新一段连续登录的天数。注意需要根据业务定义截止日期（比如昨天或今天）。如果用户今天没有登录，则最新一段可能截止到昨天，需要结合场景调整。
实际应用中的常见问题
1. 去重问题：如果登录日志表存在一天多次登录，一定要先做SELECT DISTINCT user_id, login_date或使用聚合去重，否则会影响连续判断。
2. 跨月问题：日期差值算法（DATE_SUB(login_date, INTERVAL rn DAY)）会自动处理跨月跨年，因为MySQL的日期加减是安全的。但要注意闰年和不同月份的天数差异，差值法正是利用日期的连续性，所以天然支持跨月。
3. 性能优化：如果数据量极大（上亿行），建议对(user_id, login_date)建立复合索引，并且只对近期数据做全量计算，历史累计最长天数可以预先物化存储。
4. NULL处理：当用户某天没有登录时，日志表中没有记录，所以不需要考虑填充缺失日期。连续段只基于存在记录的天数。
扩展：使用自定义变量（MySQL旧版本）
在MySQL 8.0之前标准SQL不支持窗口函数，可以用用户变量模拟。但现今大多数环境已支持窗口函数，建议优先使用窗口函数，代码更清晰。下面给出一种基于变量的实现方式作为参考：
SET @prev_user = NULL;
SET @prev_date = NULL;
SET @group_id = 0;
SELECT
user_id,
login_date,
@group_id := IF(
@prev_user = user_id AND DATEDIFF(login_date, @prev_date) = 1,
@group_id,
@group_id + 1
) AS group_id,
@prev_user := user_id,
@prev_date := login_date
FROM login_log
ORDER BY user_id, login_date;
之后再基于这个中间结果分组聚合即可。注意变量方式在多用户并发环境中可能有副作用，生产环境慎用。
熟练掌握累计连续登录天数算法，你就能轻松处理类似的“连续行为”统计需求，比如连续签到天数、连续消费天数、连续活跃天数等。核心思路先识别连续区间再聚合计数，这是SQL处理时序数据的基本功。