SQL如何求每个用户最长连续登录_SQL分组求最长连续登录

先通过ROW_NUMBER()与日期差值生成连续登录分组，再按用户和分组统计最大天数。

想在sql里揪出每个用户最长的连续登录天数？这问题听起来简单，但其实藏着一些小技巧。核心思路嘛，就是得先把那些连续的登录日期巧妙地“打包”成一个个小块，然后数数每个块里有多少天，最后再从这些块里找出最长的那一个。这过程，通常少不了窗口函数和日期计算的‘魔法’。

解决方案

要解决这个挑战，我们通常会用到一个非常经典的SQL技巧：结合

ROW_NUMBER()

窗口函数和日期运算来创建“连续组”。这个方法在我看来，既优雅又高效。

假设我们有一个

user_logins

表，包含

user_id

(用户ID) 和

login_time

(登录时间戳)。

-- 假设表结构: -- CREATE TABLE user_logins ( --     user_id INT, --     login_time dateTIME -- );  -- 插入一些示例数据 -- INSERT INTO user_logins (user_id, login_time) VALUES -- (1, '2023-01-01 08:00:00'), -- (1, '2023-01-02 09:00:00'), -- (1, '2023-01-03 10:00:00'), -- (1, '2023-01-05 11:00:00'), -- 这里断开 -- (1, '2023-01-06 12:00:00'), -- (2, '2023-01-01 13:00:00'), -- (2, '2023-01-02 14:00:00'), -- (3, '2023-01-05 15:00:00');  WITH UserDailyLogins AS (     -- 步骤1: 确保每个用户每天只算一次登录     -- 这一步很重要，因为用户可能一天内多次登录，但我们只关心“是否登录了这一天”     SELECT DISTINCT         user_id,         CAST(login_time AS DATE) AS login_day -- 统一转换为日期类型，忽略具体时间     FROM         user_logins ), RankedLogins AS (     -- 步骤2: 为每个用户的登录日期排序，并计算一个“分组标识”     -- 这是核心魔法所在。我们给每个用户的登录日期按顺序编号 (rn)     -- 然后用 login_day 减去 rn 天。     -- 如果日期是连续的 (如 2023-01-01, 2023-01-02, 2023-01-03)，     -- 对应的 rn 也是连续的 (1, 2, 3)。     -- 那么 2023-01-01 - 1天 = X     --      2023-01-02 - 2天 = X     --      2023-01-03 - 3天 = X     -- 结果 X 会是同一个值，这个值就成了我们“连续块”的唯一标识。     SELECT         user_id,         login_day,         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) AS rn,         -- mysql 示例: DATE_SUB(日期, INTERVAL 数量 单位)         DATE_SUB(login_day, INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day)) DAY) AS login_group_id         -- 其他数据库的日期减法可能不同:         -- postgresql/SQL Server: login_day - (ROW_NUMBER() OVER (...) * INTERVAL '1 day')         -- oracle: login_day - (ROW_NUMBER() OVER (...))     FROM         UserDailyLogins ), ConsecutiveStreaks AS (     -- 步骤3: 根据 user_id 和我们创建的 login_group_id 分组，计算每个连续块的长度     -- 现在，所有属于同一个连续登录序列的日期，都会有相同的 login_group_id。     -- 我们只需要按这个 ID 分组，然后计数，就能得到每个连续序列的长度了。     SELECT         user_id,         login_group_id,         count(login_day) AS streak_length     FROM         RankedLogins     GROUP BY         user_id, login_group_id ) -- 步骤4: 找出每个用户最长的连续登录天数 -- 最后一步，从每个用户的所有连续序列长度中，选出最大的那个。 SELECT     user_id,     MAX(streak_length) AS longest_consecutive_login FROM     ConsecutiveStreaks GROUP BY     user_id ORDER BY     user_id;

为什么传统的

GROUP BY

难以直接解决连续性问题？

我记得我第一次遇到这类问题时，直觉就是想用

GROUP BY login_day

来着，结果发现根本行不通。传统的

GROUP BY

聚合函数，比如

COUNT()

、

SUM()

，它处理的是“相同值”的集合。它能告诉你某个用户总共登录了多少天，或者某个日期有多少用户登录，但它对“连续性”这个概念是完全无感的。

想象一下，你有一串日期：

2023-01-01, 2023-01-02, 2023-01-03, 2023-01-05

。对

GROUP BY

来说，

2023-01-01

就是一个独立的组，

2023-01-02

是另一个独立的组，它并不会“知道”前三个日期是连在一起的。它缺乏一种“记忆”或者说“上下文感知”的能力，无法跨行去判断日期的顺序和间隔。要识别这种序列模式，我们需要更高级的工具，而窗口函数恰好提供了这种能力。在我看来，这是

