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

【解题思路】

根据要求，直接思路应该是按照【用户和月份】进行分组求和即可，解题难点是连续签到的第3天和第6天可以额外领取金币，也就是其它天签到可以领取1枚金币，但是连续签到的第3天和第6天可以领取3枚和7枚金币。理想状态下，如果知道对应的签到日期是用户连续第几天签到，【再使用连续签到的天数%7得余数就可以了，当余数是3和0的时候，金币数量就是3和7，其它为1】。

【经典问题】问题：给你一堆日期，如何进行连续识别？

对一组日期进行排序编号，然后用日期减去（或者加上）编号【date_sub/date_add(date,interval rk_num day)】，若日期是连续的，那么做减法或者加法得到的结果一样。

因此，该问题的解题步骤如下：

第一步：对用户分组，然后对日期进行排序。

select  
    uid 
    ,in_time
    ,row_number() over (partition by uid order by date(in_time)) as rk_num
from tb_user_log
where artical_id=0 and sign_in=1 and date(in_time) between'2021-07-07' and  '2021-10-31'


----
+-----+---------------------+--------+
| uid | in_time             | rk_num |
+-----+---------------------+--------+
| 101 | 2021-07-07 10:00:00 |      1 |
| 101 | 2021-07-08 10:00:00 |      2 |
| 101 | 2021-07-09 10:00:00 |      3 |
| 101 | 2021-07-10 10:00:00 |      4 |
| 101 | 2021-07-11 23:59:55 |      5 |
| 101 | 2021-07-12 10:00:28 |      6 |
| 101 | 2021-07-13 10:00:28 |      7 |
| 101 | 2021-07-14 23:59:55 |      8 |
| 101 | 2021-07-15 10:00:28 |      9 |
| 101 | 2021-07-16 10:00:28 |     10 |
| 102 | 2021-10-01 10:00:28 |      1 |
| 102 | 2021-10-02 10:00:01 |      2 |
| 102 | 2021-10-03 23:59:55 |      3 |
| 102 | 2021-10-05 11:00:53 |      4 |
| 102 | 2021-10-06 11:00:45 |      5 |
+-----+---------------------+--------+
15 rows in set (0.01 sec)

第二步：获得登入日期与rk_num的差。

select  
    uid 
    ,date(in_time)
    ,row_number() over (partition by uid order by date(in_time)) as rk_num
    ,date_sub(date(in_time),interval row_number() over (partition by uid order by date(in_time)) day) as sign_rk
from tb_user_log
where artical_id=0 and sign_in=1 and date(in_time) between'2021-07-07' and  '2021-10-31'
;

----
+-----+---------------+--------+------------+
| uid | date(in_time) | rk_num | sign_rk    |
+-----+---------------+--------+------------+
| 101 | 2021-07-07    |      1 | 2021-07-06 |
| 101 | 2021-07-08    |      2 | 2021-07-06 |
| 101 | 2021-07-09    |      3 | 2021-07-06 |
| 101 | 2021-07-10    |      4 | 2021-07-06 |
| 101 | 2021-07-11    |      5 | 2021-07-06 |
| 101 | 2021-07-12    |      6 | 2021-07-06 |
| 101 | 2021-07-13    |      7 | 2021-07-06 |
| 101 | 2021-07-14    |      8 | 2021-07-06 |
| 101 | 2021-07-15    |      9 | 2021-07-06 |
| 101 | 2021-07-16    |     10 | 2021-07-06 |
| 102 | 2021-10-01    |      1 | 2021-09-30 |
| 102 | 2021-10-02    |      2 | 2021-09-30 |
| 102 | 2021-10-03    |      3 | 2021-09-30 |
| 102 | 2021-10-05    |      4 | 2021-10-01 |
| 102 | 2021-10-06    |      5 | 2021-10-01 |
+-----+---------------+--------+------------+
15 rows in set (0.00 sec)

第三步：若是日期连续，那么sign_rk就是相同的，此时再对sign_rk进行排序编号，就知道是连续签到的第几天了。

select
	uid
	,date_format(dt,'%Y-%m') as dt
    ,row_number() over(partition by uid,sign_rk order by dt) as conti_rk
from    
(select  
    uid 
    ,date(in_time) as dt
    ,row_number() over (partition by uid order by date(in_time)) as rk_num
    ,date_sub(date(in_time),interval row_number() over (partition by uid order by date(in_time)) day) as sign_rk
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
;


----
+-----+---------+----------+
| uid | dt      | conti_rk |
+-----+---------+----------+
| 101 | 2021-07 |        1 |
| 101 | 2021-07 |        2 |
| 101 | 2021-07 |        3 |
| 101 | 2021-07 |        4 |
| 101 | 2021-07 |        5 |
| 101 | 2021-07 |        6 |
| 101 | 2021-07 |        7 |
| 101 | 2021-07 |        8 |
| 101 | 2021-07 |        9 |
| 101 | 2021-07 |       10 |
| 102 | 2021-10 |        1 |
| 102 | 2021-10 |        2 |
| 102 | 2021-10 |        3 |
| 102 | 2021-10 |        1 |
| 102 | 2021-10 |        2 |
+-----+---------+----------+
15 rows in set (0.00 sec)

第四步，分组取模出结果。最终代码如下：

select
	uid
	,dt as month
	,sum(case when conti_rk % 7 = 3 then 3 when conti_rk % 7 = 0 then 7 else 1 end) as coin
from	
    (select
        uid
        ,date_format(dt,'%Y%m') as dt
        ,row_number() over(partition by uid,sign_rk order by dt) as conti_rk
    from    
        (select  
            uid 
            ,date(in_time) as dt
            ,row_number() over (partition by uid order by date(in_time)) as rk_num
            ,date_sub(date(in_time),interval row_number() over (partition by uid order by date(in_time)) day) as sign_rk
        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
group by uid,dt
order by uid,dt
;   

-- 注意：这里日期使用in_time >= '2021-07-01' and in_time <= '2021-10-31'有的实例过不了！！！