题解 | 最长连续登录天数

WITH t1 AS(
    SELECT user_id, fdate, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS rn
    FROM tb_dau
),
t2 AS(
    SELECT user_id, fdate, DATE_SUB(fdate, INTERVAL rn DAY) AS grp
    FROM t1
),
t3 AS(
    SELECT
        user_id, grp, COUNT(*) AS log_days
    FROM t2
    GROUP BY user_id, grp
)
SELECT
    user_id,
    MAX(log_days) AS max_consec_days
FROM t3
GROUP BY user_id;

思路：

• 生成连续登录的分组标识：
• 使用 ROW_NUMBER() 窗口函数为每个用户的登录记录按日期排序。
• 将每个登录日期减去行号（fdate – rn = 本次连续登录的第一个登录日），如果登录是连续的，这个登录日将保持不变。
• 将这个差值作为分组标识（grp）。
• 分组统计连续登录天数：
• 按用户 ID 和分组标识（grp）分组，统计每个分组的登录天数。
• 使用 COUNT() 函数计算每个分组的天数，即每个连续登录区间的天数。
• 计算最长连续登录天数：
• 对每个用户，取其所有连续登录区间的最大值。

语法：

• WITH t1 AS 的作用是定义一个名为 t1 的临时表，用于存储中间结果。它允许你将一个查询的结果定义为一个临时的表，然后在主查询中引用它。

• DATE_SUB() 是一个日期函数，用于从指定的日期中减去指定的时间间隔。它与 DATE_ADD() 函数相对，用于执行日期的减法操作。