SQL常见面试提：解决连续问题（CSDN签到领金币、连续登录天数等问题）

清疚 2023-10-04 18:32 11阅读 0赞

### SQL解决连续问题（连续登录天数等问题） ###

#### 目录内容 ####

*   *  SQL解决连续问题（连续登录天数等问题）
     *   *  一、问题描述
         *  二、小试牛刀
         *   *  步骤1：按照日期排序
             *  步骤2：按照零头进行分组，再次排序（其实这里不用排序，直接得到队伍个数，哪个最大就行）
             *  代码：
         *  三、问题升级

连续登录天数问题我们可以看作是一个排队的问题，就是把连续登录的一组当作是一个队伍，这个队伍有着共同的领队，如果中断，则去往另一个队伍，这个队伍也有一个同样的领队

#### 一、问题描述 ####

怎么理解呢？看个栗子：

比如一组数据

2022-04-13
    2022-04-14
    2022-04-15
    2022-04-16
    2022-04-17
    2022-04-19
    2022-04-20
    2022-04-22

把连续的天数当做是一个队伍，那么就会有下面的数据

队伍1：
    领队： 2022-04-12
    2022-04-13
    2022-04-14
    2022-04-15
    2022-04-16
    2022-04-17
    队伍2：
    领队： 2022-04-18
    2022-04-19
    2022-04-20
    队伍3：
    领队： 2022-04-21
    2022-04-22

就像上述的样式，为什么领队是前一天呢，其实我们可以看到，每个队伍按照顺序排序，刚好领队就是这个日期的前n天，n就是排序序号，比如：队伍1中，2022-04-13排序是1，领队就是前一天2022-04-12

#### 二、小试牛刀 ####

小需求：找到连续登录天数最长是几天

拿一个队伍举例：

##### 步骤1：按照日期排序 #####

日期			序号			领头
    2022-04-13		1		2022-04-12
    2022-04-14		2		2022-04-12
    2022-04-15		3		2022-04-12
    2022-04-16		4		2022-04-12
    2022-04-17		5		2022-04-12
    2022-04-19		6		2022-04-13
    2022-04-20		7		2022-04-13

##### 步骤2：按照零头进行分组，再次排序（其实这里不用排序，直接得到队伍个数，哪个最大就行） #####

日期			序号			领头		二次排序
    2022-04-13		1		2022-04-12		1
    2022-04-14		2		2022-04-12		2
    2022-04-15		3		2022-04-12		3	
    2022-04-16		4		2022-04-12		4	
    2022-04-17		5		2022-04-12		5
    2022-04-19		6		2022-04-13		1
    2022-04-20		7		2022-04-13		2

##### 代码： #####

select count(*) max_date
    from (
    select date_sub(date_log, dr) top_date
      ,date_log
    from (
    select date_log
      ,dense_rank()
      over(order by date_log) dr  -- 第一次排序
    from tbl_log
    ) t1
    ) t2
    group by top_date
    order by count(*) desc
    limit 1;

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

#### 三、问题升级 ####

如果涉及的问题不只是作为求得连续登录的最大值，变成了连续天数不一样做出的判断也不一样，比如，CSN之前的签到，连续签到的天数不一样，给的金币也不一样，而且按照一周一个轮询 ，就算你中间断了，在周三开始再次签到，也会按照第一天，该怎么办

日期			序号		第几天
    2022-04-13		1		1
    2022-04-14		2		2
    2022-04-15		3		3
    2022-04-16		4		4
    2022-04-17		5		5
    2022-04-19		6		1
    2022-04-20		7		2

就像上面的描述

其实也很简单，就是比着最大登录天数增加了一个步骤，按照领头在做一次排序

第二次排序就是一个周期内的第几次签到

代码：

select date_log
      ,top_date
      ,dense_rank()
      over(partition by t2.top_date order by date_log) trk
    from (
    select distinct date_sub(date_log, dr) top_date
      ,date_log
    from (
    select date_log
      ,dense_rank()
      over(order by date_log) dr  -- 第一次排序
    from tbl_log
    ) t1
    ) t2;

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

那么如果是第一天就是1分钱，第二天也是1分钱，第三天给2分钱，第四天给3分钱，第五天给5分钱，第六天给8分钱，第七天给1毛3，然后轮询

这个实现其实就是按照第二次排序的给的，使用一个取余即可

select date_log
      ,top_date
      ,dense_rank()
      over(partition by t2.top_date order by date_log) trk
      ,case dense_rank() over(partition by t2.top_date order by date_log) % 7
        when 1 then 1
        when 2 then 1
        when 3 then 2
        when 4 then 3
        when 5 then 5
        when 6 then 8
        when 0 then 13
        else 0
      end coin
    from (
    select distinct date_sub(date_log, dr) top_date
      ,date_log
    from (
    select date_log
      ,dense_rank()
      over(order by date_log) dr  -- 第一次排序
    from tbl_log
    ) t1
    ) t2;

![在这里插入图片描述][99b566cea8444d4388aa9f213a337a6a.png]  
注：当然拉，这里知识举个栗子，真实的签到活动中要比这个逻辑复杂，这里当作是一个思维扩展即可

[6eef2841e2be4f488788849af8dd7bce.png]: https://img-blog.csdnimg.cn/6eef2841e2be4f488788849af8dd7bce.png
[ec2bf9d793a7400e8470439cdbd03717.png]: https://img-blog.csdnimg.cn/ec2bf9d793a7400e8470439cdbd03717.png
[99b566cea8444d4388aa9f213a337a6a.png]: https://img-blog.csdnimg.cn/99b566cea8444d4388aa9f213a337a6a.png