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

男娘i 2023-10-14 23:21 10阅读 0赞

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

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

![96954aad910844b59d3fd2a8de8a4630.png][]

--------------------

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

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

![ebc3659e266647c686074471f6c63b4f.png][]

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

先进行创建表：

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

![fbab6a4978b849f8be7bd7e216654f68.png][]

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

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

![40cabf00c96e497899bfaf3833e7e9a6.png][]

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

![6f2dcc0781b44f9e8a12f5f2f3f5e9df.png][]

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

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

![3815cc6708fe4c63ae6c07944231b55a.png][]

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

SELECT DISTINCT NAME FROM t4

![32121b4b3c864efb8f57aa33f380d9f9.png][]

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

模板给大家：

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

![9c45b8c012d74bef8647ed099e3a08b1.png][]

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;

![e7f4580764d6472f80610e0f6d7deba9.png][]

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

[96954aad910844b59d3fd2a8de8a4630.png]: https://img-blog.csdnimg.cn/96954aad910844b59d3fd2a8de8a4630.png
[f0af90cb533040a3af2dcba26ea974ba.png]: https://img-blog.csdnimg.cn/f0af90cb533040a3af2dcba26ea974ba.png
[ebc3659e266647c686074471f6c63b4f.png]: https://img-blog.csdnimg.cn/ebc3659e266647c686074471f6c63b4f.png
[fbab6a4978b849f8be7bd7e216654f68.png]: https://img-blog.csdnimg.cn/fbab6a4978b849f8be7bd7e216654f68.png
[40cabf00c96e497899bfaf3833e7e9a6.png]: https://img-blog.csdnimg.cn/40cabf00c96e497899bfaf3833e7e9a6.png
[6f2dcc0781b44f9e8a12f5f2f3f5e9df.png]: https://img-blog.csdnimg.cn/6f2dcc0781b44f9e8a12f5f2f3f5e9df.png
[3815cc6708fe4c63ae6c07944231b55a.png]: https://img-blog.csdnimg.cn/3815cc6708fe4c63ae6c07944231b55a.png
[32121b4b3c864efb8f57aa33f380d9f9.png]: https://img-blog.csdnimg.cn/32121b4b3c864efb8f57aa33f380d9f9.png
[9c45b8c012d74bef8647ed099e3a08b1.png]: https://img-blog.csdnimg.cn/9c45b8c012d74bef8647ed099e3a08b1.png
[e7f4580764d6472f80610e0f6d7deba9.png]: https://img-blog.csdnimg.cn/e7f4580764d6472f80610e0f6d7deba9.png