题解 | #统计活跃间隔对用户分级结果#

1）选出2021-07-07到2021-10-31期间签到的用户，按用户排序编号，用排序问题得到用户日期与编号之差，如果该值相等，则证明连续 select uid, date(in_time) as dt, date(in_time) - row_number() over ( partition by uid order by date(in_time) ) as oo from tb_user_log where artical_id = 0 and sign_in = 1 and date(in_time) between '2021-07-07' and '2021-10-31' 2）给选出的是否连续这一列排序，dense_rank()排序，之后按照uid和dens_rank()分组，根据规则计算积分，%7余数为3，0的分别赋值3，7，其他为0，但注意第一个周期的3，7得分也是3，7，用case when 进行汇总，之后再按要求进行细枝末节的调整即可，具体代码如下

select y.uid, y.dtm, sum( case when y.oor = 3 then 3 when y.oor = 7 then 7 when y.oor % 7 = 3 then 3 when y.oor % 7 = 0 then 7 else 1 end ) from ( select x.uid, x.oo, date_format(dt, '%Y%m') as dtm, dense_rank() over ( partition by x.uid, x.oo order by x.dt ) as oor from ( select uid, date(in_time) as dt, date(in_time) - row_number() over ( partition by uid order by date(in_time) ) as oo from tb_user_log where artical_id = 0 and sign_in = 1 and date(in_time) between '2021-07-07' and '2021-10-31' ) as x ) as y group by uid, y.dtm