SQL如何求每个用户最长连续登录_SQL分组求最长连续登录-SQL-PHP中文网

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

爱谁谁

发布： 2025-09-12 19:49:01

原创

604人浏览过

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

sql如何求每个用户最长连续登录_sql分组求最长连续登录

想在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()

登录后复制

扮演了至关重要的角色。

它的核心作用在于：

如知AI笔记

如知笔记——支持markdown的在线笔记，支持ai智能写作、AI搜索，支持DeepseekR1满血大模型

查看详情

赋予序列号：
```
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day)
```
登录后复制
这部分，它的
```
PARTITION BY user_id
```
登录后复制
确保了我们是针对每个用户独立编号，不会把不同用户的登录混淆。而
```
ORDER BY login_day
```
登录后复制
则保证了编号是按照登录日期递增的。这样，每个用户每次登录都有了一个独一无二的、且按时间顺序排列的序号（
```
rn
```
登录后复制
）。
创建“连续组”标识： 真正的魔法在于
```
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()

登录后复制

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

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

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

日期格式标准化： 这是第一步，也是非常关键的一步。用户的
```
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
```
登录后复制
。这避免了因为时间戳差异导致误判为非连续登录的情况。
跨年处理的兼容性： 我们的核心逻辑
```
DATE_SUB(login_day, INTERVAL rn DAY)
```
登录后复制
（或者其他数据库的等效操作）是完全兼容跨年的。日期减去一个整数天数，无论是否跨年，数据库都会正确计算出结果。例如，
```
2022-12-31
```
登录后复制
减去一天是
```
2022-12-30
```
登录后复制
，
```
2023-01-01
```
登录后复制
减去一天是
```
2022-12-31
```
登录后复制
。所以，只要日期类型处理得当，这个方法本身就能很好地应对跨年的情况，不需要额外的特殊处理。
不同数据库的日期函数差异： 虽然原理一样，但不同数据库在日期函数和语法上确实有差异。
- 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时，会尽量选择一些跨数据库兼容性较好的写法，或者直接在注释中说明不同数据库的等效写法，以防万一。
性能考量： 对于非常大的数据集，特别是历史登录数据，为
```
user_id
```
登录后复制
和
```
login_time
```
登录后复制
字段建立复合索引
```
(user_id, login_time)
```
登录后复制
是非常有益的。这能显著加速
```
PARTITION BY user_id ORDER BY login_day
```
登录后复制
这一步，因为窗口函数需要对数据进行排序。如果数据量巨大，并且只需要查询最近一段时间的登录，那么在
```
UserDailyLogins
```
登录后复制
CTE 中提前添加
```
WHERE login_time >= 'YYYY-MM-DD'
```
登录后复制
这样的过滤条件，能有效减少处理的数据量，提升查询效率。