最近在做大厂的sql题目,碰到了连续签到天数的问题,结合LeetCode答主对连续问题的解答,对连续签到问题的解法进行了优化,让自己更容易理解。
题目是从公众号“ 数据管道”上来的,公众号也有解法,贴上公众号链接腾讯数据分析面试题
题目:有一张用户签到表【t_user_attendence】,标记每天用户是否签到(说明:该表包含所有用户所有工作日的出勤记录) ,包含三个字段:日期【fdate】,用户id【fuser_id】,用户当天是否签到【fis_sign_in:0否1是】
问题1:请计算截至当前每个用户已经连续签到的天数
用公众号的解法即可,计算最近一次未签到的日期,再用当前日期减去那个日期。
select
fuser_id
,datediff('20200415',fdate_max) as fconsecutive_days
from
(select fuser_id
,max(fdate) fdate_max
from t_user_attendence
where fis_sign_in = 0
group by fuser_id
) t1;
问题2:请计算每个用户历史以来最大的连续签到天数
公众号上给出了解决思路,把用户所有签到记录转化成一条0-1字符串序列,用0做split切割,计算切出来的1序列组中的最大长度。
公众号的解决思路我理解,但是里面用到一些特别的函数,有时候受版本限制,mysql还不能用,我就没有实际跑过这个代码,还是放出来供参考。
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;
最近在刷LeetCode题目,有这样一道题目:取连续数字,有一位答主用row_number就解决了(贴链接LeetCode取连续数字),其思路是将出现的数值按自然连续排序,并把相同数值进行分组再自然连续排序,两个排序相减得到差值t,若数值连续,则差值t相等。我按照这个思路重新想了问题2,得到了新的sql解决方案。
据此解决思路,重新码了取最大连续天数的代码。
先按人分组按天进行自然连续排序;再只取签到部分,按人分组进行自然连续排序,相差得到差值diff1;再按照差值diff1分组计数,得到每人连续签到的天数,求最大值即可。
SELECT
fuser_id,max(ct) as max_ct
FROM
(
SELECT
fuser_id
,diff1
,count(diff1) as ct
FROM
(
SELECT
*
,row_number() over (PARTITION by fuser_id ORDER BY fdate) as or2
,or1 - row_number() over (PARTITION by fuser_id ORDER BY fdate) as diff1
FROM
(
SELECT
fdate
,fuser_id
,fis_sign_in
,row_number() over (partition by fuser_id order by fdate) as or1
from t_user_attendence
order by fuser_id,fdate
) t
where fis_sign_in =1
) t2
GROUP BY fuser_id,diff1
) t3
GROUP BY fuser_id;
感谢大佬们,站在巨人的肩膀上的感觉,hhhh~~~