有时候我们会碰到连续登录人数计算的问题&＃xff0c;比如想要求下面这两个问题的结果&＃xff0c;该怎么求&＃xff1f;
- 最近连续登陆天数
- 任意时间段内连续登陆天数
- 最近连续登陆超过x天的人数
原始数据给你&＃xff1a;
name表示用户&＃xff0c;date表示登陆的日期
最近连续登陆天数计算实现方式&＃xff1a;
select name,count(1) as &＃96;用户最近连续登陆天数&＃96;
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)&＃43;1 day) date_line -- 以用户最近登陆日期做降序排序&＃xff0c;算出连续间隔1、2、3...n天的日期from aaa
) t
where date &＃61; date_line -- 限制 date &＃61; date_line 得到用户最近连续活跃日期
group by name
计算过程&＃xff1a;
1.用户最近登陆日期&＃xff1b;
2.以用户最近登陆日期做降序排序&＃xff0c;算出连续间隔1、2、3...n天的日期&＃xff0c;得到一列date_line字段&＃xff1b;
3.此时&＃xff0c;如果我们限制date&＃61;date_line&＃xff0c;就已经得到用户最近连续活跃日期了&＃xff1b;
4.使用上面的结果汇总一下&＃xff0c;"用户最近连续登陆天数"结果就出来了&＃xff08;通过此结果还能计算“最近连续登陆超过x天的人数”&＃xff09;&＃xff1a;
下面这个实现方式稍微复杂一些&＃xff0c;但不仅是能计算“最近的连续登陆天数”&＃xff0c;还能算“任意时间段的连续登陆天数”&＃xff0c;扩展性更强&＃xff1a;
实现脚本&＃xff1a;
with t as(select *,min(last_date) over (partition by name order by date desc) as date_setfrom (select name,date,if(DATEDIFF(lag(date) over (partition by name order by date desc),date)&＃61;1,null,date) as last_datefrom aaa) t
)
select name,date_set,count(*) as &＃96;连续登陆天数&＃96;
from t
-- where date_group&＃61;&＃39;2020-10-19&＃39; 最近一天登陆用户的连续登陆天数
group by name,date_set
计算过程&＃xff1a;
1.把同一用户的连续登陆日期放到一块&＃xff0c;派生出连续登陆日期组的最后日期&＃xff0c;作为下游统计时用的分组&＃xff1a;
2.以用户和“连续日期组”作分组汇总&＃xff0c;得到用户在该组下面的连续登陆天数&＃xff08;通过此结果还能计算“某段时间内连续登陆超过x天的人数”&＃xff09;。
京公网安备 11010802041100号