经典sql题（一）求连续登录不少于三天用户

示例数据

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

id	date
1	2023-10-01 08:00:00
1	2023-10-01 09:00:00
1	2023-10-02 10:00:00
1	2023-10-03 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

第一步：去重并提取日期

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
2	2023-10-01
2	2023-10-02
2	2023-10-03
2	2023-10-04
3	2023-10-01

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

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
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

第三步：统计连续的登录天数

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  -- 保持一致，使用 'table1'
) AS table2  -- 使用 'table2' 作为新的别名
GROUP BY 
    id, date2
HAVING 
    COUNT(*) >= 3;

结果（最终结果）：

id	date2	day_cnt
1	2023-09-30	3
2	2023-09-30	4

完整步骤解析

1. 去重提取日期：

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

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

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

• 步骤：

  • 使用 ROW_NUMBER() 函数，按照日期顺序为每个用户的登录日期分配一个序号。

  • 例如，对于用户 ID = 1，假设他们的日期是 2023-10-01、2023-10-02 和 2023-10-03，ROW_NUMBER() 将为它们生成序号 1、2 和 3。

  • date2 是通过将每个日期减去它的序号得到的。这个操作的目的是为了检测连续的日期。

  • 例如：

    • 对于 2023-10-01，序号是 1，计算为 2023-10-01 - 1，得到 2023-09-30。
    • 对于 2023-10-02，序号是 2，计算为 2023-10-02 - 2，得到 2023-09-30。
    • 对于 2023-10-03，序号是 3，计算为 2023-10-03 - 3，得到 2023-09-30。

通过这样的计算，我们可以将连续的日期归类到同一个组中。因为给定的 date2 值会相同（如上例中都是 2023-09-30），这使得后续的统计操作（如计算连续登录天数）变得简单。具体来说：

• 连续性检测：当两个日期的 date2 相同，说明它们是连续的。因此，进行统计时可以通过分组 date2 来确定每个用户连续登录的天数。

以用户 ID = 1 为例：

id	date1	ROW_NUMBER	date2
1	2023-10-01	1	2023-09-30
1	2023-10-02	2	2023-09-30
1	2023-10-03	3	2023-09-30

3. 统计连续的登录天数：
   • 查询：根据 date2 统计每个用户的连续登录天数，筛选出连续登录天数大于等于3天的用户。
   • 最终结果：显示符合条件的用户 ID 和连续登录天数。