SQL计算用户连续签到问题

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

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

解析：对用户分组 取该用户当前日期-最近一次未签到的日期差即为连续签到天数。
存在两种情况：1.当从最开始一直签到未断，连续天数则为当前日期-最开始日期+1；
2.中间签到断过，则为当前日期-最大断签的日期

#第一步，计算用户最初签到日期
select 
 fuser_id ,
 min(fdate) '最开始签到日期'
from t_user_attendence 
where fis_sign_in=1 
group by 1

#第二步，计算用户最大断签日期
select 
 fuser_id ,
 max(fdate) '最大断签日期'
from t_user_attendence 
where fis_sign_in=0 #(and fdate<='2021/6/4' 如果不是求当前（2021/6/6），而是求之前某一日期需在此处限制日期）
group by 1

#第三步，结果查询
select 
 fuser_id,
 fcontinuous_days 
from
(
SELECT
	a.fuser_id,
CASE WHEN c.最大断签日期 IS NULL THEN
		  datediff( '2021/6/6', b.最开始签到日期 )+ 1 ELSE datediff( '2021/6/6', c.最大断签日期 ) 
	 END AS fcontinuous_days 
FROM
	t_user_attendence a
	LEFT JOIN ( SELECT fuser_id, min( fdate ) '最开始签到日期' FROM t_user_attendence WHERE fis_sign_in = 1 GROUP BY 1 ) b 
	ON a.fuser_id = b.fuser_id
	LEFT JOIN ( SELECT fuser_id, max( fdate ) '最大断签日期' FROM t_user_attendence WHERE fis_sign_in = 0 GROUP BY 1 ) c 
	ON a.fuser_id = c.fuser_id 
WHERE a.fdate = '2021/6/6'
 )t
where t.fcontinuous_days !=0

左连接’最大断签日期’，如果未NULL则证明该用户一直签到未断

最终结果（别忘记题目，输出表仅包含当天签到的所有用户）

题目二，查找所有签到连续三次及以上的user_id

解析：连续签到即该用户两次日期的签到状态fis_sign_is=1且日期差=1

第一步，将日期向下偏移一位，即lag_date表示fdate对应的前一天。
select 
  fdate, 
	fuser_id, 
	fis_sign_in, datediff(fdate,lag(fdate) over(partition by fuser_id order by fdate)) diff 
 from t_user_attendence 
 where fis_sign_in=1

所得结果如下，datediff(fdate,lag_date),判断日期差是否等于1，=1则连续，≠1不连续

第二步 计算结果
select
a.fuser_id,
sum(case when a.diff=1 then 1 else 0 end)+1 '连续次数'
from
(select 
  fdate, 
	fuser_id, 
	lag(fdate) over(partition by fuser_id order by fdate) lag_date,
	fis_sign_in, datediff(fdate,lag(fdate) over(partition by fuser_id order by fdate)) diff 
 from t_user_attendence 
 where fis_sign_in=1
)a
group by a.fuser_id, a.diff

从上个结果可看出，当diff=1时日期是连续的，计为1，当diff≠1时日期是断开的，记为0。最后按fuser_id将日期连续对应的1相加，再加1即可得出所有对应连续天数

三、查找所有用户的最大连续签到次数

解析：在第二题的基础上按fuser_id计算max(连续次数)即可

SELECT
t.fuser_id,
max(t.连续次数) '最大连续次数'
from
(select
a.fuser_id,
sum(case when a.diff=1 then 1 else 0 end)+1 '连续次数'
from
(select 
  fdate, 
	fuser_id, 
	lag(fdate) over(partition by fuser_id order by fdate) lag_date,
	fis_sign_in, datediff(fdate,lag(fdate) over(partition by fuser_id order by fdate)) diff 
 from t_user_attendence 
 where fis_sign_in=1
)a
group by a.fuser_id, a.diff
)t
group by t.fuser_id在这里插入代码片