如何提取连续5天登录streak起始日期并统计有效streak数？

需求说明 #

在指定日期范围（如2023-01-01至2023-01-31）内：

• 提取连续5天登录记录的起始日期
• 若连续登录10天，计为2个streak
• 最终统计每个id对应的有效streak数量

示例表 #

期望输出 #

连续登录起始日期 #

streak统计结果 #

已尝试方案及问题 #

曾使用带变量计数器的SQL，但性能低下，且过滤无后续连续日期的记录时会丢失streak最后一天。尝试的代码如下：

SELECT 
    DISTINCT
    a.id AS id,
    a.date as DATE
FROM 
    logins a,
    (SELECT @counter := 0, @streak := 0) counter,
    (
        SELECT 
            DISTINCT(b.date) as date
        FROM 
            logins b
        WHERE
            b.id = 1 AND
            b.date >= '2023-01-01' AND
            b.date  <= '2023-01-31'
    ) b
WHERE 
    a.id = 1 AND
    a.date >= '2023-01-01' AND
    a.date <= '2023-01-31' AND
    DATE_ADD(a.date, INTERVAL 1 DAY) = b.date AND
    b.date BETWEEN a.date AND DATE_ADD(a.date, INTERVAL 5 DAY)
GROUP BY
    id, date

高效解决方案 #

使用窗口函数实现，无需变量，性能更优，逻辑清晰。

完整可执行SQL #

WITH RECURSIVE nums(n) AS (
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100 -- 可根据业务最大连续登录天数调整上限
),
login_groups AS (
    SELECT 
        id,
        date,
        -- 通过日期与行号的差值标记连续登录组：同一连续组的差值固定
        DATE_SUB(date, INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) DAY) AS group_id
    FROM logins
    WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
),
group_details AS (
    SELECT 
        id,
        MIN(date) AS start_date,
        COUNT(*) AS consecutive_days
    FROM login_groups
    GROUP BY id, group_id
),
streak_starts AS (
    SELECT 
        id,
        DATE_ADD(start_date, INTERVAL (5 * n) DAY) AS streak_start
    FROM group_details
    JOIN nums
    ON 5 * (n + 1) <= consecutive_days
)
-- 切换注释可获取不同结果：
-- 1. 提取所有streak起始日期
-- SELECT id, streak_start AS date FROM streak_starts ORDER BY id, streak_start;
-- 2. 统计每个id的总streak数
SELECT id, COUNT(*) AS streak FROM streak_starts GROUP BY id;

方案逻辑解析 #

1. 生成数字序列：用递归CTE生成0到指定上限的数字，用于拆分连续登录段
2. 标记连续登录组：通过窗口函数计算每个登录日期与行号的差值，同一连续登录段的差值一致
3. 计算连续段详情：统计每个连续登录段的起始日期和总天数
4. 拆分有效streak：按每5天为一个单元，从连续段起始日期开始生成所有有效streak的起始日期
5. 输出结果：可选择输出所有起始日期，或统计每个id的streak总数

方案优势 #

• 性能高效：窗口函数是数据库原生优化的操作，比变量计数器更适配大数据量场景
• 逻辑清晰：通过分组标记连续段，再拆分streak，不易出现边界错误
• 扩展性强：只需修改5这个参数，即可适配不同的连续天数要求

内容的提问来源于stack exchange，提问作者Coding