SQL中如何处理跨年连续登录_SQL处理跨年日期连续计算

26次阅读

核心是利用日期差与行号差识别连续登录周期。通过去重、分配行号并计算login_date与行号的差值作为分组标识，可准确识别跨年连续登录，再按该标识分组统计起止时间和长度，从而解决跨年及重复登录等问题。

处理SQL中的跨年连续登录问题，核心在于巧妙地利用日期函数和窗口函数来识别日期序列中的“连续性”，即便这些日期跨越了不同的年份。通过将每个登录日期与一个递增的序列号进行关联并求差，我们可以为每个连续的登录周期生成一个唯一的标识符，从而轻松地进行分组和计算。

解决方案

要解决跨年连续登录的计算，我们通常需要一个包含用户ID和登录日期的表。假设我们有一个

user_logins

表，结构如下：

CREATE TABLE user_logins (     user_id INT,     login_date DATE );  INSERT INTO user_logins (user_id, login_date) VALUES (1, '2023-12-29'), (1, '2023-12-30'), (1, '2023-12-31'), (1, '2024-01-01'), (1, '2024-01-02'), (1, '2024-01-05'), -- Gap (1, '2024-01-06'), (2, '2024-01-01'), (2, '2024-01-02'), (2, '2024-01-04'); -- Gap

处理这类问题，我个人比较偏爱使用“日期差”结合“行号”的方法。这种方法非常优雅，它能够将所有连续的日期归并到一个逻辑组中，无论它们是否跨年。

我们首先需要为每个用户的登录日期去重，因为一天内多次登录通常只算作一次。然后，为每个用户的每个登录日期分配一个基于日期排序的行号。关键的一步是，从登录日期中减去这个行号（或者说，减去一个代表天数的间隔）。如果日期是连续的，那么这个差值会保持不变，从而形成一个“连续组”的标识。

WITH UserDailyLogins AS (     -- 确保每个用户每天只计算一次登录     SELECT DISTINCT         user_id,         login_date     FROM         user_logins ), GroupedLogins AS (     SELECT         user_id,         login_date,         -- 为每个用户的登录日期按时间顺序分配一个序列号         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,         -- 关键步骤：计算一个“连续组”标识符         -- 如果日期连续，login_date - rn 的结果会保持一致         DATE_SUB(login_date, INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) DAY) AS login_group_id         -- 对于PostgreSQL/Oracle，可以使用 login_date - (ROW_NUMBER() OVER (...) * INTERVAL '1 day')         -- 对于SQL Server，可以使用 DATEADD(day, -1 * (ROW_NUMBER() OVER (...)), login_date)     FROM         UserDailyLogins ) -- 现在我们可以基于 login_group_id 来计算每个连续登录周期的长度 SELECT     user_id,     MIN(login_date) AS streak_start_date,     MAX(login_date) AS streak_end_date,     COUNT(login_date) AS streak_length FROM     GroupedLogins GROUP BY     user_id,     login_group_id HAVING     COUNT(login_date) > 1 -- 只显示连续登录天数大于1的记录，如果需要所有，可以移除此行 ORDER BY     user_id,     streak_start_date;