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

#对签到进行一个排序，连续签到的排序和时间的差值是一样的
#窗口函数不能用别名？？
SELECT uid,date_format(dt,'%Y%m') as mon,sum(
case when lxqd in (0,1,3,4,5) then 1
    when lxqd = 2 then 3
    when lxqd = 6 then 7
    end
)
from
(
SELECT * ,(rank() over (partition by uid,date_cha order by dt)-1)%7 as lxqd
FROM
(
SELECT *,date_sub(dt,interval rk day) as date_cha 
FROM(
SELECT uid ,date(in_time) as dt,rank() over(partition by uid order by date(in_time)) as rk
from tb_user_log
where artical_id = 0 and sign_in = 1
and date(in_time)>='2021-07-07' and date(in_time)<= '2021-10-31'
)l
)ll

)lll
group by uid,mon