hiveSql 跨N天最大连续统计

说明

看到标题可能没太能理解，什么叫跨N天连续？这里解释下：
跨N天连续定义为：登录相隔天数小于N
用跨2天举例（即隔一天也算连续登录）
例如 ：
2022-01-01 与 2022-01-02算连续
2022-01-01 与 2022-01-03也算连续（隔了一个2022-01-02 但是也算连续登录）
2022-01-01 与 2022-01-04则不算连续了（隔了2天不算连续了）

需求

有数据如下：
表：tmp_login

id	dt
1001	2021-12-12
1001	2021-12-13
1001	2021-12-14
1001	2021-12-16
1001	2021-12-19
1001	2021-12-20
1001	2021-12-26
1001	2021-12-27
1001	2021-12-29
1002	2021-12-12
1002	2021-12-16
1002	2021-12-17
1
2
3
4
5
6
7
8
9
10
11
12
13

字段：
id：用户ID
dt：用户登录日期

希望计算每个用户的最大连续登录天数，这里的连续定义为小于2天。即隔一天也算连续登录
基于上述明细数据，期望获得以下数据：

1001：是2021-12-12到2021-12-16号 5天
1002：是2021-12-16到2021-12-17号 2天

---

到这里可以先思考下用hiveSql怎么实现

分析

对于严格每天登录，这个逻辑来计算最大连续登录天数，很容易想到，将用户登录日期排序，用登录日期减去排序序号，则按照减完之后的日期分组统计天数即可。但是现在隔一天也算连续，这种方式貌似实现不了，减完之后的日期会被分到多组中。所以最关键的点在于怎么把满足上述定义的连续的日期分到同一个组里，然后聚合组内天数即可。容易想到，在给每个用户的登录日期排序后，将相邻的两个日期做差，差值大于2的有一个断点，后续利用这个断点做日期分组。这种思路也叫做重分组思想。

有了上述思路，下面逐步完成sql编写

实现

• 1.将用户登录日期排序，排序后相邻日期做差值

select
	id,dt,
	lag(dt,1,dt) over(partition by id order by dt) as previous_dt,
	datediff(dt,lag(dt,1,dt) over(partition by id order by dt)) as adjacent_dt_diff
from tmp_login
1
2
3
4
5

previous_dt：当前日期的升序排序后前一个日期
adjacent_dt_diff：当前日期 与 升序排序后前一个日期相差的天数
有结果如下：

• 2.有相差天数后，可以按照需求定义小于什么差值是连续，这里是小于两天为连续。可以根据adjacent_dt_diff值来定义上述分析中的断点

select 
	id,dt,previous_dt
	,if(adjacent_dt_diff>2,1,0) as flag
from
	(select
		id,dt,
		lag(dt,1,dt) over(partition by id order by dt) as previous_dt,
		datediff(dt,lag(dt,1,dt) over(partition by id order by dt)) as adjacent_dt_diff
	from tmp_login
	) t;
1
2
3
4
5
6
7
8
9
10

flag：这里即是断点，因为用户可能会有多段满足条件的连续，每段的第一天flag即为1。断点与断点之间的日期即是满足条件的连续日期了。
结果如下：

上述截图可以看到，用户1001，在2021-12-16 与 2021-12-19之间是超过2天的，即不连续。2021-12-19与2021-12-20有是下一段的连续，flag在2021-12-19这条上是1。

• 3.上述断点有了之后，怎么讲每个断点间的日期分在同一组呢？这里可以利用开窗sum() over() 累加的方式分组：

select 
	id,dt,previous_dt,adjacent_dt_diff
	,if(adjacent_dt_diff>2,1,0) as flag
    ,sum(if(adjacent_dt_diff>2,1,0)) over (partition by id order by dt) as group_flag
from
	(select
		id,dt,
		lag(dt,1,dt) over(partition by id order by dt) as previous_dt,
		datediff(dt,lag(dt,1,dt) over(partition by id order by dt)) as adjacent_dt_diff
	from tmp_login
	) t;
1
2
3
4
5
6
7
8
9
10
11

group_flag：满足条件的分组字段

上述截图可以看到，每个用户id下满足条件的连续登录日期都按照group_flag分组了，接下来只需要做分组，取组内最大日期减去组内最小日期即可

• 4.分组聚合，取组内最大最小日期天数差

select 
    id,max(days) as max_days
from
    (select 
        id,group_flag,datediff(max(dt),min(dt)) as days
    from
        (select 
            id,dt,previous_dt,adjacent_dt_diff
            ,if(adjacent_dt_diff>2,1,0) as flag
            ,sum(if(adjacent_dt_diff>2,1,0)) over (partition by id order by dt) as group_flag
        from
            (select
                id,dt,
                lag(dt,1,dt) over(partition by id order by dt) as previous_dt,
                datediff(dt,lag(dt,1,dt) over(partition by id order by dt)) as adjacent_dt_diff
            from tmp_login
            ) t
        ) t1
        group by id,group_flag
    ) t2
group by id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

结果如下：

此处有个小的注意事项，最大日期 - 最小日期后加1。

• 5.精简sql，完整sql如下：

select 
    id,max(days) as max_days
from
    (select 
        id,flag_sum,datediff(max(dt),min(dt))+1 as days
    from
        (select 
            id,dt,flag
            ,sum(flag) over(partition by id order by dt) as flag_sum
        from
            (select 
                id,dt,
                if(datediff(dt,lag(dt,1,dt) over(partition by id order by dt) ) > 2,1,0) as flag
            from tmp_login
            ) t
        ) t1
    group by id,flag_sum
    ) t2
group by id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

最后

喜欢的点赞、关注、收藏吧~ 感谢支持~~