查询连续三天登录问题

解决问题：要求使用sql查询出连续三天登录的用户

1.建表：

CREATE TABLE `tmp` (
  `name` varchar(255) NOT NULL,
  `login_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.插入数据：

INSERT INTO `tmp`(`name`, `login_date`) VALUES ('a', '2021-09-02 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('a', '2021-09-03 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('a', '2021-09-04 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('a', '2021-09-05 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('a', '2021-09-06 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('b', '2021-09-01 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('b', '2021-09-03 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('b', '2021-09-04 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('b', '2021-09-06 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('c', '2021-09-05 00:00:00');
INSERT INTO `tmp`(`name`, `login_date`) VALUES ('c', '2021-09-06 00:00:00');

思路一：

　　先使用 ROW_NUMBER 函数求出同一个人登陆时间的排名，再使用 DATE_SUB 函数求出登陆时间减去排名（开始连续登陆时间），最后进行 GROUP BY 求出连续登录次数。

SELECT name, count(1) AS cnt
FROM 
    (SELECT a.name, 
                    a.login_date, 
                    a.ra,
                    date_sub(a.login_date, INTERVAL a.ra DAY) AS interval_days
    FROM (SELECT name, 
                             login_date, 
                             ROW_NUMBER() OVER(PARTITION BY name ORDER BY login_date) AS ra
            FROM tmp) a)b
GROUP BY name, interval_days
HAVING counT(1) >=  3;

思路二：

　　使用 LEAD() OVER() 函数获取排名2位后的登陆时间，再使用 DATEDIFF 函数求出后2位登陆时间和登陆时间相差2的名字，去重。

SELECT 
   DISTINCT a.name 
FROM (SELECT 
    name,
    login_date,
    LEAD(login_date,2) OVER(PARTITION BY name ORDER BY login_date) AS lag_2days 
    FROM tmp 
    ORDER BY name,login_date)a 
WHERE DATEDIFF(a.lag_2days,a.login_date) = 2;