GROUP BY

的设计初衷所限，它更侧重于统计而非序列分析。

窗口函数在处理这类序列问题中的核心作用是什么？

窗口函数在处理序列问题上简直是“救星”级别的存在。对于我们这个“最长连续登录”的问题，

ROW_NUMBER()

扮演了至关重要的角色。

它的核心作用在于：

AISEO

AI创作对SEO友好的文案和文章

36

查看详情

1. 赋予序列号：

   ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day)

   这部分，它的

   PARTITION BY user_id

   确保了我们是针对每个用户独立编号，不会把不同用户的登录混淆。而

   ORDER BY login_day

   则保证了编号是按照登录日期递增的。这样，每个用户每次登录都有了一个独一无二的、且按时间顺序排列的序号（

   rn

   ）。

2. 创建“连续组”标识： 真正的魔法在于

   login_day - rn

   这个操作。当日期是连续的时候，比如

   Day1, Day2, Day3

   ，对应的

   rn

   是

   1, 2, 3

   。那么

   Day1 - 1

   ,

   Day2 - 2

   ,

   Day3 - 3

   得到的结果会是同一个日期（或者一个固定值）。一旦日期不连续了，比如

   Day3

   之后是

   Day5

   ，那么

   Day5 - 4

   得到的结果就会和前面的值不一样，这就自然而然地“切分”出了一个新的连续组。这个“差值”就成了我们用来

   GROUP BY

   的神奇标识符

   login_group_id

   。

可以说，窗口函数提供了“在不折叠行的情况下，对一组相关行进行计算”的能力，这正是我们处理序列问题所需要的。它允许我们“看到”当前行周围的行，从而进行基于位置或顺序的复杂逻辑判断，这是普通聚合函数望尘莫及的。对我来说，掌握

ROW_NUMBER()

配合日期或时间戳的差值运算，是解决很多序列分析问题的“万能钥匙”。

如何处理跨年或日期格式不一致的登录数据？

处理跨年或日期格式不一致的登录数据，其实是这类问题中一个很实际的细节，往往在真实场景里会遇到。

1. 日期格式标准化： 这是第一步，也是非常关键的一步。用户的

   login_time

   字段可能存储的是

   DATETIME

   、

   timestamp

   甚至

   VARCHAR

   字符串。为了确保我们只比较“天”，而不是具体的时分秒，我通常会用

   CAST(login_time AS DATE)

   或者

   DATE(login_time)

   （取决于数据库方言）来将其统一转换为日期类型。这样，

   2023-01-01 08:00:00

   和

   2023-01-01 23:59:59

   都会被标准化为

   2023-01-01

   。这避免了因为时间戳差异导致误判为非连续登录的情况。

2. 跨年处理的兼容性： 我们的核心逻辑

   DATE_SUB(login_day, INTERVAL rn DAY)

   （或者其他数据库的等效操作）是完全兼容跨年的。日期减去一个整数天数，无论是否跨年，数据库都会正确计算出结果。例如，

   2022-12-31

   减去一天是

   2022-12-30

   ，

   2023-01-01

   减去一天是

   2022-12-31

   。所以，只要日期类型处理得当，这个方法本身就能很好地应对跨年的情况，不需要额外的特殊处理。

3. 不同数据库的日期函数差异： 虽然原理一样，但不同数据库在日期函数和语法上确实有差异。

   • MySQL： 使用

     DATE_SUB(date, INTERVAL expr unit)

     或

     DATE_ADD

     。

   • PostgreSQL： 可以直接

     date - Integer

     或者

     date - (integer * INTERVAL '1 day')

     。

   • SQL Server：

     DATEADD(day, -integer, date)

     。

   • Oracle： 可以直接

     date - integer

     。 在编写代码时，需要根据实际使用的数据库进行调整。我个人在写通用SQL时，会尽量选择一些跨数据库兼容性较好的写法，或者直接在注释中说明不同数据库的等效写法，以防万一。

4. 性能考量： 对于非常大的数据集，特别是历史登录数据，为

   user_id

   和

   login_time

   字段建立复合索引

   (user_id, login_time)

   是非常有益的。这能显著加速

   PARTITION BY user_id ORDER BY login_day

   这一步，因为窗口函数需要对数据进行排序。如果数据量巨大，并且只需要查询最近一段时间的登录，那么在

   UserDailyLogins

   CTE 中提前添加

   WHERE login_time >= 'yyYY-MM-DD'

   这样的过滤条件，能有效减少处理的数据量，提升查询效率。