SQL-根据规则计算连续签到积分

---

前言

本文主要记录计算连续签到积分的相关笔记，思路来源于牛客网真题，链接: 点击此处.

---

1. 问题介绍

用户行为日志表tb_user_log：

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：
artical_id-文章ID代表用户浏览的文章的ID，特殊情况artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。注意：只有artical_id为0时sign_in值才有效。
从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）
问题：计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序。

注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。
输出示例：
示例数据的输出结果如下：

uid	moth	coin
101	202107	15
102	202110	7

解释：
101在活动期内连续签到了7天，因此获得1*7+2+6=15金币；
102在10.01~10.03连续签到3天获得5金币
10.04断签了，10.05~10.06连续签到2天获得2金币，共得到7金币。

2. 解题思路

筛选出符合要求的数据集；
然后根据用户分组以in_time的先后顺序进行排序(排序列命名为为“cuo”)结果存入表t；
用date(in_time)-cuo，结果相等的属于是按顺序签到的一个序列，以用户和date(in_time)-cuo进行分组计算每个序列的数量，并进行去重操作，结果存入表t1（注：由于需要以月份进行结果输出，可能会出现同一用户再一个月内有多个连续序列的情况，如果序列的数量相同，月份也相同，去重时会将本应该保留下来的序列也去掉了，所以在此步骤还需要添加一个“date(in_time)-cuo”来避免这种情况）；
接下来只需要计算应得积分，首先计算有多少个连续的7，通过floor(cuo/7)进行计算，然后通过if(cuo%7>2,1,0)来计算序列小于7时是否大于3，命名为“ii”，最后使用sum(cuo+fc8+ii2)来计算最终的得分
。

3. 具体语句

with t as(select uid,date(in_time)dt,
		  row_number()over(partition by uid order by in_time)ro 
          from tb_user_log where sign_in!=0 and artical_id=0 
          and date(in_time) between '2021-07-07' and '2021-10-31'),
    t1 as(select distinct uid,dt-ro,substring(dt-ro,1,6) ddt,
    	  count(uid)over(partition by uid,dt-ro)cuo from t)
select uid,ddt,sum(cuo+fc*8+ii*2) coin from(
select *,if(cuo%7>2,1,0)ii from(
select *,floor(cuo/7) fc from t1)t3)t4 
group by uid,ddt order by uid,ddt