题解 | #连续签到领金币#

今天一层层剖析到这里~测试通过，待会来写解答！！！！！ 用最后提交测试的案例来做例子

（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,月;