类似连续登录人数计算的问题，比如想要求下面这几个问题的结果，怎么算？
最近连续登陆天数
任意时间段内连续登陆天数
最近连续登陆超过 x 天的人数
原始数据：
name 表示用户，date 表示登陆的日期
最近连续登陆天数计算实现方式：
select name,count(1) as `用户最近连续登陆天数`from ( select name ,date ,first_value(date) over (partition by name order by date desc) as last_login_date -- 用户最近登陆日期 ,DATE_ADD( first_value(date) over (partition by name order by date desc) -- 用户最近登陆日期 ,INTERVAL -row_number() over (partition by name order by date desc)+1 day ) date_line -- 以用户最近登陆日期做降序排序，算出连续间隔1、2、3...n天的日期 from aaa) t where date = date_line -- 限制 date = date_line 得到用户最近连续活跃日期group by name
复制代码
计算过程：
1.用户最近登陆日期；
2.以用户最近登陆日期做降序排序，算出连续间隔 1、2、3...n 天的日期，得到一列 date_line 字段；
3.此时，如果我们限制 date=date_line，就已经得到用户最近连续活跃日期了；
4.使用上面的结果汇总一下，"用户最近连续登陆天数"结果就出来了（通过此结果还能计算“最近连续登陆超过 x 天的人数”）：
下面这个实现方式稍微复杂一些，但不仅是能计算“最近的连续登陆天数”，还能算“任意时间段的连续登陆天数”，扩展性更强：
实现脚本：
with t as( select * ,min(last_date) over (partition by name order by date desc) as date_set from ( select name ,date ,if(DATEDIFF(lag(date) over (partition by name order by date desc),date)=1,null,date) as last_date from aaa ) t )select name,date_set,count(*) as `连续登陆天数`from t -- where date_group='2020-10-19' 最近一天登陆用户的连续登陆天数group by name,date_set
复制代码
计算过程：
1.把同一用户的连续登陆日期放到一块，派生出连续登陆日期组的最后日期，作为下游统计时用的分组：
2.以用户和“连续日期组”作分组汇总，得到用户在该组下面的连续登陆天数（通过此结果还能计算“某段时间内连续登陆超过 x 天的人数”）。
评论