经典sql题（二）求连续登录最多天数用户

示例数据

假设我们的 test 表有以下数据：

id	date
1	2023-10-01 08:00:00
1	2023-10-02 09:00:00
1	2023-10-03 10:00:00
1	2023-10-05 11:00:00
2	2023-10-01 10:00:00
2	2023-10-02 12:00:00
2	2023-10-03 14:00:00
2	2023-10-04 15:00:00
3	2023-10-01 16:00:00
3	2023-10-02 16:00:00
3	2023-10-03 16:00:00
3	2023-10-04 16:00:00

第一步：去重并提取日期

SELECT 
    id,
    SUBSTR(date, 1, 10) AS date1
FROM 
    test
GROUP BY 
    id, SUBSTR(date, 1, 10);

结果（表 table1）：

id	date1
1	2023-10-01
1	2023-10-02
1	2023-10-03
1	2023-10-05
2	2023-10-01
2	2023-10-02
2	2023-10-03
2	2023-10-04
3	2023-10-01
3	2023-10-02
3	2023-10-03
3	2023-10-04

第二步：为每个用户和日期生成序列号

SELECT 
    id,
    date1,
    DATE_ADD(date1, -ROW_NUMBER() OVER (PARTITION BY id ORDER BY date1)) AS date2
FROM (
    SELECT 
        id,
        SUBSTR(date, 1, 10) AS date1
    FROM 
        test
    GROUP BY 
        id, SUBSTR(date, 1, 10)
) AS table1;  -- 使用 'table1' 作为别名

结果（表 table2）：

id	date1	date2
1	2023-10-01	2023-10-01
1	2023-10-02	2023-10-01
1	2023-10-03	2023-10-01
1	2023-10-05	2023-10-04
2	2023-10-01	2023-10-01
2	2023-10-02	2023-10-01
2	2023-10-03	2023-10-01
2	2023-10-04	2023-10-01
3	2023-10-01	2023-10-01
3	2023-10-02	2023-10-01
3	2023-10-03	2023-10-01
3	2023-10-04	2023-10-01

第三步：统计每个用户的最大连续登录天数

SELECT 
    id, 
    MAX(day_cnt) AS max_day_cnt
FROM (
    SELECT 
        id,
        date2,
        COUNT(*) AS day_cnt
    FROM (
        SELECT 
            id,
            date1,
            DATE_ADD(date1, -ROW_NUMBER() OVER (PARTITION BY id ORDER BY date1)) AS date2
        FROM (
            SELECT 
                id,
                SUBSTR(date, 1, 10) AS date1
            FROM 
                test
            GROUP BY 
                id, SUBSTR(date, 1, 10)
        ) AS table1
    ) AS table2
    GROUP BY 
        id, date2
) AS final_table
GROUP BY id
ORDER BY max_day_cnt DESC
LIMIT 1;

结果（如果求的是用户天数而非用户则无需ORDER BY）：

id	max_day_cnt
3	4

完整步骤解析

1. 去重提取日期：

   • 查询：提取每个用户的唯一登录日期。
   • 结果表 table1：显示每个用户的唯一日期。

2. 为每个用户生成序列号：

   • 查询：为每个用户的日期生成序号，并计算 date2。
   • 结果表 table2：显示用户的日期和对应的 date2。

3. 统计用户最大连续登录天数：

   • 查询：根据 date2 统计每个用户的连续登录天数，并通过 MAX() 函数获取每个用户的最大连续登录天数。
   • 最终结果：显示连续登录天数最多的用户及其天数。