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

连续签到领金币

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

运行结果如下: alt

这样一来,每月出现打卡记录的第一天、中断的日期均会标识为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

运行结果如下: alt

(3)接下来,我需要构建字段“天数标识”,即该记录的in_time是连续打卡的第几天,逻辑是如果在同一uid下对同一模块进行如下操作:对该记录及之前的打卡标识和连续标识中的较大值进行加总,即可以得到该记录所处的连续打卡天数

select *,sum(case when 连续标识 < 打卡标识 then 打卡标识 else 连续标识 end) over(partition by uid,模块 order by 签到日) as 天数标记 from
t3
##该表命名为t4

运行结果如下: alt

(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

运行结果如下:

alt

(5)最后按uid和月对积分标识进行加总即可得最后的总分

select uid,
       date_format(签到日,'%Y%m') as 月,
       sum(积分标识) as 总积分
       from
       t5
       group by uid,月;

运行结果如下:

alt

封装代码块:

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,月;
全部评论

相关推荐

鬼迹人途:你去投一投尚游游戏,服务器一面,第一个图算法,做完了给你一个策略题,你给出方案他就提出低概率问题,答不上当场给你挂
点赞 评论 收藏
分享
04-25 18:13
五邑大学 Java
后来123321:大二两段实习太厉害了,我现在大二连面试都没有
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务