经典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_SUB(date1, INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY date1) DAY) AS date2
FROM (SELECT id,SUBSTR(date, 1, 10) AS date1FROM testGROUP 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_SUB(date1, INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY date1) DAY) AS date2FROM (SELECT id,SUBSTR(date, 1, 10) AS date1FROM testGROUP 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 |
完整步骤解析
-
去重提取日期：
- 查询：提取每个用户的唯一登录日期。
- 结果表
table1：显示每个用户的唯一日期。
-
为每个用户生成序列号：
- 查询：为每个用户的日期生成序号，并计算
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 |
- 统计连续的登录天数：
- 查询：根据
date2统计每个用户的连续登录天数，筛选出连续登录天数大于等于3天的用户。 - 最终结果：显示符合条件的用户 ID 和连续登录天数。
- 查询：根据