SELECT uid, DATE_FORMAT(dt,'%Y%m') month, sum(day_coin) coin from (SELECT *, DATE_SUB(dt,INTERVAL rn day) dt_tmp, case DENSE_RANK() over(PARTITION BY DATE_SUB(dt,INTERVAL rn day),uid ORDER BY dt )%7 WHEN 3 THEN 3 WHEN 0 THEN 7 ELSE 1 END as day_coin from (SELECT DISTINCT uid, DATE(in_time) dt, DENSE_RANK() over(PARTITION BY uid ORDER BY DATE(in_time)) rn -- 编号 FROM tb_user_log WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31' AND artical_id = 0 AND sign_in = 1 ) t1) t2 GROUP BY uid, DATE_FORMAT(dt,'%Y%m') ORDER BY DATE_FORMAT(dt,'%Y%m'),uid