在用户留存分析、活跃度统计等业务场景中，统计用户最大连续登录天数是高频需求。相比于逐行判断前后日期是否连续的复杂逻辑，差值法分组结合COUNT累计的方案逻辑更清晰，执行效率也更高，适配大多数关系型数据库。
实现原理说明
差值法的核心逻辑基于一个规律：如果是连续日期，那么登录日期减去排序序号的结果是相同的。具体步骤如下：
- 先过滤出用户的登录记录，按登录日期去重，避免同一天多次登录影响统计
- 对每个用户的登录日期按升序排序，生成连续的序号
- 用登录日期减去排序序号，得到分组标识，相同标识的日期属于同一个连续登录区间
- 按用户和分组标识分组，统计每个组的日期数量，即为该连续区间的登录天数
- 最后取每个用户的最大统计值，就是最大连续登录天数
基础表结构示例
假设我们有用户登录记录表user_login_log，结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| id | bigint | 记录主键 |
| user_id | bigint | 用户ID |
| login_date | date | 登录日期 |
完整SQL实现（以MySQL为例）
以下是统计所有用户最大连续登录天数的完整SQL代码：
-- 统计每个用户的最大连续登录天数
WITH distinct_login AS (
-- 去重同一用户同一天的多次登录记录
SELECT
user_id,
login_date
FROM user_login_log
GROUP BY user_id, login_date
),
login_with_rank AS (
-- 给每个用户的登录日期排序，生成序号
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM distinct_login
),
login_group AS (
-- 计算差值，得到连续分组标识
SELECT
user_id,
login_date,
-- 日期减去序号，相同结果即为连续区间
DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag
FROM login_with_rank
),
group_count AS (
-- 统计每个连续区间的天数
SELECT
user_id,
group_flag,
COUNT(1) AS continuous_days
FROM login_group
GROUP BY user_id, group_flag
)
-- 取每个用户的最大连续天数
SELECT
user_id,
MAX(continuous_days) AS max_continuous_login_days
FROM group_count
GROUP BY user_id;
代码逻辑解析
1. distinct_login公用表表达式先对user_login_log表按user_id和login_date分组，避免同一天多次登录导致统计偏差。
2. login_with_rank使用窗口函数ROW_NUMBER()，按用户分区、登录日期升序生成序号rn，序号是连续的整数。
3. login_group中通过DATE_SUB(login_date, INTERVAL rn DAY)计算分组标识，连续日期的计算结果相同，非连续日期结果不同，自然形成分组。
4. group_count按用户和分组标识分组，统计每个组的记录数，即该连续区间的登录天数。
5. 最后外层查询取每个用户的最大连续天数，得到最终结果。
注意事项
- 如果登录日期是
datetime类型，需要先转换为date类型再处理，避免时间部分影响日期差值计算 - 该方法依赖窗口函数，MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库可直接使用，低版本MySQL需要额外调整序号生成逻辑
- 如果需要统计指定时间范围内的最大连续登录天数，可以在
distinct_login阶段增加时间过滤条件
单用户统计示例
如果只需要统计用户ID为1001的用户的最大连续登录天数，可以在基础查询上增加过滤条件：
WITH distinct_login AS (
SELECT
user_id,
login_date
FROM user_login_log
WHERE user_id = 1001
GROUP BY user_id, login_date
),
login_with_rank AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM distinct_login
),
login_group AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag
FROM login_with_rank
),
group_count AS (
SELECT
user_id,
group_flag,
COUNT(1) AS continuous_days
FROM login_group
GROUP BY user_id, group_flag
)
SELECT
user_id,
MAX(continuous_days) AS max_continuous_login_days
FROM group_count
GROUP BY user_id;