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

题目算积分没有明确是 年内/月内 的连续签到，测试用例的签到数据也是没有连着月的。

比如09-29到10-01的连续签到，经历了09-29 | 09-30 | 10-01，那么10月1号的签到是领1积分还是3积分？

在此记录一下我额外的想法。

月内连续签到

select
uid,sign_month,
sum(coin) as coin
from
	(select
	uid,
	sign_month,
	case
	when (count(sign_num) over(partition by uid,sign_month,sign_num order by sign_date))%7=3 then 3
	when (count(sign_num) over(partition by uid,sign_month,sign_num order by sign_date))%7=0 then 7
	else 1
	end as coin
	from
		(select
		distinct uid, date_format(in_time,"%Y%m") as sign_month, date_format(in_time,"%Y%m%d") as sign_date,
		dayofyear(in_time)-(row_number() over(partition by uid order by date_format(in_time,"%Y%m%d"))) as sign_num
		from
		tb_user_log
		where artical_id=0 and sign_in=1 and in_time between "2021-07-07 00:00:00" and "2021-11-01 00:00:00") as tmp) as tmp2
group by uid,sign_month;

这个sql可以处理月内连续签到的积分计算。

我这里通过第16行 dayofyear(in_time)-(row_number() over(partition by uid order by date_format(in_time,"%Y%m%d"))) as sign_num来作为当年连续签到的判断标识：

• dayofyear(in_time)是当年的第几天，
• (row_number() over(partition by uid order by date_format(in_time,"%Y%m%d")))是按用户uid分组日期正序的连续数值，前者减去后者就可以得到当年连续签到的判断标识sign_num。

sign_num的理解：因为连续数值是+1的，如果每天都签到了，那相减的数值就会是一样的。

你可能会想那我同一天签到两次呢（in_time有相同的签到日期），这里用distinct就解决这个问题了，在第15行。

年内连续签到

select
uid,sign_month,
sum(coin) as coin
from
	(select
	uid,
	sign_month,
	case
	when (count(sign_num) over(partition by uid,sign_num order by sign_date))%7=3 then 3
	when (count(sign_num) over(partition by uid,sign_num order by sign_date))%7=0 then 7
	else 1
	end as coin
	from
		(select
		distinct uid, date_format(in_time,"%Y%m") as sign_month, date_format(in_time,"%Y%m%d") as sign_date,
		dayofyear(in_time)-(row_number() over(partition by uid order by date_format(in_time,"%Y%m%d"))) as sign_num
		from
		tb_user_log
		where artical_id=0 and sign_in=1 and in_time between "2021-07-07 00:00:00" and "2021-11-01 00:00:00") as tmp) as tmp2
group by uid,sign_month;

这个sql可以处理年内连续签到的积分计算。

相比较于月内连续签到的sql，这里就删掉了第9，10行的sign_month，就可以处理年内连续签到了。