MySQL中连续登录统计怎么做_MySQL实现连续登录统计步骤

30次阅读

利用ROW_NUMBER()和日期差构造分组键，可高效统计MySQL中用户连续登录天数，该方法能正确处理跨月跨年情况，并支持计算最大、当前连续登录时长及用户行为分析。

在MySQL中统计用户的连续登录天数，核心的解决思路是巧妙地利用窗口函数（尤其是

ROW_NUMBER()

）和日期函数，通过构造一个“分组键”来识别并聚合连续的登录日期。这种方法能够高效地将一系列连续的日期事件归类，从而计算出每个连续登录区间的长度。

解决方案

要实现MySQL中的连续登录统计，我们需要一张记录用户登录事件的表。假设我们有这样一张表：

CREATE TABLE user_logins (     id INT AUTO_INCREMENT PRIMARY KEY,     user_id INT NOT NULL,     login_time DATETIME NOT NULL,     INDEX (user_id, login_time) );  -- 插入一些示例数据 INSERT INTO user_logins (user_id, login_time) VALUES (1, '2023-01-01 08:00:00'), (1, '2023-01-02 09:15:00'), (1, '2023-01-03 10:30:00'), (1, '2023-01-05 11:00:00'), -- 中断 (1, '2023-01-06 12:00:00'), (1, '2023-01-07 13:00:00'), (2, '2023-01-01 14:00:00'), (2, '2023-01-03 15:00:00'), -- 中断 (2, '2023-01-04 16:00:00'), (2, '2023-01-05 17:00:00');

实现连续登录统计的步骤如下：

为每个用户的登录记录按日期排序并分配行号。 这一步利用
```
ROW_NUMBER()
```
窗口函数，为每个
```
user_id
```
的登录日期（去重后）分配一个递增的序列号。这里我们只关心登录日期，所以会先对
```
login_time
```
进行
```
DATE()
```
处理。
构造一个“分组键”（

grouping_key

）。这是整个解决方案中最巧妙的一环。我们用每个用户的登录日期减去其对应的行号（或者行号减1对应的天数）。如果日期是连续的，那么这个差值将保持不变。例如：
- 2023-01-01
  (行号1):
  2023-01-01
  –
```
INTERVAL (1-1) DAY
```
  =
  2023-01-01
- ```
2023-01-02
```
  (行号2):
```
2023-01-02
```
  –
```
INTERVAL (2-1) DAY
```
  =
  2023-01-01
- ```
2023-01-03
```
  (行号3):
```
2023-01-03
```
  –
```
INTERVAL (3-1) DAY
```
  =
  2023-01-01
  可以看到，连续的登录日期经过这个计算后，会得到相同的“分组键”。一旦登录中断，这个键值就会发生变化，从而形成新的分组。
根据用户ID和“分组键”进行分组，计算每个分组的长度。 每个分组的长度即为一个连续登录区间的持续天数。我们还可以找到每个分组的起始和结束日期。
从结果中提取所需信息。 可以找出每个用户的最大连续登录天数，或者所有用户的最长连续登录天数，甚至是当前正在进行的连续登录天数。

完整的SQL查询如下：

WITH UserDailyLogins AS (     -- 提取每个用户每天的唯一登录记录     SELECT         user_id,         DATE(login_time) AS login_date     FROM         user_logins     GROUP BY         user_id, DATE(login_time) ), RankedLogins AS (     -- 为每个用户的登录日期分配一个递增的序列号     SELECT         user_id,         login_date,         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn     FROM         UserDailyLogins ), GroupedStreaks AS (     -- 构造分组键：login_date 减去 (rn-1) 天，连续登录的日期会得到相同的 grouping_key     SELECT         user_id,         login_date,         DATE_SUB(login_date, INTERVAL (rn - 1) DAY) AS grouping_key     FROM         RankedLogins ) -- 最终统计：计算每个连续登录区间的开始、结束日期和长度 SELECT     user_id,     MIN(login_date) AS streak_start_date,     MAX(login_date) AS streak_end_date,     COUNT(login_date) AS streak_length FROM     GroupedStreaks GROUP BY     user_id, grouping_key ORDER BY     user_id, streak_start_date;

如果你想找出每个用户的最大连续登录天数，可以在上述查询的基础上再加一层：

WITH UserDailyLogins AS (     SELECT user_id, DATE(login_time) AS login_date     FROM user_logins     GROUP BY user_id, DATE(login_time) ), RankedLogins AS (     SELECT user_id, login_date,            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn     FROM UserDailyLogins ), GroupedStreaks AS (     SELECT user_id, login_date,            DATE_SUB(login_date, INTERVAL (rn - 1) DAY) AS grouping_key     FROM RankedLogins ), CalculatedStreaks AS (     SELECT         user_id,         COUNT(login_date) AS streak_length     FROM         GroupedStreaks     GROUP BY         user_id, grouping_key ) SELECT     user_id,     MAX(streak_length) AS max_consecutive_login_days FROM     CalculatedStreaks GROUP BY     user_id ORDER BY     user_id;

为什么传统的日期比较方式难以实现连续登录统计？

我刚开始接触这类问题时，也习惯性地想着用

LAG()

函数去比较前一天是不是登录了。比如，我们可以很方便地判断出某个用户今天是否是继昨天之后登录的：

