问题：计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序。
注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了
读题后思路如下：我们选出活动期间内所有签到的日期，判断其是连续签到的第几天，然后按月份和uid分组将所得硬币Sum即可。
1.这里有一个技巧。我们需要判断是否连续签到：则将用户签到的日期排序得到rk，用签到的日期减去rk 如果得到的这个差值日期相等则在连续签到。说起来有点抽象，但是只要举几个例子很快就能明白
select uid,dt,rk,date_sub(dt,interval rk day) as rn from
(select distinct uid,date(in_time) as dt,rank()over(partition by uid order by date(in_time)) as rk
from tb_user_log
where artical_id=0 and sign_in=1) as u
where dt between '2021-07-07' and '2021-10-3
我们使用上述代码即可将每个用户签到日期进行排序，然后用签到当天的日期减去这个排序号 得到rn 结果如下：
仔细观察这个输出结果就能发现，我们这个原理是合理的。
2.我们接下来要做的就是求出所得硬币和，那么其实就是需要将同一用户同一rn的组按dt进行排序。这样就能得到他是连续登录的第几天了
select uid,dt,rn,rank()over(partition by uid,rn order by dt) as rm from p
我们把一中的表作为p 即可得到如下结果：
3.剩下的就是按月份以及用户进行分组然后求和了。注意题目中的规则，是连续签到的3、7天额外得2、6金币，那实际就是3，7金币。7天后进入第八天又开始算第一天，我们自然想到%除取余数即可。
select uid,date_format(dt,'%Y%m') as month,
sum(case when rm%7=0 then 7 when rm%7=3 then 3 else 1 end ) as coin
from t
group by uid,month
order by month,uid
完整代码如下：
with t as
(select uid,dt,rn,rank()over(partition by uid,rn order by dt) as rm from
(select uid,dt,rk,date_sub(dt,interval rk day) as rn from
(select distinct uid,date(in_time) as dt,rank()over(partition by uid order by date(in_time)) as rk
from tb_user_log
where artical_id=0 and sign_in=1) as u
where dt between '2021-07-07' and '2021-10-31'
) as p )
select uid,date_format(dt,'%Y%m') as month,
sum(case when rm%7=0 then 7 when rm%7=3 then 3 else 1 end ) as coin
from t
group by uid,month
order by month,uid
京公网安备 11010502036488号