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

此题共包含一张表：

表1：tb_user_log

要解决的问题：

问题：计算每个用户2021年7月以来每月获得的金币数,该活动到10月底结束，11月1日开始的签到不再获得金币结果按月份、ID升序排序。

注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。

解题思路：

1. 获得底表：

• 需要获取用户id，日期，按用户ID分组时间排序
• 增加条件where artical_id=0 and sign_in=1
• 统计时间为2021年7月7日至2021年10月31日

2. 统计用户获得的金币数：

• 考虑到有不连续打卡的情况，找到每次连续打卡的首日，date_sub(d, interval rank1-1 day)
• 根据UID和首日打卡分组，以in_time排序，获得排序编号
• 由于连续签到7日后，金币发放数从新从1开始，所以要将上面的条件取余 %7
• 当余数为3时，金币数3，当余数为0时，金币数7，其他情况金币数为1

3. 根据上表获得UID，month和coin
4. 按月份，ID升序排序

知识点：

1. 在解题思路第2步中，也可以使用timestampadd、date_add获得同样的结果，语法如下：

• TIMESTAMPADD(unit,interval,datetime_expr)
• DATE_ADD(date,INTERVAL expr unit)
• DATE_SUB(date,INTERVAL expr unit)

2. case (...可以是一个窗口函数) when...else...end，这一步将窗口函数嵌套在case条件语句中，省去了很多麻烦
3. 选择窗口函数进行排序时，一定要考虑到序列和条件函数的关系，所以此题一定是选择dense_rank()，遇连续相同条件排列数相同且后面排序的数字为连续数字，如1,2,2,2,5,6

select uid,date_format(dt,"%Y%m") as mon,sum(coin)
from
  (select uid,
          DATE_sub(dt, INTERVAL rank1-1 day) start_dt,
          DENSE_RANK() over(partition by uid ,DATE_sub(dt, INTERVAL rank1-1 day) order by dt),
          case (DENSE_RANK() over(partition by uid ,DATE_sub(dt, INTERVAL rank1-1 day) order by dt)) %7 
          when 3 then 3
          when 0 then 7
          else 1 end as coin,
          dt
  from
     (select uid,DATE(in_time) as dt,
      DENSE_RANK() over(partition by uid order by DATE(in_time)) as rank1
      from tb_user_log
      where DATE(in_time) between "2021-07-07" and "2021-10-31"
      and artical_id=0 and sign_in=1
     ) tmp
  ) base
group by uid,date_format(dt,"%Y%m")
order by mon,uid