在用户留存、活跃回访、连续签到等分析场景中，统计用户连续登录天数是一项基础工作。连续登录并不是简单统计登录总天数，而是要识别相邻日期之间是否没有中断，并将一段不间断的登录行为归为一个连续周期。传统写法通常依赖自连接、游标或变量，逻辑分散且难以维护。窗口函数能够在不折叠行数据的前提下，为每一行补充前后相邻行的信息，因此非常适合处理这类行间比较问题。
连续登录统计的业务含义与窗口函数优势
连续登录统计的核心难点不在聚合，而在分组。一个用户可能在不同时间段内出现多次连续登录，例如一次连续三天，中断后又连续两天。如果只按用户分组统计登录次数，会把多段连续行为混在一起；如果只比较总天数，又无法回答每一段连续登录从何时开始、到何时结束、持续多久。因此，必须先识别登录日期之间的断点，再把没有断点的日期划分到同一个连续区间。
传统 SQL 实现往往通过自连接寻找相邻日期，或者使用变量逐行标记。自连接会导致数据膨胀，且当连续天数较长时条件判断会变得复杂；变量写法虽然可以减少连接，但依赖行的处理顺序，可移植性和可读性都较差。窗口函数的优势在于，它保留明细行的同时，可以在当前行上直接访问同一分区内排序后的前一行或后一行，使相邻日期比较变得直观。
本文将围绕 LAG 与 LEAD 两个窗口函数展开。LAG 用于回看上一次登录日期，适合判断当前登录是否接续前一次；LEAD 用于前瞻下一次登录日期，适合判断当前登录是否属于某段连续区间的末尾。两者结合，可以完成连续登录区间的识别、标记与统计。
基础数据准备以及 LAG 与 LEAD 的相邻行视角
为了演示完整流程，先准备一张简化的登录记录表。表中只保留用户标识和登录日期两个关键字段。为了让示例不依赖具体日历日期，这里使用相对当前日期的方式构造测试数据。实际业务中，登录日期通常来自日志明细表，只要保证日期字段是按天粒度的 DATE 类型即可。
-- 创建用户登录记录表
CREATE TABLE user_login_log (
user_id INT NOT NULL,
login_date DATE NOT NULL
);
-- 插入相对日期测试数据
INSERT INTO user_login_log (user_id, login_date) VALUES
(1, CURRENT_DATE - INTERVAL 6 DAY),
(1, CURRENT_DATE - INTERVAL 5 DAY),
(1, CURRENT_DATE - INTERVAL 4 DAY),
(1, CURRENT_DATE - INTERVAL 2 DAY),
(1, CURRENT_DATE - INTERVAL 1 DAY),
(2, CURRENT_DATE - INTERVAL 6 DAY),
(2, CURRENT_DATE - INTERVAL 4 DAY),
(2, CURRENT_DATE - INTERVAL 3 DAY),
(2, CURRENT_DATE - INTERVAL 2 DAY);
LAG 和 LEAD 都属于窗口函数，它们不会改变结果集的行数，而是根据 PARTITION BY 指定的分组和 ORDER BY 指定的顺序，在当前行附近取值。PARTITION BY 相当于为每个用户建立独立的处理范围，ORDER BY 则决定日期先后。这样每个用户的登录记录只会与自己的历史或未来记录比较，不会跨用户误判。
LAG 的基本示例
LAG 的含义是获取当前行在排序结果中的前一行数据。偏移量为一时，表示取上一条记录；如果不存在上一条记录，可以返回默认值，未显式指定时通常返回 NULL。在连续登录场景中，第一行没有上一次登录日期，因此通常将它视为新的连续周期起点。
SELECT
user_id,
login_date,
LAG(login_date, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS prev_login_date
FROM user_login_log;
在这段查询中，窗口函数会为每个 user_id 单独排序，然后把上一行的 login_date 作为 prev_login_date 附加到当前行。对于每个用户的第一条记录，prev_login_date 为 NULL，这正好可以作为判断连续周期开始的依据。
LEAD 的基本示例
LEAD 与 LAG 方向相反，它获取当前行之后的一行数据。偏移量为一时，表示取下一条记录；当没有下一条记录时，同样会返回默认值或 NULL。在连续登录分析中，最后一行往往意味着当前连续段可能结束。
SELECT
user_id,
login_date,
LEAD(login_date, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS next_login_date
FROM user_login_log;
通过 next_login_date，可以判断当前登录日期之后是否还有紧邻的登录记录。如果下一条记录不存在，或者与当前日期间隔不是一天，就说明当前行很可能是某段连续登录的结束位置。
使用 LAG 构造连续登录分组并统计天数
第一步是获取上一次登录日期。只有知道当前日期与前一次日期之间的间隔，才能判断登录是否连续。如果间隔是一天，说明用户没有中断；如果间隔大于一天，或者根本没有前一次登录，就说明出现了新的连续周期。
SELECT
user_id,
login_date,
LAG(login_date, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS prev_login_date
FROM user_login_log;
第二步是根据间隔生成新组标记。这里使用 is_new_group 表示当前行是否开启新的连续登录段。当前用户没有上一次登录日期时，标记为一；当前日期与上一次日期相差一天时，标记为零；其他情况也标记为一。这个标记本身不是连续天数，而是连续区间发生变化的边界信号。
WITH deduped_login AS (
SELECT DISTINCT
user_id,
login_date
FROM user_login_log
),
login_with_prev AS (
SELECT
user_id,
login_date,
LAG(login_date, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS prev_login_date
FROM deduped_login
)
SELECT
user_id,
login_date,
prev_login_date,
CASE
WHEN prev_login_date IS NULL THEN 1
WHEN DATEDIFF(login_date, prev_login_date) = 1 THEN 0
ELSE 1
END AS is_new_group
FROM login_with_prev;
第三步将边界信号转换为组编号。对每个用户按登录日期排序，并对 is_new_group 做累计求和，可以得到每一行所属的连续区间编号。每出现一次新组标记，组编号就增加一；连续登录的日期之间标记为零，因此会保持在同一个组编号内。最后按用户和组编号聚合，统计每个连续区间的开始日期、结束日期和天数。
WITH deduped_login AS (
SELECT DISTINCT
user_id,
login_date
FROM user_login_log
),
login_with_prev AS (
SELECT
user_id,
login_date,
LAG(login_date, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS prev_login_date
FROM deduped_login
),
login_with_flag AS (
SELECT
user_id,
login_date,
CASE
WHEN prev_login_date IS NULL THEN 1
WHEN DATEDIFF(login_date, prev_login_date) = 1 THEN 0
ELSE 1
END AS is_new_group
FROM login_with_prev
),
login_with_group AS (
SELECT
user_id,
login_date,
SUM(is_new_group) OVER (
PARTITION BY user_id
ORDER BY login_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS group_id
FROM login_with_flag
)
SELECT
user_id,
group_id,
MIN(login_date) AS continuous_start_date,
MAX(login_date) AS continuous_end_date,
COUNT(*) AS continuous_login_days
FROM login_with_group
GROUP BY user_id, group_id
ORDER BY user_id, group_id;
这段查询的结果中，每一行代表一个用户的一段连续登录行为。continuous_start_date 表示该段连续登录的第一天，continuous_end_date 表示最后一天，continuous_login_days 表示这段连续登录包含多少天。若业务只关心用户历史最长连续登录天数，可以在外层再按用户取 continuous_login_days 的最大值。
使用 LEAD 判断连续登录的结束位置
LAG 从当前行回看过去，更适合判断一行是否属于新的连续段；LEAD 从当前行看向未来，更适合判断一行是否属于连续段的尾部。如果当前登录日期之后没有下一次登录，或者下一次登录日期与当前日期间隔不是一天，那么当前日期就可以视为连续登录的结束日期。
WITH login_with_next AS (
SELECT
user_id,
login_date,
LEAD(login_date, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS next_login_date
FROM user_login_log
)
SELECT
user_id,
login_date,
next_login_date,
CASE
WHEN next_login_date IS NULL THEN '是'
WHEN DATEDIFF(next_login_date, login_date) = 1 THEN '否'
ELSE '是'
END AS is_continuous_end
FROM login_with_next;
在这个查询中，next_login_date 表示同一用户下一次登录日期。当 next_login_date 为 NULL 时，说明当前记录已经是该用户现有数据中的最后一次登录；当 DATEDIFF 的结果不是一天时，说明下一次登录与当前登录之间存在中断。两种情况都意味着当前行处于连续登录区间的边界位置。
在实际分析中，LEAD 常用于生成连续区间结束标志，或者辅助判断某个连续段是否已经完成。如果同时使用 LAG 与 LEAD，还可以进一步标记每一行既是开始还是结束，从而得到更细的区间属性。不过，如果目标只是统计每段连续登录的天数，通常使用 LAG 生成边界标记，再通过累计求和得到组编号，会更直接。
数据去重、日期差异与工程注意事项
在真实业务表中，登录日志往往不是天然干净的。同一个用户在同一天可能产生多条登录记录，例如多次登录、多端登录或日志重复写入。如果不先去重，连续登录统计会出现偏差。一方面，重复日期会让 COUNT 统计出的天数大于实际连续天数；另一方面，LAG 与 LEAD 会把同一天的重复记录识别为相邻行，导致日期差值为零，从而错误地触发新组标记。
-- 按用户和登录日期去重
SELECT DISTINCT
user_id,
login_date
FROM user_login_log;
日期差值计算在不同数据库中并不完全一致。MySQL 常用 DATEDIFF，PostgreSQL 可以直接用日期相减，Oracle 也支持日期相减但可能需要关注时间与秒级分量。如果登录字段包含时分秒，还应先将字段规范化到按天粒度，否则即使日期看起来相同，也可能因为时间部分不同而影响比较结果。
| 关注点 | 处理建议 |
|---|---|
| 同一天多条登录 | 先按用户和登录日期去重，再进入窗口函数计算 |
| 数据库兼容性 | 确认当前数据库是否支持 LAG、LEAD 以及公共表表达式 |
| 日期粒度 | 尽量使用 DATE 类型，或将时间字段截断到天 |
| 排序稳定性 | 按用户分区，并按登录日期升序排序，避免跨用户比较 |
性能方面，窗口函数通常比多层自连接更容易优化，但仍建议先过滤分析所需的时间范围，避免全量历史数据参与计算。如果登录表非常大，可以在用户标识和登录日期上建立合适的索引，并优先在明细层完成去重。连续登录问题本质上属于间隔与孤岛问题，LAG 与 LEAD 提供了清晰的相邻行视角，而累计求和则是把相邻行关系转化为分组编号的关键步骤。
总结
统计用户连续登录天数的关键，是把每一条登录记录放入正确的连续区间。LAG 通过获取上一次登录日期，帮助判断当前行是否开启新的连续段；LEAD 通过获取下一次登录日期，帮助判断当前行是否结束一个连续段。在得到新组标记后，利用窗口函数进行累计求和，就能把连续日期划分成独立组。
整体实现可以概括为四个步骤：准备按天粒度的登录明细，使用 LAG 或 LEAD 获取相邻登录日期，根据日期间隔生成边界标记，最后按用户和连续组编号聚合统计天数。掌握这个思路后，不仅可以处理连续登录，还可以迁移到连续签到、连续下单、连续活跃等类似分析场景。