一张用户表，uer_id，signin_date，大概是这么几项，查找连续三天登录的用户。

比如说，1,2两天登录不是连续三天，456登录为连续三天登录，5678也为连续三天登录。

• 实验环境信息
  
  

一、测试数据构建

-- 创建用户登录表
CREATE TABLE user_logins (user_id INT,signin_date DATE,PRIMARY KEY (user_id, signin_date)
);-- 插入测试数据
INSERT INTO user_logins (user_id, signin_date) VALUES
(1, '2023-01-01'),
(1, '2023-01-02'), -- 用户1：不连续三天
(2, '2023-01-04'),
(2, '2023-01-05'),
(2, '2023-01-06'), -- 用户2：连续三天 (4,5,6)
(3, '2023-01-05'),
(3, '2023-01-06'),
(3, '2023-01-07'),
(3, '2023-01-08'), -- 用户3：连续四天 (5,6,7,8)
(4, '2023-01-01'),
(4, '2023-01-02'),
(4, '2023-01-04'); -- 用户4：不连续 (1,2,4)    

二、自连接方案

• 三次连接同一表：
  • t1：第一天登录
  • t2：与第一天间隔 1 天的登录
  • t3：与第一天间隔 2 天的登录
• 关键条件：
  • t1.user_id = t2.user_id = t3.user_id：确保是同一用户
  • DATEDIFF(t2.signin_date, t1.signin_date) = 1：第二天比第一天大 1 天
  • DATEDIFF(t3.signin_date, t1.signin_date) = 2：第三天比第一天大 2 天
• 结果：
  • 仅当存在连续三天登录记录时，三个表才能匹配成功
  • DISTINCT用于去重，确保每个用户只返回一次

  -- 自连接解决方案（适用于不支持窗口函数的MySQL 5.x）SELECT DISTINCT t1.user_id
  FROM user_logins t1
  JOIN user_logins t2 ON t1.user_id = t2.user_id AND DATEDIFF(t2.signin_date, t1.signin_date) = 1JOIN user_logins t3 ON t1.user_id = t3.user_id AND DATEDIFF(t3.signin_date, t1.signin_date) = 2;    
  

三、窗口函数方案

• 核心逻辑是：
  • 按用户分组并排序：使用ROW_NUMBER为每个用户的登录日期生成连续的序号
  • 计算日期差值：用登录日期减去序号，如果是连续的日期，这个差值会相同
  • 分组统计：按用户和差值分组，统计每组的记录数，大于等于 3 的即为连续三天登录的用户

  WITH ranked_logins AS (SELECT user_id,signin_date,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY signin_date) AS rnFROM user_logins
  ),grouped_logins AS (SELECT user_id,signin_date,rn,DATE_SUB(signin_date, INTERVAL rn DAY) AS grpFROM ranked_logins
  )SELECT DISTINCT user_id
  FROM grouped_logins
  GROUP BY user_id, grp
  HAVING COUNT(*) >= 3;