17370845950

答案是使用窗口函数结合去重和分组逻辑计算连续登录天数。首先通过DISTINCT去除同一天的重复登录，再用ROW_NUMBER()为每个用户的登录日期排序，然后利用DATE_SUB(login_date, INTERVAL rn DAY)生成分组键，将连续登录归为同一组，最后按组统计起始日、结束日及连续天数；若需筛选至少连续N天，可添加HAVING COUNT(DISTINCT login_date) >= N条件；性能优化建议包括建立(user_id, login_date)索引、使用合适数据类型及物化视图；此外也可用自连接方法替代窗口函数，适用于小数据量场景。

SQL计算连续登录并去重，核心在于识别连续的登录行为，并排除重复的登录记录。这通常涉及到窗口函数和一些巧妙的逻辑判断。

首先，要理解“连续”的定义，以及如何基于时间序列数据进行判断。其次，去重是指在计算连续登录天数时，同一用户在同一天多次登录只算一次。

计算连续登录并去重的方法，本质上是找到每个用户的登录记录，然后按照登录时间排序，最后判断哪些登录记录是连续的。

如何利用窗口函数计算连续登录天数？

窗口函数在这里扮演着关键角色。

ROW_NUMBER()

可以为每个用户的登录记录分配一个行号，而

LAG()

或

LEAD()

可以访问前一行或后一行的数据。

假设我们有一个名为

login_records

的表，包含

user_id

和

login_date

两列。

WITH RankedLogins AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM (SELECT DISTINCT user_id, login_date FROM login_records) AS DistinctLogins -- 去重
),
ConsecutiveLoginGroups AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS group_start_date
    FROM RankedLogins
)
SELECT
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(DISTINCT login_date) AS consecutive_days
FROM ConsecutiveLoginGroups
GROUP BY user_id, group_start_date
ORDER BY user_id, start_date;

这段SQL代码做了以下几件事：

1. 去重 (DistinctLogins): 子查询

   DistinctLogins

   使用

   SELECT DISTINCT

   确保每个用户每天只有一条登录记录。
2. 排序 (RankedLogins):

   ROW_NUMBER()

   函数为每个用户的登录日期分配一个序号

   rn

   ，按照登录日期升序排列。

   PARTITION BY user_id

   确保每个用户独立编号。
3. 分组 (ConsecutiveLoginGroups): 关键一步！将登录日期减去其序号

   rn

   ，得到一个

   group_start_date

   。连续的登录日期会得到相同的

   group_start_date

   ，从而将它们划分到同一组。 例如，如果用户在2025-11-01, 2025-11-02, 2025-11-03登录，他们的

   group_start_date

   都是 2025-10-31。
4. 统计: 最后，按

   user_id

   和

   group_start_date

   分组，统计每个连续登录组的起始日期、结束日期和天数。

   COUNT(DISTINCT login_date)

   确保即使因为某些原因同一组内有重复日期，天数也不会被错误计算。

这种方法的巧妙之处在于，通过

DATE_SUB(login_date, INTERVAL rn DAY)

将连续的日期转换成相同的分组依据，从而简化了连续登录的判断。

如何处理更复杂的连续登录场景？

如果需要计算用户至少连续登录N天的情况，可以在上述查询的基础上添加一个

HAVING

子句。

WITH RankedLogins AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM (SELECT DISTINCT user_id, login_date FROM login_records) AS DistinctLogins
),
ConsecutiveLoginGroups AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS group_start_date
    FROM RankedLogins
)
SELECT
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(DISTINCT login_date) AS consecutive_days
FROM ConsecutiveLoginGroups
GROUP BY user_id, group_start_date
HAVING COUNT(DISTINCT login_date) >= N  -- 至少连续登录N天
ORDER BY user_id, start_date;

将

N

替换为你需要的最小连续登录天数。

这种SQL语句在性能上有什么需要注意的？

对于大型数据集，窗口函数可能会比较消耗资源。优化查询性能可以从以下几个方面入手：

• 索引: 确保

  user_id

  和

  login_date

  列上有索引。特别是组合索引

  (user_id, login_date)

  可以显著提高查询速度。
• 数据类型: 使用合适的数据类型。例如，如果

  login_date

  列存储的是日期和时间，但只需要日期部分，可以考虑将其转换为

  DATE

  类型，减少数据量。
• 物化视图: 如果查询频繁执行，可以考虑创建物化视图，预先计算结果并存储起来，从而避免每次都进行全表扫描。

除了窗口函数，还有其他方法计算连续登录吗？

当然，虽然窗口函数很强大，但并不是唯一的选择。可以使用自连接来实现类似的功能。

SELECT
    l1.user_id,
    l1.login_date,
    COUNT(DISTINCT l2.login_date) AS consecutive_days
FROM (SELECT DISTINCT user_id, login_date FROM login_records) l1
LEFT JOIN (SELECT DISTINCT user_id, login_date FROM login_records) l2
ON l1.user_id = l2.user_id AND l2.login_date <= l1.login_date AND l2.login_date >= DATE_SUB(l1.login_date, INTERVAL 6 DAY) -- 假设要计算连续7天登录
GROUP BY l1.user_id, l1.login_date
HAVING COUNT(DISTINCT l2.login_date) = 7
ORDER BY l1.user_id, l1.login_date;

这种方法通过自连接找到每个用户在指定日期范围内（这里假设是7天）的登录记录，然后统计登录天数。 这种方法在某些情况下可能比窗口函数更有效率，尤其是在数据量不是特别大的时候。但需要根据实际情况进行测试和比较。