彩票网站建设需要什么,聊城网站建设首选天成网络,个人网页的代码,建设行业年度峰会网站为了找到连续登录超过 3 天的用户#xff0c;我们可以使用 SQL 窗口函数和递归查询来实现。假设有一个 user_logins 表#xff0c;包含以下字段#xff1a;
user_id#xff08;用户ID#xff09;login_date#xff08;登录日期#xff09;
假设 login_date 是 DATE 类…为了找到连续登录超过 3 天的用户我们可以使用 SQL 窗口函数和递归查询来实现。假设有一个 user_logins 表包含以下字段
user_id用户IDlogin_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代码使用了两个CTElogin_streaks 和 streak_groups。
CTE 1login_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-302024-07-02 - INTERVAL 2 DAY 2024-06-30这两个记录的“连续登录标识符”将是相同的 2024-06-30表明它们是连续登录的。
CTE 2streak_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_idlogin_date12024-07-0112024-07-0212024-07-0312024-07-0522024-07-0122024-07-0322024-07-042 2024-07-05
Step 1: login_streaks CTE结果
user_idlogin_datestreak_id12024-07-012024-06-3012024-07-022024-06-3012024-07-032024-06-3012024-07-052024-07-0222024-07-012024-06-3022024-07-032024-07-0122024-07-042024-07-0122024-07-052024-07-01
Step 2: streak_groups CTE结果
user_idstreak_length13112123
Step 3: 最终查询结果
user_id12
总结
这段SQL代码通过使用两个CTE先计算每个用户的连续登录天数然后筛选出连续登录天数大于等于3天的用户非常适合于分析用户的活跃度和粘性。
