问题
登陆日志,计算每个人连续登陆的最大天数!(注意:断一天也算连续)
数据如下所示:
id dt
01 2021-02-28
01 2021-03-01
01 2021-03-02
01 2021-03-04
01 2021-03-05
01 2021-03-06
01 2021-03-08
02 2021-03-01
02 2021-03-02
02 2021-03-03
02 2021-03-06
03 2021-03-06
方法1
思路:等差数列
1、按照id分组同时按照dt排序,求rk
2、将每行日期减去rk值得到flag标志,如果之前是连续的日期,则相减后为相同的日期,flag相同
3、按照id和flag分组,计算count(*)得数连续的天数
4、按照id分组同时按照flag排序,求rk
5、将每行flag减去rk值得到new_flag标志
6、按照id和new_flag分组,计算连续的天数
7、对id进行分组
sql 实现:
select
id
,max(days) as days
from
(select
id
,new_flag
,sum(days)+count(*)-max(1) as days
from
(
select
id
,flag
,days
,falg -rank as new_falg
from
(select
id
,flag
,days
,rank() over(partition by id order by flag) as rank
from
(select
id
,flag
,count(dt) as days
from (
select
id
,dt
,dt -rk as flag
from (
select
id
,dt
rank()over(partition by id order by dt) rk
from tablename;
)t1
)t2
group by
id
,flag
)t3
)t4
)t5
group by
id
,new_flag
)t6
group by
id;
备注:这种方法有个弊端,像俄罗斯套娃,当条件发生改变,比如断5天也算连续,你要重复5次;
方法2
思路:采用lag开窗函数
sql 实现:
select
id
,flag
,datediff(max(dt),min(dt))+1
from
(
select
id
,dt
sum(if(dtDiff >2,1,0)) over (partition by id order by dt) as flag
from (
select
id
,dt
,datediff(dt,lagDt) as dtDiff
from (
select
id
,dt
,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
from tablename
)t1
)t2
)t3
group by
id
,flag
备注:断一天也算连续,则 sum(if(dtDiff >2,1,0))处为2
断n天也算连续, 则 sum(if(dtDiff >n+1,1,0))处为n+1