腾讯面试题：你今天上班打卡了吗？

【腾讯面试题】

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

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

【解题步骤】

1.分组排序

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

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

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

先按用户id分组，找出每个用户id当天未签到的日期，再按日期降序排序。举个例子，

下图排在第一个的就是最近一次未签到的日期。

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

1 select a.用户id,2 a.最近一次未签到日期3 from4 (select 用户id,日期 ,5 row_number() over(partition by 用户id 6 order by 日期 desc) as 排序7 from 用户签到表8 where 用户当天是否签到 = '0'9 ) as a10 where a.排序 = 1;查询结果：

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

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

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

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

1 select min(日期) as 用户签到表的开始日期2 from 用户签到表;2.表联结

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

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

对应SQL如下：

1
2 select b.用户id,
3 (case when a.用户id is null
4 then datediff('2021-03-22',用户签到表的开始日期)+1
5 when a.用户id is not null
6 then datediff('2021-03-22',a.最近一次未签到日期)
7 end) as 连续签到天数
8 from
9 (
10 select 用户id
11 from 用户签到表
12 where 日期 = '2021-03-22' #当天
13 and 用户当天是否签到 = '1'
14 ) as b
15 left join
16 (
17 select a.用户id,a.最近一次未签到日期
18 from
19 (
20 select 用户id,日期
21 ,row_number() over(partition by 用户id order by 日期 desc) as 排序
22 from 用户签到表
23 where 用户当天是否签到 = '0'
24 ) as a
25 where a.排序 = 1
26 ) as a
27 on a.用户id = b.用户id; 查询结果：

【本题考点】

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

1 over (partition by 2 order by )