连续登录天数是用户活跃度分析中最经典的需求之一。常规做法是先用窗口函数给登录记录排序，再用日期减去序号得到一个辅助分组字段，相同分组内的记录即为连续登录。这个方法在一年之内运行良好，但当年份从12月31日切换到次年1月1日时，不少写法会突然算错，把本应连续的两天拆成两个分组。问题的根源不在窗口函数，而在于日期计算的方式。本文将围绕跨年这一边界场景，系统讲解SQL中处理日期连续性的正确姿势。
一、连续登录计算的基本原理：日期差值法
处理连续登录的核心思路可以概括为一句话：如果两条登录记录是连续的，那么日期与排序序号的差值应该保持不变。假设某用户在1月1日、1月2日、1月3日登录，按日期排序后序号分别是1、2、3，用日期减去序号，得到三个完全相同的差值，说明这三天属于同一个连续区间。一旦某天中断，例如1月4日没有登录，1月5日的序号虽然加一，但日期跳了两天，差值就会发生变化，从而被划分到新的分组。
这个方法的巧妙之处在于把“连续性判断”转化成了“分组判断”，而分组在SQL中是非常成熟的操作。下面是一个基础实现：
-- 建表并插入跨年测试数据
CREATE TABLE login_log (
user_id INT,
login_date DATE
);
INSERT INTO login_log VALUES
(1001, '2023-12-29'),
(1001, '2023-12-30'),
(1001, '2023-12-31'),
(1001, '2024-01-01'), -- 跨年仍然连续
(1001, '2024-01-02'),
(1001, '2024-01-05'); -- 中断两天后的新区间
-- 第一步：去重并排序，计算差值
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
DATE_SUB(login_date,
INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
FROM (SELECT DISTINCT user_id, login_date FROM login_log) t;
在上面的查询中，grp列就是分组依据。对于12月29日到次年1月2日这五条连续记录，虽然年份变了，但由于DATE_SUB是真正基于日期的减法运算，跨年时日期数值依然正确递增，差值始终保持一致，因此它们会被归入同一个分组。这正是该方案能够天然支持跨年的原因：它操作的是日期本身，而不是日期的某个碎片化部分。
二、跨年场景下常见的错误写法及成因分析
既然日期差值法天然支持跨年，为什么实际项目中还是频繁出现算错的情况？原因大多出在开发者为了“简化”计算而引入了破坏日期连续性的处理方式。下面列举三种典型错误。
第一种错误是按年分组后再排序。有的写法先按用户和年份分区，再在每年内部计算序号：
-- 错误示范：按年分区导致跨年断层
SELECT
user_id,
login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id, YEAR(login_date)
ORDER BY login_date
) AS rn
FROM login_log;
这种写法下，2023年12月31日的序号是该年内的最后一个，而2024年1月1日的序号重新从1开始。即便后续用日期减序号，两条相邻记录的差值也会出现跳变，连续区间被硬生生切断。PARTITION BY中一旦出现YEAR(login_date)这类字段，就等于人为声明了年份是分组边界，跨年连续性必然被破坏。
第二种错误是用字符串拼接构造辅助日期。例如先截取月和日拼成数字再比较：
-- 错误示范：用月日拼接判断连续，1231 与 0101 被视为断档
SELECT
user_id,
login_date,
CAST(DATE_FORMAT(login_date, '%m%d') AS SIGNED) AS mmdd
FROM login_log;
12月31日拼出1231，次年1月1日拼出0101，数值上从1231骤降到101，任何基于这个字段的连续性判断都会失败。字符串截取丢弃了年份信息，本质上已经不是日期运算，而是文本游戏。
第三种错误相对隐蔽：用时间戳秒数取模或者按周几分组来判断连续。周几本身是七天一个循环，跨周时必然跳变，这种方案只能用于判断“每周固定某天登录”之类的周期性需求，绝不能用于连续天数计算。
三、完整方案：计算每个用户的最大连续登录天数
理解原理并避开陷阱后，我们给出一个完整的、可直接运行的方案，输出每个用户的最大连续登录天数以及区间的起止日期。整个查询分为三步：去重排序、差值分组、聚合统计。
SELECT
user_id,
MAX(streak_days) AS max_streak_days
FROM (
SELECT
user_id,
grp,
COUNT(*) AS streak_days
FROM (
SELECT
user_id,
login_date,
DATE_SUB(
login_date,
INTERVAL ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) DAY
) AS grp
FROM (SELECT DISTINCT user_id, login_date FROM login_log) t1
) t2
GROUP BY user_id, grp
) t3
GROUP BY user_id;
对于测试数据，用户1001在2023-12-29到2024-01-02之间连续登录五天，最大连续天数就是5。如果还想拿到区间的起止日期，只需在内层聚合中额外输出MIN(login_date)和MAX(login_date)即可：
SELECT
user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS streak_days
FROM (
SELECT
user_id,
login_date,
DATE_SUB(
login_date,
INTERVAL ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) DAY
) AS grp
FROM (SELECT DISTINCT user_id, login_date FROM login_log) t1
) t2
GROUP BY user_id, grp
ORDER BY user_id, start_date;
需要注意DISTINCT去重这一步不可省略。如果业务表中一天可能产生多条登录记录，不去重会导致序号与日期错位，差值不再稳定，分组结果随之混乱。去重应该放在窗口计算之前完成。
四、不同数据库的语法差异与替代方案
上述示例基于MySQL语法，日期减法使用DATE_SUB加INTERVAL。换成其他数据库时，语法略有不同，但思路完全一致。
在PostgreSQL中，日期类型可以直接与整数相加减：
-- PostgreSQL 写法
SELECT
user_id,
login_date,
login_date - ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
)::INTEGER AS grp
FROM (SELECT DISTINCT user_id, login_date FROM login_log) t;
在Hive或Spark SQL中，使用DATE_SUB函数配合窗口函数，写法与MySQL几乎相同，但要注意Hive的低版本中DISTINCT与窗口函数不能出现在同一层查询，需要用子查询分开：
-- Hive 写法
SELECT
user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS streak_days
FROM (
SELECT
user_id,
login_date,
DATE_SUB(
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)
) AS grp
FROM (
SELECT DISTINCT user_id, login_date FROM login_log
) dedup
) tmp
GROUP BY user_id, grp;
另外一种跨库通用的技巧是先把日期转成距离某个固定基准日的天数，再减去序号。例如DATEDIFF(login_date, '1970-01-01')得到一个整数，任何数据库都支持整数减法，这样就彻底规避了各数据库日期函数的语法差异，在需要编写跨平台SQL时非常实用。
五、延伸场景：按自然月和自然周的计算技巧
掌握了差值法的本质后，可以把它推广到更多场景。如果需求是判断连续自然月登录（例如每月至少登录一次且月份不间断），思路是先按用户和月份去重，再对月份序列做同样的差值处理：
SELECT
user_id,
DATE_FORMAT(login_date, '%Y-%m') AS login_month
FROM login_log
GROUP BY user_id, DATE_FORMAT(login_date, '%Y-%m')
对去重后的月份，不能直接用字符串相减，而要换算成月度序号，即年份乘以12加上月份：
SELECT
user_id,
login_month,
YEAR(login_month) * 12 + MONTH(login_month)
- ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_month) AS grp
FROM monthly_login;
这里的关键同样是把周期单位换算成单调递增的整数。月份序号跨年时从2023年12月的24288跳到2024年1月的24289，只增加1，连续性得以保留。这个原则适用于任何周期：自然周可以用周序号，季度可以用季度序号，只要保证换算后的数值随时间严格递增且间隔均匀，差值法就永远有效。
反过来说，判断一个方案是否会在跨年时出错，也可以用这个原则来检验：如果把日期或周期转换成了循环往复的数值（如月日拼接、周几、月份单独使用），跨边界必然出错；如果转换成了单调递增的数值（如完整日期、月度序号、时间戳），则天然安全。理解了这一点，连续登录、连续签到、连续消费等一类问题就都可以用同一套模板解决，不再需要针对每个边界场景单独打补丁。