SQL高频面试题：求最大连续登录天数，间隔n天内都可以算做连续登录

冷不防 2023-10-04 18:35 49阅读 0赞

### SQL高频面试题：求最大连续登录天数，间隔n天内都可以算做连续登录 ###

> 连续问题的解决思路，通过等差数列，在之前讲解过[SQL解决标准连续问题(等差方法)][SQL]，如果题目改变，变成了如果是间隔两天，都算是连续登录，问题怎么解决？

id		dt
    1		2022-05-01
    1		2022-05-02
    1		2022-05-04
    1		2022-05-07

比如有上述数据，得到id=1的连续登录天数为4天，2022-05-01到2022-05-04

> 怎么解决？

除了等差方法求解，求得两次等差的方法，还可以按照这种

1.  获得每行数据的前一行数据
2.  获得和前一行数据的时间差
3.  开窗，如果时间差大于2则为1，求和
4.  按照求和结果分组，得到最大值

> 求解

1.  获得每行数据的前一行数据
    
    注意：这里没有重复值，如果有重复值的时候，要去重的
    
        select id
          ,dt
          ,lag(dt,1,'1970-00-00')
          over(partition by id order by dt) lagdt
        from conq
2.  获得和前一行数据的时间差
    
        select id
          ,dt
          ,datediff(dt, lagdt) flag
        from t1
3.  开窗，如果时间差大于2则为1，求和
    
        select id
          ,dt
          ,sum(if(flag>2,1,0)）
          over(partition by id order by dt) flag
        from t2
4.  按照求和结果分组，得到最大值
    
        select id
          ,flag
          ,datediff(max(dt),min(dt))+1 days
        from t3
        group by id,flag
5.  取得最大值
    
        select id
          ,max(days) max_days
        from t4
        group by id

> 总代码：

with r1 as (
      select id
      ,dt
      ,datediff(dt, lagdt) flag
    from (
    select id
      ,dt
      ,lag(dt,1,'1970-00-00')
      over(partition by id order by dt) lagdt
    from conq
    ) t1
    ),
    
    r2 as (
      select id
        ,dt
        ,sum(if(flag>2,1,0))
        over(partition by id order by dt) flag
      from r1
    ),
    
    r3 as (
      select id
      ,flag
      ,datediff(max(dt),min(dt)) days
    from r2
    group by id,flag 
    )
    
    select id
      ,max(days)+1 max_days
    from r3
    group by id

> 结果

![在这里插入图片描述][65c74f7da59840c0be8e44681f2c702a.png]

> 这个方式好在一点就是，问题变了，变成了如果是间隔3天就算是连续，那我们只需要改一下数字即可

select id
      ,dt
      ,sum(if(flag>3,1,0)）
      over(partition by id order by dt) flag
    from t2

![在这里插入图片描述][d0cb7150f5204029970760ed0aab79d2.png]

> 如果改成标准连续，就是一天间断都不行

select id
      ,dt
      ,sum(if(flag>1,1,0)）
      over(partition by id order by dt) flag
    from t2

![在这里插入图片描述][42a20282e3494e5fa7b12e975bf7f5b5.png]

[SQL]: http://t.csdn.cn/EN5Ai
[65c74f7da59840c0be8e44681f2c702a.png]: https://img-blog.csdnimg.cn/65c74f7da59840c0be8e44681f2c702a.png
[d0cb7150f5204029970760ed0aab79d2.png]: https://img-blog.csdnimg.cn/d0cb7150f5204029970760ed0aab79d2.png
[42a20282e3494e5fa7b12e975bf7f5b5.png]: https://img-blog.csdnimg.cn/42a20282e3494e5fa7b12e975bf7f5b5.png