-- 第一步得到符合条件的日期 跟sign_in的值
SELECT uid,DATE(in_time) dt,sign_in ,
dense_rank()over(PARTITION BY uid ORDER BY DATE(in_time)) dr
FROM tb_user_log
WHERE artical_id=0 AND sign_in!=0 AND (in_time>'2021-07-07-00:00:00' AND in_time<'2021-11-01-00:00:00')
-- 第二步的想法 对7取模等于0 +6 对7取模等于3 +2
SELECT uid,dt,sign_in,dr ,
dense_rank()over(PARTITION BY DATE_SUB(dt,INTERVAL dr DAY),uid ORDER BY dt) dr2
FROM ( SELECT uid,DATE(in_time) dt,sign_in ,
dense_rank()over(PARTITION BY uid ORDER BY DATE(in_time)) dr
FROM tb_user_log
WHERE artical_id=0 AND sign_in!=0 AND (in_time>'2021-07-07-00:00:00' AND in_time<'2021-11-01-00:00:00')) temp1
-- 取模求值
SELECT uid,DATE_FORMAT(dt,'%Y%m') MONTH,
(CASE WHEN dr2%7=3 THEN 3 WHEN dr2%7=0 THEN 7 ELSE 1 END) sums
FROM
( SELECT uid,dt,sign_in,dr ,
dense_rank()over(PARTITION BY DATE_SUB(dt,INTERVAL dr DAY),uid ORDER BY dt) dr2
FROM ( SELECT uid,DATE(in_time) dt,sign_in ,
dense_rank()over(PARTITION BY uid ORDER BY DATE(in_time)) dr
FROM tb_user_log
WHERE artical_id=0 AND sign_in!=0 AND (in_time>'2021-07-07-00:00:00' AND in_time<'2021-11-01-00:00:00')) temp1)temp2
-- 求最后的值
SELECT uid,MONTH,SUM(sums) coin
FROM
( SELECT uid,DATE_FORMAT(dt,'%Y%m') MONTH,
(CASE WHEN dr2%7=3 THEN 3 WHEN dr2%7=0 THEN 7 ELSE 1 END) sums
FROM
( SELECT uid,dt,sign_in,dr ,
dense_rank()over(PARTITION BY DATE_SUB(dt,INTERVAL dr DAY),uid ORDER BY dt) dr2
FROM ( SELECT uid,DATE(in_time) dt,sign_in ,
dense_rank()over(PARTITION BY uid ORDER BY DATE(in_time)) dr
FROM tb_user_log
WHERE artical_id=0 AND sign_in!=0 AND (in_time>'2021-07-07-00:00:00' AND in_time<'2021-11-01-00:00:00')) temp1)temp2) temp3
GROUP BY uid,MONTH
ORDER BY MONTH,uid