在用户留存分析、活跃度统计等业务场景中，计算用户的连续登录天数是非常常见的需求。传统实现方式需要通过自连接或者变量逐行判断，逻辑复杂且执行效率较低，而SQL的窗口函数中的LAG和LEAD函数可以很高效地解决这个问题。
LAG和LEAD函数基础介绍
LAG和LEAD都是SQL标准中的窗口函数，用于获取当前行相邻行的数据，不需要进行表连接操作。
LAG函数语法
LAG函数用于获取当前行之前第N行的数据，语法如下：
LAG(字段名, 偏移量, 默认值) OVER (PARTITION BY 分组字段 ORDER BY 排序字段)
其中偏移量默认为1，表示获取上一行的数据；如果上一行不存在，会返回设置的默认值，未设置则默认返回NULL。
LEAD函数语法
LEAD函数用于获取当前行之后第N行的数据，语法和LAG类似：
LEAD(字段名, 偏移量, 默认值) OVER (PARTITION BY 分组字段 ORDER BY 排序字段)
基础数据准备
首先我们创建一个用户登录记录表，包含用户ID和登录日期两个核心字段，插入测试数据：
-- 创建登录记录表
CREATE TABLE user_login_log (
user_id INT,
login_date DATE
);
-- 插入测试数据
INSERT INTO user_login_log (user_id, login_date) VALUES
(1, '2024-01-01'),
(1, '2024-01-02'),
(1, '2024-01-03'),
(1, '2024-01-05'),
(1, '2024-01-06'),
(2, '2024-01-01'),
(2, '2024-01-03'),
(2, '2024-01-04'),
(2, '2024-01-05');
用LAG函数统计连续登录天数
我们可以通过LAG函数获取每个用户上一次的登录日期，然后判断当前登录日期和上一次登录日期是否相差1天，来识别连续登录的分组。
第一步：获取上一次登录日期
SELECT
user_id,
login_date,
LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS last_login_date
FROM user_login_log;
执行以上SQL后，会得到每个用户当前登录日期和上次登录日期的对应关系，其中每个用户的第一条记录last_login_date为NULL。
第二步：标记连续登录分组
如果当前登录日期和上一次登录日期相差1天，说明是连续登录，否则属于新的连续登录周期。我们可以通过日期相减的方式判断：
SELECT
user_id,
login_date,
-- 如果上次登录日期不存在，或者当前日期和上次日期相差1天，属于同一连续组，否则是新组
CASE
WHEN DATEDIFF(login_date, LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date)) = 1
THEN 0
ELSE 1
END AS is_new_group
FROM user_login_log;
这里不同数据库的日期相减函数可能有差异，比如MySQL用DATEDIFF，PostgreSQL用login_date - LAG(...) OVER(...)，Oracle用login_date - LAG(...) OVER(...)。
第三步：计算连续登录天数
我们对每个用户的连续组进行累加，得到每个登录记录所属的连续组编号，然后按用户和组编号分组统计数量，就是连续登录天数：
WITH login_with_group AS (
SELECT
user_id,
login_date,
SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id
FROM (
SELECT
user_id,
login_date,
CASE
WHEN DATEDIFF(login_date, LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date)) = 1
THEN 0
ELSE 1
END AS is_new_group
FROM user_login_log
) t
)
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;
执行以上SQL后，就可以得到每个用户每次连续登录的起始日期、结束日期和连续天数。
用LEAD函数识别连续登录区间
LEAD函数可以获取下一次的登录日期，我们可以用来判断当前登录是否是连续登录的最后一天，或者计算连续登录的结束时间。
比如我们要找到每个用户连续登录的最后一天，并且标记出来：
SELECT
user_id,
login_date,
LEAD(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS next_login_date,
CASE
WHEN DATEDIFF(LEAD(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date), login_date) = 1
THEN '否'
ELSE '是'
END AS is_continuous_end
FROM user_login_log;
如果下一次登录日期和当前日期相差1天，说明当前不是连续登录的最后一天，否则就是最后一天。
注意事项
- 不同数据库的窗口函数支持程度不同，MySQL从8.0版本开始支持LAG和LEAD，低版本无法使用。
- 日期计算函数在不同数据库中语法有差异，需要根据实际使用的数据库调整日期相减的逻辑。
- 如果登录记录存在同一天多次登录的情况，需要先对用户的登录日期去重，再进行连续登录统计，避免重复计算。
去重示例：
-- 先对用户的登录日期去重，再统计连续登录 SELECT DISTINCT user_id, login_date FROM user_login_log;