如何用MySQL查询连续登录天数，Lead与Lag函数如何巧妙运用？

本文共计1043个文字，预计阅读时间需要5分钟。

核心思路是：

常见错误是忘记 ORDER BY login_date —— 窗口函数不指定排序，结果完全不可预测；还有人漏掉 PARTITION BY user_id，导致跨用户比较，数据全乱。

• LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) 是标准写法，第二个参数 1 表示取前 1 行（可省略，默认就是 1）
• 注意 login_date 必须是 DATE 类型，如果是 DATETIME，得先 DATE(login_date) 去时分秒，否则同一天多次登录会被误判为“非连续”
• 首次登录时 LAG 返回 NULL，需要用 IS NULL 单独处理，不能直接参与减法运算（否则整行变 NULL）

怎么用 LEAD 判断连续段的结束位置

LEAD 和 LAG 是对称操作，但实战中更常用 LAG 做“向前比”，因为连续性天然依赖“上一天是否存在”。不过当你需要标记每段连续登录的终点（比如导出“某次连续登录共 5 天”），LEAD 就派上用场了：看下一天是否断开。

典型场景是生成连续区间：先用 LAG 标出每个登录日是否为连续段开头（即前一天不连续），再用 LEAD 标出是否为结尾（即后一天不连续）。两者结合就能圈出完整区间。

• LEAD(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) 取下一行日期
• 判断结尾的条件是：LEAD(login_date) IS NULL OR DATEDIFF(LEAD(login_date), login_date) > 1
• 别直接用 LEAD(login_date) - login_date > 1——MySQL 中日期相减是隐式转为数字（如 20240501 - 20240430 = 71），结果不可靠，必须用 DATEDIFF()

怎么聚合出最大连续天数（含空洞容错）

单纯用 LAG/LEAD 只能标出断点，真正要算“最长连续多少天”，得先生成连续组号（group id），再按组聚合。标准做法是用「日期 - 行号」构造等差特征：同一连续段内，login_date - ROW_NUMBER() 的值恒定。

这个技巧容易被忽略的是：行号必须基于已去重且严格按日期排序的数据。如果原始表有重复登录记录（同用户同天多条），必须先 DISTINCT 或 GROUP BY user_id, DATE(login_date)，否则行号错位，组号就崩了。

• 先清理数据：SELECT DISTINCT user_id, DATE(login_date) AS dt FROM login_log
• 再计算组号：dt - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) DAY
• 最后 GROUP BY user_id, 组号，用 COUNT(*) 得单段天数，外层再 MAX() 即可

MySQL 5.7 不支持窗口函数？替代方案只能硬关联

MySQL 5.7 及更早版本没有 LAG/LEAD，强行用会报错 ERROR 1064: You have an error in your SQL syntax。这时候只能用自连接或相关子查询模拟，性能差但可行。

例如找上一次登录日：对每条记录，查同用户、日期小于当前日期的最大日期。虽然语法啰嗦，但逻辑清晰，适合低频或小数据量场景。

• 子查询写法：(SELECT MAX(t2.login_date) FROM login_log t2 WHERE t2.user_id = t1.user_id AND t2.login_date
• 自连接写法更慢但可读性稍好，注意加 t2.login_date 和 <code>NOT EXISTS 排除中间值，否则会多匹配
• 务必给 (user_id, login_date) 加联合索引，否则 N² 复杂度，万级数据就卡死

真正上线前，先确认 MySQL 版本：SELECT VERSION();。8.0+ 才有窗口函数，别在 5.7 上死磕 LAG 语法。