SQL助你面大厂（连续N天登录）

       在腾讯、网易或者一些游戏类大厂中，他们经常关注的就是用户上线人数以及天数，那么给我们一个数据库，我们怎么样才能快速的查询那个用户的连续N天登录？

那我们用案例来说明，再多的语言在现实面前总是那么苍白无力：

---

       老渣和小渣都是情场高手，都是时间管理大师，自从上次约会时间过后，两人更是闹的不可开交，现在每天都在小美这里进行约会，小美有一个癖好，每次喜欢和约会的人登录约会时间信息，其实小美也是海王，优秀的猎手总是以猎物的身份出现，现在小美想查找小渣、老渣谁约会了连续3天？你可以帮小美解决这个问题么？

 首先看图表，如果我们人为操作的话，应当该怎么去执行：

我们用代码来模拟这个场景：

先进行创建表：

CREATE TABLE login(
DATE DATE,
NAME VARCHAR(20)
)
INSERT INTO login VALUES('2023-08-19','老渣'),('2023-08-20','老渣'),('2023-08-21','老渣'),
('2023-08-18','小渣'),('2023-08-19','小渣')
DROP TABLE login

先来第一步进行查询：

WITH t1 AS(
SELECT DISTINCT NAME,DATE d FROM login
)
SELECT * FROM t1

 根据老渣、小渣分组后对各自的时间进行了一个排序：

WITH t1 AS(
   SELECT DISTINCT NAME,DATE d FROM login
),
t2 AS(
   SELECT *,
     ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY d) AS rn
 FROM t1
)
 SELECT * FROM t2

 然后根据新求出来的rn去获得一个临时日期：

WITH t1 AS(
   SELECT DISTINCT NAME,DATE d FROM login
),
t2 AS(
   SELECT *,
      ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY d) AS rn
 FROM t1
),
t3 AS(
   SELECT*,
      DATE_SUB(d,INTERVAL rn DAY) AS temp
 FROM t2
)
 SELECT * FROM t3

 根据我们的临时日期分别对各用户进行统计并进行条件查询：

WITH t1 AS(
   SELECT DISTINCT NAME,DATE d FROM login
),
t2 AS(
   SELECT *,
     ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY d) AS rn
FROM t1
),
t3 AS(
   SELECT*,
     DATE_SUB(d,INTERVAL rn DAY) AS temp
FROM t2
),
t4 AS(
   SELECT NAME,temp,
     COUNT(1) AS cnt
FROM t3
     GROUP BY NAME,temp
     HAVING COUNT(1)>=3
)
SELECT * FROM t4

       由于有可能最后的结果的结果出来两个张三的情况，这就和我们解决的问题造成了一点冲突，展现上也不好看，所以在最后的时候还要对字段进行去重

SELECT DISTINCT NAME FROM t4

       这种方式在面试中面试官也想看到你用这种方法去解决（优选），因为这种代码可读性较高，如果要压缩合并的话，可能就你不是我，我不是你了

模板给大家：

distinct
->row_number
->date_sub(st,rn) as dt2
->group by dt2.name
->having count(1)>=N天
->distinct name
->count(name)

第二种方法(拓阔思维)：窗口函数

先上模板：

->distinct
->date_add(dt,N-1) as date2
->lead(dt,N-1) over(partition by userid order by dt)as date3
->where date2=date3
->distinct

WITH t1 AS (
  SELECT DISTINCT NAME, DATE FROM login
),
t2 AS (
  SELECT *,
     DATE_ADD(DATE, INTERVAL 2 DAY) AS date2,
     LEAD(DATE, 2) OVER (PARTITION BY NAME ORDER BY DATE) AS date3
  FROM t1
)
SELECT * FROM t2 WHERE date2 = date3;

       这种思路虽然比第一种代码少，看起来简单，但是实际理解起来确实比第一种难的多，我建议大家再面试的时候如果碰到这种题直接第一种方法直接做，肯定没有多大问题，模板记住，理解会套，就Ok了，还有在学习sql函数中，里面的参数,有的因为版本或者各个方法的问题，使得大小有异，在学习的时候千万不要照葫芦画瓢，以自己的学习sql的版本为主