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

遇到比较复杂的逻辑问题时, 首先就是要一步步拆解细化

1. 首先是只有artical_id为0时sign_in值才有效, 计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）, 添加where条件过滤即可.
2. 连续签到的积分怎么求?

• 首先明确, 连续签到这个行为肯定是某个用户的, 需要根据用户分组, 其次, 签到是有先后顺序的.

• 根据窗口函数, 对uid进行分组, 再对in_time机型升序排序, 就能得到用户签到的时间先后顺序.

• 如上表所示, 此时聪明的读者会发现, 中间断签, 但是排序却是不间断的, 所以我们离答案只差最后一部了
• 观察in_time, 会发现该列和order同序, 只需要利用in_time的有序但间断的特性和order列相减, 就能求得用户到底连续签到了多少天了.(注: 连续问题的核心就利用in_time原始排序列, 和构造的新排序列order的差值是相等的)
• 这里方法有很多, 我是用的是to_days(in_time) - order, 得到如下表格

• 到这里答案已经呼之欲出了, 只需要再构造新的一列, 根据uid, sign_mark分组, in_time组内排序, 即可得到用户连续签到的天数, 再根据连续签到的天数对7取余, 等于3, 7的额外增加2, 6个金币

• 最后再根据uid, month分组, mark进行求和, 即可得到答案了

怎么样, 这样分析下来是不是感觉很简单呀

select t2.uid, t2.month, sum(t2.coin) as coin
from (
    select t1.uid, t1.month,
        case when rank() over(partition by t1.uid, t1.sign_mark order by t1.in_time) % 7 = 0 then 7
            when rank() over(partition by t1.uid, t1.sign_mark order by t1.in_time) % 7 = 3 then 3
            else 1 end as coin
    from (
        select ul.uid,
            date(ul.in_time) in_time,
            date_format(ul.in_time, '%Y%m') month,
            to_days(ul.in_time) - rank() over(partition by ul.uid order by date(ul.in_time)) as sign_mark
        from tb_user_log ul
        where ul.artical_id=0
            and ul.sign_in=1
            and ul.in_time between '2021-07-07' and '2021-11-01'
        order by ul.uid, date(ul.in_time)
    ) t1
) t2 group by t2.uid, t2.month
order by t2.month, t2.uid