【腾讯面试题】
有一张用户签到表,表中记录了每个用户每天签到的情况。该表包括了三列日期、用户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表中的最近断签日期