如何用 SQL 窗口函数统计连续登录天数？

Nora

发布时间：2026-08-24 | 10人浏览过

原创

前言

连续登录天数是留存分析、会员激励里很常见的指标，但一到 SQL 实操，很多人会卡在“怎么识别连续段”这一步。本文从 `ROW_NUMBER()` 加日期序数分组讲起，再补上 `LAG()` 的适用范围、最长连续天数的完整写法，以及去重、时区、时间精度这些最容易把结果带偏的细节，方便你判断一条 SQL 到底是真的算对了，还是只是刚好跑通。

连续登录天数看起来像一道普通聚合题，但只要数据里出现断连、同天多次登录、跨月日期，甚至时区转换，这个问题就很容易算错。更稳妥的办法，是先用窗口函数把“连续的日期”识别成同一组，再统计每组长度；本文按这个思路拆开说明，并补上跨库写法和几个最常见的坑。

为什么用 ROW_NUMBER() 加日期相减就能识别连续段

核心思路是：对同一用户的登录日期按时间升序排列后，连续日期的“日期序数 - 行号”会保持不变，这个固定差值就可以当作连续段的分组标识。

连续登录分组原理示意图 — **连续登录分组原理图**用日期序数减去行号后，连续日期会落入同一个固定差值组，断连时组值变化。

比如某用户在 2024-01-01、2024-01-02、2024-01-03 登录，ROW_NUMBER() 会得到 1/2/3；如果把日期转成序数，像 TO_DAYS(date) 那样处理，则可能得到 739250/739251/739252，三者分别减去行号后的结果都等于 739249。一旦中间断一天，这个差值就会变化，连续组也就被自然切开了。

这套方法成立的前提

必须先按 user_id 分组，再按 login_date 升序排序，否则 ROW_NUMBER() 的结果没有业务意义。
日期必须先转换成可比较的“日序数”，不能直接把不同数据库的日期类型写法混用。
如果同一用户同一天有多条登录记录，先去重，否则连续天数会被放大。

不同数据库的日期序数写法

MySQL：TO_DAYS(login_date)
PostgreSQL：login_date::date - '1970-01-01'::date
SQLite：julianday(login_date)

这部分不要直接照搬 MySQL 写法到其他数据库，否则 SQL 语义可能一致，结果却不一致。

如果原始日志表记录的是每次登录行为，而不是“每日是否登录”，那统计连续天数前必须先按用户和日期去重。常见做法包括：

PostgreSQL：DISTINCT ON (user_id, login_date)
通用思路：GROUP BY user_id, login_date

LAG() 能不能用：可以，但更适合判断是否断连

LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) 可以拿到前一次登录日期，再和当前日期比较，判断两次登录是否相隔 1 天。这种写法适合检查“今天是否接上昨天”，但不适合直接算出最长连续登录天数。

LAG() 的几个限制

它只能比较相邻两行，无法直接产出“最长连续多少天”，要继续配合递归或自连接才能完成量化。
跨月场景如 2024-01-31 → 2024-02-01，用 DATE_ADD(lag_date, INTERVAL 1 DAY) 比 lag_date + 1 更安全。
如果中间某天缺失、后面又恢复登录，LAG() 不会自动帮你分段，还需要额外处理首行的 lag_date IS NULL。

所以，LAG() 更像是“断连检测工具”，而不是完整的“连续段统计方案”。

怎么查每个用户的最长连续登录天数

要得到每个用户的最长连续登录天数，通用做法是两步：

最长连续登录天数计算流程图 — **最长连续登录天数计算流程**先算 grp，再按用户和连续段分组计数，最后取每个用户的最大值。

先用窗口函数为连续日期打上同一个组标识 grp
再按 user_id + grp 聚合，统计每段长度，最后取最大值

典型 SQL 如下：

SELECT user_id, MAX(consecutive_days) AS max_streak
FROM (
SELECT user_id,
 COUNT(*) AS consecutive_days,
 grp
FROM (
SELECT user_id,
 login_date,
 TO_DAYS(login_date) - ROW_NUMBER() OVER (
 PARTITION BY user_id ORDER BY login_date
 ) AS grp
FROM login_log
) t1
GROUP BY user_id, grp
) t2
GROUP BY user_id;