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

with t1 as (
  select uid,date(in_time) dayt,sign_in,datediff(lead(date(in_time)) over(partition by uid order by in_time),date(in_time)) as diff_d
from tb_user_log where date(in_time) BETWEEN '2021-7-7' and '2021-10-31'  and artical_id =0)
select uid,DATE_FORMAT(dayt,'%Y%m') as month,sum(score) as coin  from (
SELECT
uid,dayt,sign_in,diff_d,if(@uid=uid,if(sign_in=1 ,if (@diff =1 ,@day:=@day+1,@day:=1) ,@day:=0),@day:=1) as  day_s,@uid:=uid,@diff:=diff_d,
case  when    @day=0 then 0  when mod(@day,7)=3 then 3 when mod(@day,7)=0 then 7 else 1 end as score  
 from t1,(select @day := 1,@uid :=null,@diff:=1  )t 
)t
group by uid,DATE_FORMAT(dayt,'%Y%m') 
order by month,uid

要求条件
1、artical_id = 0
2、2021-7-7至2021-10-31 为止，如果in_time 不加date函数，会少算日子
3、未签到的日子金币为0
4、签到3、7为倍数的日子，金币为3，7其他结为1

上面答案使用变量完成，甚是复杂，后看了评论区后，还是自己思路不够开阔，不用变量简单很多。

select uid,DATE_FORMAT(dt,'%Y%m') month ,sum(grade) from
     (select uid ,dt,  
     case 
     when   mod( rank() over  (partition by uid,rank_day order by dt),7) =3 then 3     
     when   mod( rank() over (partition by uid,rank_day order by dt) ,7)=0 then 7 
     else 1   end   grade    
     from 
        (
        select
        uid,
        dt,
        date_sub(dt,INTERVAL RANK() over(PARTITION by uid order by dt) day )  rank_day  from
            (
            select DISTINCT uid, date(in_time) dt  #查出所有签到记录 过滤掉可能重复的记录
            from tb_user_log 
            where artical_id=0 and sign_in=1 and date(in_time) BETWEEN '2021-07-07' and '2021-10-31' 
            ) t1
        ) t2
     )t3
group by uid,month;

这种思路逻辑简洁，通过去掉sign=0等数据，然后利用排名生成连续的辅助列，然后利用两个分组排序，算出连续签到天数，根据得分规则打分即可。

第一个重点是可以想到生成连续的辅助列。