SQL腾讯面试题：你今天上班打卡了吗？（连续签到天数、逻辑拆解）

【腾讯面试题】

有一张用户签到表，表中记录了每个用户每天签到的情况。该表包括了三列日期、用户id、用户当日是否签。

问题：计算截至当前每个用户已经连续签到的天数（输出表仅包含当天签到的所有用户，计算其连续签到天数）

【解题步骤】

1.分组排序

先看问题，要求输出两个字段：当天签到的用户id和连续签到的天数。

翻译成大白话就是，找出当天签到的用户，和每个用户最近一次未签到的日期，计算从上一次未签到日期到当天的间隔天数(这段时间内，该用户每天签到)。

该问题是分组排序问题，这类问题要想到《猴子 从零学会SQL》里讲过的窗口函数。

先按用户id分组，找出每个用户id当天未签到的日期，再按日期降序排序。举个例子，下图排在第一个的就是最近一次未签到的日期。

 每个用户最近一次未签到日期，SQL如下：

select a.用户id,
       a.最近一次未签到日期
from 
(select 用户id,日期 ,
        row_number() over(partition by 用户id 
                          order by 日期 desc) as 排序 
from 用户签到表 
where 用户当天是否签到 = '0'
) as a
where a.排序 = 1;

 

看到这的时候，细心的小伙伴一定发现了，用户id为1的用户，消失了。为什么呢？

因为在给出的示例数据中，用户id为1的用户每天都签到，没有未签到日期。那么这类用户的连续签到天数该如何计算呢？

我们可以查询用户签到表的开始日期，将那天作为该用户开始签到的日期，计算该日期和当天的间隔，然后加1，即为该用户的连续签到天数。

查询用户签到表开始日期的SQL如下：

select min(日期) as 用户签到表的开始日期
from 用户签到表;

2.表联结

题目要求，输出表仅包含当天签到的所有用户。因此需要2个表，表b为当天签到的用户id，表a为每个用户id的最近一次未签到日期。

根据上文描述知道，有的用户每天都签到，在表b中，该用户不存在。因此在以用户id关联两个表时，若在表b中用户id关联不到时，使用用户签到表的开始日期，计算连续签到天数。

对应SQL如下：

select b.用户id,
      (case when a.用户id is null 
            then datediff('2021-03-22',用户签到表的开始日期)+1
            when a.用户id is not null 
            then datediff('2021-03-22',a.最近一次未签到日期)
            end) as 连续签到天数
from
(
    select 用户id
    from 用户签到表
    where 日期 = '2021-03-22' #当天
    and 用户当天是否签到 = '1'
) as b
left join
(
    select a.用户id,a.最近一次未签到日期
    from 
    (
        select 用户id,日期
              ,row_number() over(partition by 用户id order by 日期 desc) as 排序
        from 用户签到表
        where 用户当天是否签到 = '0'
    ) as a
    where a.排序 = 1
) as a 
on a.用户id = b.用户id;

【本题考点】

遇到要取出每个分组(用户/部门/月份)中，某个字段的值最高/最低/处于第n个的记录，也就是分组排序问题，要想到用窗口函数：

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>) 

 注解：

        首先需要理解清楚题目意思，题目的条件是至当前为止的连续天数，也就是最后一天要是签到的，构建一个子表a，然后需要提前准备一个子表b就是最近一次没有签到的时间，用到窗口函数进行排序，子表有两种情况，一种是一直连续签到的，就不会出现在子表中，一种是中途断签了的

        将两表进行左连接，也就是以a表为基础，此时会出现两种情况，一个是今天签到了，但是不在b表中的，即是一直签到的情况，其签到天数为今天的日期减去其最开始签到的日期+1（加上今天）；第二种情况就是今天签到了，中间断签了的，其连续签到天数为当前日期减去b表中的最近断签日期