Mysql 解法, 执行用时: 36ms, 内存消耗: 6444KB, 提交时间: 2022-01-22

#给确定每个人每个签到阶段的起始日期
select uid,date_format(dt,'%Y%m') as month
,sum(case when stage_index=2 then 3
   when stage_index=6 then 7
   else 1 end) as coin
from(
    select uid,dt
    ,(row_number()over(partition by uid,init_date order by dt)-1)%7 as stage_index
    from
    (
        select uid,dt,rn,subdate(dt,rn) as init_date
        from (
            #给符合条件的每个人的日期编号
            select uid,date(in_time) as dt
            ,row_number()over(partition by uid order by date(in_time)) as rn
            from tb_user_log
            where date(in_time) >='2021-07-07'
            and date(in_time)<'2021-11-01'
            and artical_id=0 and sign_in=1
        ) raw_t
    ) init_date_t
) a
group by uid,month
order by month,uid;

Mysql 解法, 执行用时: 36ms, 内存消耗: 6596KB, 提交时间: 2022-01-01

select uid,df_date,sum(jinbi) from
(
select uid,date_format(ds_date,'%Y%m') df_date,sum(jinbi) jinbi
FROM
(
select uid,date_sub(new_in_time,interval rn day) ds_date,count(*) ct,
(count(*) div 7)*8+(mod(count(*),7)  div 3)*2 jinbi
FROM
(
select uid,date_format(in_time,'%Y-%m-%d') new_in_time,row_number() over(partition by uid order by in_time)-1 rn
from tb_user_log
where sign_in = 1 and in_time between '2021-07-07' and '2021-11-01' and artical_id=0
)a group by uid,ds_date
)b group by uid,df_date

union ALL

select uid,date_format(in_time,'%Y%m') df_date ,sum(sign_in) jinbi
from tb_user_log
where sign_in = 1 and in_time between '2021-07-07' and '2021-11-01' and artical_id=0
group by uid,df_date
)c group by uid,df_date

Mysql 解法, 执行用时: 37ms, 内存消耗: 6396KB, 提交时间: 2022-02-21

select uid,date_format(sign_dt,"%Y%m") as mon,sum(coin)
from
(
    select uid,
       case (RANK() over(partition by uid ,TIMESTAMPADD(day,-diff+1,sign_dt)
                        order by sign_dt))%7  when 3 then 3
                                                 when 0 then 7
                                                 else 1 end as coin,
      sign_dt
from
(
select uid,DATE_FORMAT(in_time,"%Y%m%d") as sign_dt,
       RANK() over(partition by uid order by DATE_FORMAT(in_time,"%Y%m%d"))
       as diff
from tb_user_log
where DATE_FORMAT(in_time,"%Y%m%d") between "20210707" and "20211031"
      and artical_id=0 and sign_in=1
) tmp
    
) base
group by uid,date_format(sign_dt,"%Y%m")
order by mon,uid

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2022-02-25

SELECT uid,DATE_FORMAT(sign_dt,'%Y%m')as month,sum(coin)
FROM
(SELECT uid,sign_dt,TIMESTAMPADD(day,-diff+1,sign_dt)as start_day ,
case (DENSE_RANK() over (PARTITION by uid,TIMESTAMPADD(day,-diff+1,sign_dt) ORDER BY sign_dt))%7
WHEN 3 then 3 
WHEN 0 THEN 7
ELSE 1 end as coin
FROM
(SELECT uid ,DATE_FORMAT(in_time,'%Y%m%d')as sign_dt,
DENSE_RANK() over(PARTITION by uid ORDER BY in_time) as diff 
FROM tb_user_log
WHERE DATE_FORMAT(in_time,'%Y%m%d') BETWEEN 20210707 and 20211031 
AND artical_id =0 AND sign_in =1 )t1 )t2 
GROUP BY uid,DATE_FORMAT(sign_dt,'%Y%m')
ORDER BY DATE_FORMAT(sign_dt,'%Y%m') ,uid

Mysql 解法, 执行用时: 37ms, 内存消耗: 6412KB, 提交时间: 2022-01-24

# 1.我们只要先排序，再将日期减去排序值就可以得到第一天连续打卡的时间，从而解决打断的问题。
#   tmp表是得到排序数，base表是减去排序值。
# 2.之后我们根据 用户 和 每个阶段连续打卡的第一天 进行分组就可以得到今天是连续打卡的第几天 
# 3.之后根据数学规则mod7就可以根据规则得到改天得到的金币 
# 4.再根据月进行聚合求sum即可
# TIMESTAMPADD(unit,interval,datetime_expr)
# dense_rank() -- '1 2 2 4(排名不连续)

select uid,date_format(sign_dt,"%Y%m") as mon,sum(coin)
from
(select uid,
     TIMESTAMPADD(day,-diff+1,sign_dt) as start_dt,
     case (DENSE_RANK() over(partition by uid ,TIMESTAMPADD(day,-diff+1,sign_dt) order by sign_dt))%7
     when 3 then 3  -- 连续第3天,3分
     when 0 then 7  -- 连续第3天,7分
     else 1  -- 普通时间,1分
     end as coin,
     sign_dt
from
(select uid,
     DATE_FORMAT(in_time,"%Y%m%d") as sign_dt,
     DENSE_RANK() over(partition by uid order by DATE_FORMAT(in_time,"%Y%m%d")) as diff
     from tb_user_log
     where DATE_FORMAT(in_time,"%Y%m%d") between "20210707" and "20211031"
     and artical_id=0 and sign_in=1) tmp) base
group by uid,date_format(sign_dt,"%Y%m")
order by mon,uid