题解 | #连续签到领金币#
连续签到领金币
http://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
今天一层层剖析到这里~测试通过,待会来写解答!!!!! 用最后提交测试的案例来做例子
(1)首先,我的思路是先提取一张表,包含连续标识(该日是否为当月有效打卡的连续日,比如10月3日签到但之后10月5日才签到,则标识为0)、打卡标识(该日是否打卡,比如10月5日标识为1,而10月4日如果出现在表中且没打卡,那么打卡标识与连续标识都为0)、行序号(按id 和 月的行序号)
其中,连续标识的条件为该条记录的artical_id = 0 且 sign_in = 1 并且小于该日的最大日期为该日的前一日;
其中打卡标识即为该条记录的artical_id = 0 且 sign_in = 1
其中行序号用row_number() over() 解决
select case when t1.artical_id = 0 and t1.sign_in = 1 and datediff(t1.in_time,(select max(in_time) from tb_user_log where tb_user_log.in_time < t1.in_time and t1.uid = tb_user_log.uid and date(tb_user_log.in_time) between '2021-07-07' and '2021-10-31')) = 1 then 1
else 0 end as 连续标识,
case when t1.artical_id = 0 and t1.sign_in = 1 then 1 else 0 end as 打卡标识,
row_number() over(partition by t1.uid order by t1.in_time) as 行序号,
t1.uid,
date(t1.in_time) as 签到日
from
tb_user_log t1
join
tb_user_log
on t1.uid = tb_user_log.uid
and t1.in_time = tb_user_log.in_time
where date(t1.in_time) between '2021-07-07' and '2021-10-31'
##这张表命名为t2
运行结果如下:
这样一来,每月出现打卡记录的第一天、中断的日期均会标识为0
(2)接下来,我需要对上面这张表进行模块划分,以便在后期的partition上可以顺利按断签的天和该月首日打卡日来分块,我的思路是,如果某一个日期记录的连续标识为0,那么从它起就要另起模块,所以该uid下该月的第一个0是模块1,直到第二个0是模块2,因此行序号减去该日前连续标识的和就是已存在0的个数(比如第10行就相当于10个1相加减去了9个1和1个0相加),也就是本记录所处的的模块
select (行序号 - (sum(连续标识) over(partition by uid order by 签到日)) + 1) as 模块,
uid,
连续标识,
打卡标识,
签到日
from
t2
order by uid,签到日,模块;
##该表命名为t3
运行结果如下:
(3)接下来,我需要构建字段“天数标识”,即该记录的in_time是连续打卡的第几天,逻辑是如果在同一uid下对同一模块进行如下操作:对该记录及之前的打卡标识和连续标识中的较大值进行加总,即可以得到该记录所处的连续打卡天数
select *,sum(case when 连续标识 < 打卡标识 then 打卡标识 else 连续标识 end) over(partition by uid,模块 order by 签到日) as 天数标记 from
t3
##该表命名为t4
运行结果如下:
(4)接下来就是签到分数的标识了,逻辑是对于天数标识为3的倍数的则赋值为1+2 = 3,标识为7的倍数的则赋值为1+6 = 7,其余除0之外的标识赋值为1
select uid,
签到日,
case when 天数标记 <> 3 and 天数标记 <> 7 and mod(天数标记,7) <> 0 and mod(天数标记-3,7) <> 0 then 1
when 天数标记 = 3 or mod(天数标记-3,7) = 0 and 天数标记 <> 0 then 3
when 天数标记 = 7 or mod(天数标记,7) = 0 and 天数标记 <> 0 then 7
else 0 end as 积分标识
from
t4
##该表命名为t5
运行结果如下:
(5)最后按uid和月对积分标识进行加总即可得最后的总分
select uid,
date_format(签到日,'%Y%m') as 月,
sum(积分标识) as 总积分
from
t5
group by uid,月;
运行结果如下:
封装代码块:
select uid,
date_format(签到日,'%Y%m') as 月,
sum(积分标识) as 总积分
from
(select uid,
签到日,
case when 天数标记 <> 3 and 天数标记 <> 7 and mod(天数标记,7) <> 0 and mod(天数标记-3,7) <> 0 then 1
when 天数标记 = 3 or mod(天数标记-3,7) = 0 and 天数标记 <> 0 then 3
when 天数标记 = 7 or mod(天数标记,7) = 0 and 天数标记 <> 0 then 7
else 0 end as 积分标识
from
(select *,sum(case when 连续标识 < 打卡标识 then 打卡标识 else 连续标识 end) over(partition by uid,模块 order by 签到日) as 天数标记 from
(select (行序号 - (sum(连续标识) over(partition by uid order by 签到日)) + 1) as 模块,
uid,
连续标识,
打卡标识,
签到日
from
(select case when t1.artical_id = 0 and t1.sign_in = 1 and datediff(t1.in_time,(select max(in_time) from tb_user_log where tb_user_log.in_time < t1.in_time and t1.uid = tb_user_log.uid and date(tb_user_log.in_time) between '2021-07-07' and '2021-10-31')) = 1 then 1
else 0 end as 连续标识,
case when t1.artical_id = 0 and t1.sign_in = 1 then 1 else 0 end as 打卡标识,
row_number() over(partition by t1.uid order by t1.in_time) as 行序号,
t1.uid,
date(t1.in_time) as 签到日
from
tb_user_log t1
join
tb_user_log
on t1.uid = tb_user_log.uid
and t1.in_time = tb_user_log.in_time
where date(t1.in_time) between '2021-07-07' and '2021-10-31') t2
order by uid,签到日,模块) t3) t4) t5
group by uid,月;