为了找到连续登录超过 3 天的用户,我们可以使用 SQL 窗口函数和递归查询来实现。假设有一个 user_logins
表,包含以下字段:
user_id
(用户ID)login_date
(登录日期)
假设 login_date
是 DATE
类型,下面是实现该需求的 SQL 查询:
1. 数据库表结构与样本数据
CREATE TABLE user_logins (user_id INT,login_date DATE
);INSERT INTO user_logins (user_id, login_date) VALUES
(1, '2023-07-01'), (1, '2023-07-02'), (1, '2023-07-03'),(1, '2023-07-05'),
(2, '2023-07-01'), (2, '2023-07-03'), (2, '2023-07-04'),(2, '2023-07-05');
2. SQL 查询
WITH login_streaks AS (SELECTuser_id,login_date,login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS streak_idFROMuser_logins
),
streak_groups AS (SELECTuser_id,COUNT(*) AS streak_lengthFROMlogin_streaksGROUP BYuser_id, streak_id
)
SELECT DISTINCTuser_id
FROMstreak_groups
WHEREstreak_length > 3;
解析:
这段SQL代码通过使用公用表表达式(CTE)计算用户的连续登录天数,并筛选出连续登录天数大于等于3天的用户。我们将逐步解析这段SQL代码的每个部分。
1. WITH子句和CTE
SQL代码使用了两个CTE:login_streaks
和 streak_groups
。
CTE 1:login_streaks
WITH login_streaks AS (SELECTuser_id,login_date,login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS streak_idFROMuser_logins
),
目的:计算每个用户的登录日期,并为每个用户生成一个“连续登录标识符”(streak_id
)。
关键点:
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)
:为每个用户按login_date
排序的每一行生成一个唯一的行号。login_date - INTERVAL ROW_NUMBER() DAY
:通过减去行号的天数来生成一个“连续登录标识符”。如果用户在连续的日期登录,减去行号后得到的结果将是相同的。
例如:
- 假设用户在
2024-07-01
和2024-07-02
登录,那么:2024-07-01 - INTERVAL 1 DAY
=2024-06-30
2024-07-02 - INTERVAL 2 DAY
=2024-06-30
- 这两个记录的“连续登录标识符”将是相同的
2024-06-30
,表明它们是连续登录的。
CTE 2:streak_groups
streak_groups AS (SELECTuser_id,COUNT(*) AS streak_lengthFROMlogin_streaksGROUP BYuser_id, streak_id
)
目的:计算每个用户的每个“连续登录标识符”对应的连续登录天数。
关键点:
GROUP BY user_id, streak_id
:按用户和“连续登录标识符”分组。COUNT(*) AS streak_length
:计算每个分组的记录数量,即连续登录的天数。
最终查询
SELECTuser_id
FROMstreak_groups
WHEREstreak_length >= 3;
目的:筛选出连续登录天数大于等于3天的用户。
关键点:
WHERE streak_length >= 3
:只选择连续登录天数(streak_length
)大于等于3的用户。
完整解析
-
login_streaks
CTE:- 为每个用户生成一个包含
user_id
、login_date
和streak_id
的临时表。 streak_id
标识用户的连续登录,具有相同streak_id
的记录表示用户的连续登录序列。
- 为每个用户生成一个包含
-
streak_groups
CTE:- 计算每个用户的每个
streak_id
对应的连续登录天数。 - 结果表包含
user_id
和streak_length
。
- 计算每个用户的每个
-
最终查询:
- 从
streak_groups
中筛选出streak_length
大于等于3的用户。
- 从
示例数据和结果
假设user_logins
表包含以下数据:
user_id | login_date |
---|---|
1 | 2024-07-01 |
1 | 2024-07-02 |
1 | 2024-07-03 |
1 | 2024-07-05 |
2 | 2024-07-01 |
2 | 2024-07-03 |
2 | 2024-07-04 |
2 | 2024-07-05 |
Step 1: login_streaks
CTE结果:
user_id | login_date | streak_id |
---|---|---|
1 | 2024-07-01 | 2024-06-30 |
1 | 2024-07-02 | 2024-06-30 |
1 | 2024-07-03 | 2024-06-30 |
1 | 2024-07-05 | 2024-07-02 |
2 | 2024-07-01 | 2024-06-30 |
2 | 2024-07-03 | 2024-07-01 |
2 | 2024-07-04 | 2024-07-01 |
2 | 2024-07-05 | 2024-07-01 |
Step 2: streak_groups
CTE结果:
user_id | streak_length |
---|---|
1 | 3 |
1 | 1 |
2 | 1 |
2 | 3 |
Step 3: 最终查询结果:
user_id |
---|
1 |
2 |
总结
这段SQL代码通过使用两个CTE,先计算每个用户的连续登录天数,然后筛选出连续登录天数大于等于3天的用户,非常适合于分析用户的活跃度和粘性。