SELECT     user_id,     login_date,     LAG(login_date, 1, NULL) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login_date,     DATEDIFF(login_date, LAG(login_date, 1, NULL) OVER (PARTITION BY user_id ORDER BY login_date)) AS diff_days FROM (     SELECT user_id, DATE(login_time) AS login_date     FROM user_logins     GROUP BY user_id, DATE(login_time) ) AS daily_logins;

这个查询能清晰地显示出每次登录与前一次登录之间的天数差异。如果

diff_days

是1，那确实是连续的。但问题来了：这只能判断相邻两天的关系。要形成一个“连续”的“段”——比如“用户A连续登录了三天”，并找出这个“三天”的起始和结束，

LAG()

函数本身并不直接提供分组能力。它无法自动识别出一段连续的序列，并在序列中断时自动“重置”计数。你可能需要在此基础上再进行复杂的嵌套查询或者使用变量来模拟状态机，这不仅代码复杂，而且在MySQL中，用户变量在窗口函数场景下的表现有时会比较难以预测和优化。

相比之下，

ROW_NUMBER()

结合日期减去行号的方式，其精妙之处在于它提供了一个稳定的、跨越中断的分组标识。一旦日期序列中断，即使行号继续递增，

login_date - INTERVAL (rn - 1) DAY

这个计算结果也会发生变化，从而自然地形成了新的分组。这种方法避免了对前一行状态的依赖，让逻辑变得异常清晰和健壮。这在我看来，是处理这类“序列中连续段”问题的标准范式之一。

Poe

Quora旗下的对话机器人聚合工具

289

查看详情

如何处理跨越月份或年份的连续登录？

关于跨越月份或年份的连续登录，你完全不需要担心。MySQL的日期函数，例如

DATE()

、

DATE_SUB()

、

DATEDIFF()

等，都是设计来正确处理日期和时间边界的。

在我们的解决方案中，

DATE(login_time)

会精确地提取出日期部分，无论

login_time

是

2023-12-31 23:59:59

还是

2024-01-01 00:00:01

，它们对应的

DATE()

结果分别是

2023-12-31

和

2024-01-01

。

同样，

DATE_SUB(login_date, INTERVAL (rn - 1) DAY)

这个操作，MySQL会智能地处理日期的减法。例如：

如果

login_date

是

2024-01-01

，

rn

是1，那么

DATE_SUB('2024-01-01', INTERVAL 0 DAY)

就是

2024-01-01

。

如果

login_date

是

2024-01-02

，

rn

是2，那么

DATE_SUB('2024-01-02', INTERVAL 1 DAY)

就是

2024-01-01

。

如果

login_date

是

2023-12-31

，

rn

是1，那么

DATE_SUB('2023-12-31', INTERVAL 0 DAY)

就是

2023-12-31

。

如果

login_date

是

2024-01-01

，

rn

是2（紧接在2023-12-31之后），那么

DATE_SUB('2024-01-01', INTERVAL 1 DAY)

就是

2023-12-31

。

你看，无论日期如何跨越月份甚至年份，只要它们在时间轴上是连续的，

DATE_SUB()

函数都会确保

grouping_key

保持一致。所以，这个方法在处理日期边界问题上是非常健壮和可靠的，不需要额外的特殊处理逻辑。

除了最大连续登录天数，我们还能从这些数据中挖掘出哪些有价值的信息？

说实话，单纯一个“最大连续登录天数”，它本身的商业价值可能有限。但当我们把这个计算方法当作一个“工具”，它能帮我们解锁更多维度的用户行为洞察，这些洞察往往比一个简单的数字更有意义。

当前连续登录天数： 用户当前的连续登录天数是一个非常重要的实时指标。它能直接反映用户的活跃度和粘性。我们可以找到每个用户最近一次登录所属的连续登录区间，并计算其长度。这对于设计即时奖励、推送个性化消息（“恭喜您已连续登录X天！”）非常有用。要获取这个，你只需在
```
CalculatedStreaks
```
的基础上，找到
```
MAX(streak_end_date)
```
对应的
```
streak_length
```
。
用户活跃度分层与奖励机制： 我们可以根据用户的最大连续登录天数，将用户划分为不同的活跃度层级。例如，连续登录超过7天的视为高活跃用户，连续登录超过30天的可能是核心用户。基于此，可以设计不同的激励策略，比如达到一定天数给予虚拟物品、优惠券等，以鼓励用户保持登录习惯。
用户流失预警： 如果一个用户之前有很长的连续登录记录，但最近的登录行为显示其连续登录中断了，这可能是一个潜在的流失信号。通过监控这些“中断”事件，我们可以及时介入，通过运营手段（如短信、App推送）尝试挽回用户。我们可以分析哪些用户在什么情况下更容易中断他们的连续登录。
用户行为模式分析： 通过观察大量用户的连续登录数据，可以发现某些普遍的登录模式。例如，是工作日登录多，还是周末登录多？是否存在某个特定的时间段，用户的连续登录更容易中断？这些数据可以为产品优化和运营活动提供数据支持。比如，如果发现周一的登录中断率高，可能需要考虑在周末提供更强的激励或提醒。
新功能或活动效果评估： 当产品上线新功能或推出新的运营活动时，我们可以观察用户连续登录天数的变化。如果新功能或活动能够有效提升用户的连续登录天数，那么说明其对用户粘性有积极作用。反之，则需要进行调整。