题解 | #最长连续登录天数#

这道题目要求我们计算2023年1月1日至2023年1月31日期间，每个用户的最长连续登录天数。下面是这个SQL查询的思路和实现步骤。

1. 确定总体问题

我们需要计算每个用户在给定日期范围内的最长连续登录天数。连续登录天数的计算需要识别出连续的日期序列。

2. 分析关键问题

• 排序和编号：首先，我们需要对每个用户的登录日期进行排序，并为每个日期分配一个序号。
• 识别连续序列：通过计算每个日期减去其序号的结果，我们可以将连续的日期分组。
• 计算连续天数：对每个分组计算连续天数，并找出最长的连续天数。

3. 解决每个关键问题的代码及讲解

步骤1：排序和编号

我们使用ROW_NUMBER()窗口函数为每个用户的登录日期分配一个序号：

WITH ordered_logins AS (
    SELECT 
        user_id,
        fdate,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS rn
    FROM 
        tb_dau
    WHERE 
        fdate BETWEEN '2023-01-01' AND '2023-01-31'
)

• PARTITION BY user_id：按用户分区。
• ORDER BY fdate：按日期排序。

步骤2：识别连续序列

通过计算每个日期减去其序号的结果，我们可以将连续的日期分组：

grouped_logins AS (
    SELECT 
        user_id,
        fdate,
        rn,
        DATE_SUB(fdate, INTERVAL rn DAY) AS grp
    FROM 
        ordered_logins
)

• DATE_SUB(fdate, INTERVAL rn DAY)：通过把fdate减去rn天，连续的日期将具有相同的结果，从而可以分组。

DATE_SUB处理前后样例如下

步骤3：计算连续天数

对每个分组计算连续天数，并找出最长的连续天数：

SELECT 
    user_id,
    MAX(consec_days) AS max_consec_days
FROM (
    SELECT 
        user_id,
        grp,
        COUNT(*) AS consec_days
    FROM 
        grouped_logins
    GROUP BY 
        user_id, grp
) AS consecutive
GROUP BY 
    user_id;

• COUNT(*) AS consec_days：计算每个分组的数量（具有相同天数的数量），即可得到连续天数。
• MAX(consec_days) AS max_consec_days：找出每个用户的最长连续天数。

完整代码

WITH ordered_logins AS (
    SELECT 
        user_id,
        fdate,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS rn
    FROM 
        tb_dau
    WHERE 
        fdate BETWEEN '2023-01-01' AND '2023-01-31'
),
grouped_logins AS (
    SELECT 
        user_id,
        fdate,
        rn,
        DATE_SUB(fdate, INTERVAL rn DAY) AS grp
    FROM 
        ordered_logins
)
SELECT 
    user_id,
    MAX(consec_days) AS max_consec_days
FROM (
    SELECT 
        user_id,
        grp,
        COUNT(*) AS consec_days
    FROM 
        grouped_logins
    GROUP BY 
        user_id, grp
) AS consecutive
GROUP BY 
    user_id;