题解 | 最长连续登录天数

SELECT user_id,max(consec_days) AS max_consec_days
FROM (
    SELECT b.user_id,
    b.first_date,
    COUNT(b.first_date) AS consec_days
    FROM (
        SELECT a.user_id,
        date_sub(fdate,interval a.rn day) AS first_date
        FROM(
            SELECT user_id,
            fdate,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS rn
            FROM tb_dau
        ) AS a
    ) AS b
    GROUP BY b.user_id,b.first_date
) AS c
GROUP BY user_id;

按照连续日期拥有同一个初始日期的逻辑进行分类，通过当前日期减去当前日期的序号（连续）来得到初始日期。

该查询通过date_sub(fdate, interval rn day)将连续日期映射到相同的基准日期，从而将连续日期分组。这是识别连续值的经典方法，适用于任何需要检测连续序列的场景。