SQL面试题

有一张用户签到表【t_user_attendence】，标记每天用户是否签到（说明：该表包含所有用户所有工作日的出勤记录） ，包含三个字段：日期【fdate】，用户id【fuser_id】，用户当天是否签到【fis_sign_in：0否1是】；

 

Drop table if EXISTS  `t_user_attendence`;
CREATE TABLE IF NOT EXISTS `t_user_attendence`(
   `user_id`  INT4 NOT NULL,
   `date`  Date,
   `fis_sign_in`  INT1
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into t_user_attendence (user_id,date,fis_sign_in) values
(1000,'2020-01-01',1),
(1000,'2020-01-02',1),
(1000,'2020-01-03',0),
(1000,'2020-01-04',1),
(1000,'2020-01-05',1),
(1000,'2020-01-06',1),
(1000,'2020-01-07',0),
(1001,'2020-01-01',1),
(1001,'2020-01-02',0),
(1001,'2020-01-03',0),
(1001,'2020-01-04',1),
(1001,'2020-01-05',1),
(1001,'2020-01-06',0),
(1001,'2020-01-07',0),
(1002,'2020-01-01',1),
(1002,'2020-01-02',1),
(1002,'2020-01-03',0),
(1002,'2020-01-04',1),
(1002,'2020-01-05',1),
(1002,'2020-01-06',1),
(1002,'2020-01-07',1);

问题一：有一张用户签到表【t_user_attendence】，标记每天用户是否签到（说明：该表包含所有用户所有工作日的出勤记录） ，包含字段：日期【fdate】，用户id【fuser_id】，用户当天是否签到【fis_sign_in：0否1是】；

思路🤔：先找用户最近一次未签到日期，再用今天减那个日期（前提是工作的出勤记录，所有用户都有 签到和未签到的情况，因为人不可能天天工作，或者天天休息）

select user_id
,datediff('20200107',fdate_max) fconsecutive_days
from
    ( select user_id
    ,max(date) fdate_max
    from t_user_attendence
    where fis_sign_in = 0 /*前提是工作的出勤记录，所有用户都有 签到和未签到的情况，因为人不可能天天工作，或者天天休息*/
    group by user_id
    ) t1 

 问题二：请计算每个用户历史以来最大的连续签到天数（输出表为用户签到表中所有出现过的用户，计算其历史最大连续签到天数）

思路🤔：把用户所有签到记录转化成一条0-1字符串序列，用0做split切割，计算切出来的1序列组中的最大长度

以下代码为复制粘贴原作者的，这条sql 需要在Oracle 下运行，需要想想有没有其他的写法🤔。

select fuser_id
,max(length(cut_fsign_record)) as fmax_days
(select fuser_id
,fsign_record
,cut_fsign_record
from
    (select fuser_id
    ,wm_concat(fis_sign_in) fsign_record
    from t_user_attendence
    group by fuser_id
    ) t1 
lateral view explode(split(fsign_record,'0')) t as cut_fsign_record
) t2
where cut_fsign_record<>''
group by fuser_id
;