HIVE常见五种时间交叉及连续登录类问题

本文章旨在用于记录学习经验和便于交流 博主水平有限 如果文章中有任何错误 欢迎指出 博主不胜感激。（注：转载引用请注明出处）

连续问题：

如下数据为蚂蚁森林中用户领取的减少碳排放量，找出连续3天及以上减少碳排放量在100以上的用户。

id		dt				lowcarbon
1001	2021-12-12		123
1002	2021-12-12		45
1001	2021-12-13		43
1001	2021-12-13		45
1001	2021-12-13		23
1002	2021-12-14		45
1001	2021-12-14		230
1002	2021-12-15		45
1001	2021-12-15		23
… …

思路·：1 按照用户id和dt进行分组聚合 剔除排放量少于100的用户 2 对用户进行排序 3 使用dt减去排名的差值 4 按照差值进行分组 统计相同差值的个数 保留 个数大于3的用户

select
 	id,
 	flag,
 	count(*) ct
from  (
	select
	  id,
	  dt,
	  lowcarbon,
	  date_sub(dt,user_order)  flag
	from (
		select
		  id,
		  dt,
		  lowcarbon,
		  rank() over(partition by id orderby dt) user_order
		from (
			select
			  id,
			  dt,
			  sum(lowcarbon) lowcarbon
			from test1
			group by id,dt
			having lowcarvon>100
		)t1
	)t2
)t3
group by id,flag
having ct>-3

函数直达：date_sub()函数：函数直达
连续登录多种解法文章直达：Hive sql 七天内连续3天活跃sql查询

分组问题
如下为电商公司用户访问时间数据

id		ts(秒)
1001	17523641234
1001	17523641256
1002	17523641278
1001	17523641334
1002	17523641434
1001	17523641534
1001	17523641544
1002	17523641634
1001	17523641638
1001	17523641654

某个用户连续的访问记录如果时间间隔小于60秒，则分为同一个组，结果为：

id		ts(秒)			group
1001	17523641234		1
1001	17523641256		1
1001	17523641334		2
1001	17523641534		3
1001	17523641544		3
1001	17523641638		4
1001	17523641654		4
1002	17523641278		1
1002	17523641434		2
1002	17523641634		3

思路：1 使用lag函数 将前一行数据进行下移 2 用本次时间减去上次时间 求差值 2 统计每个用户差值大于60的个数 即为用户登录次数

select
	id，
	ts，
	lag(ts,1,0) over(partition by id order by ts) lagts
from test2

// 用本次时间减去上次时间求差值

select
	id,
	ts,
	ts-lagts tsdiff
from (
	select
		id，
		ts，
		lag(ts,1,0) over(partition by id order by ts) lagts
	from test2
)t1
//  统计每个用户时间差值大于60的个数 即为用户登录次数
select
	id,
	ts,
	sum(if(tsdiff>60,1,0)) over(partition by id order by ts) group_id
from(
	select
		id,
		ts,
		ts-lagts tsdiff
	from (
		select
			id，
			ts，
			lag(ts,1,0) over(partition by id order by ts) lagts
		from test2
	)t1
)t2

间隔连续问题
某游戏公司记录的用户每日登录数据，计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在1,3,5,6登录游戏，则视为连续6天登录。

id		dt
1001	2021-12-12
1002	2021-12-12
1001	2021-12-13
1001	2021-12-14
1001	2021-12-16
1002	2021-12-16
1001	2021-12-19
1002	2021-12-17
1001	2021-12-20

思路：1 将上调数据进行下移 2 计算本次登录时间与上次登录时间差值 datadiff函数 3 按照用户进行分组 按照时间进行排序 计算当前行大于2的数据的总条数 4 按照用户和时间进行分组 求最大时间减去最小时间并加上1 5 取连续登录天数的最大值

select 
	id,
	max(days)+1

from(
	select
		id,
		flag,
		datadiff(max(dt),main(dt)) days

	from(
		select
			id,
			dt,
			sum(if(flag>2,1,0)) over(partition bu id order by dt) flag

		from(
			select
				id,
				dt,
				datediff(dt,lagdt) flag

			from(
				select
					id,
					dt,
					lag(dt,1,'1970-01-01') over(partition by id orser by dt) lagdt

				from test3
			)t1
		)t2
	)t3
	group by id,flag

)t4
group by id

打折日期交叉问题
如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期，计算每个品牌总的打折销售天数，注意其中的交叉日期，比如vivo品牌，第一次活动时间为2021-06-05到2021-06-15，第二次活动时间为2021-06-09到2021-06-21其中9号到15号为重复天数，只统计一次，即vivo总打折天数为2021-06-05到2021-06-21共计17天。

brand	stt			edt
oppo	2021-06-05	2021-06-09
oppo	2021-06-11	2021-06-21
vivo	2021-06-05	2021-06-15
vivo	2021-06-09	2021-06-21
redmi	2021-06-05	2021-06-21
redmi	2021-06-09	2021-06-15
redmi	2021-06-17	2021-06-26
huawei	2021-06-05	2021-06-26
huawei	2021-06-09	2021-06-15
huawei	2021-06-17	2021-06-21

思路：1 获取当前行以前的数据最大的结束时间放在当前行 2 比较开始时间和下移的数据 如果开始时间大 则无需操作 反之需要移动下来的数据加后替换当前行的开始时间 第一行数据无需替换 3 计算开始时间与结束时间的差值 4 按照品牌进行分组 计算每条真是数据加1的总和

select
	id,
	sum(days>0,days+1,0) days
from
(
	select
		id,
		datediff(edt,stt) days
	from(
		select
			id,
			if(maxEdt is null,stt,if(stt>maxEdt,stt,data_add(maxEdt,1))) stt,
			edt
		from
		(
			select
				id,
				stt,
				edt,
				max(edt) over(partition by id order by stt rows betwwen UNBOUNDED PRECEDING and 1PRECEDING) maxEdt
			from test4
		)t1
	)t2
)t3
group by id

同时在线问题
如下为某直播平台主播开播及关播时间，根据该数据计算出平台最高峰同时在线的主播人数。

id		stt						edt
1001	2021-06-14 12:12:12		2021-06-14 18:12:12
1003	2021-06-14 13:12:12		2021-06-14 16:12:12
1004	2021-06-14 13:15:12		2021-06-14 20:12:12
1002	2021-06-14 15:12:12		2021-06-14 16:12:12
1005	2021-06-14 15:18:12		2021-06-14 20:12:12
1001	2021-06-14 20:12:12		2021-06-14 23:12:12
1006	2021-06-14 21:12:12		2021-06-14 23:15:12
1007	2021-06-14 22:12:12		2021-06-14 23:10:12
… …

思想 ：1 对数据进行分类 在开始数据后面添加正1 在结束时间后面添加-1 2 按照时间排序 计算累计在线人数 3 找出同时在线人数最大值

select
	max(sum_p)
from(
	select
		id,
		dt,
		sum(p) over(order by dt) sum_p

	from (
		select
			id，
			stt dt，
			1 p
		from test5
		union 
		select
			id，
			edt dt，
			-1 p
		from test5
	)t1
)t2

其中第一步后得到的结果是

select id,stt dt,1 p from test5
union
select id,edt dt,-1 p from test5;t1

1001	2021-06-14 12:12:12	1
1001	2021-06-14 18:12:12	-1
1001	2021-06-14 20:12:12	1
1001	2021-06-14 23:12:12	-1
1002	2021-06-14 15:12:12	1
1002	2021-06-14 16:12:12	-1
1003	2021-06-14 13:12:12	1
1003	2021-06-14 16:12:12	-1
1004	2021-06-14 13:15:12	1
1004	2021-06-14 20:12:12	-1
1005	2021-06-14 15:18:12	1
1005	2021-06-14 20:12:12	-1
1006	2021-06-14 21:12:12	1
1006	2021-06-14 23:15:12	-1
1007	2021-06-14 22:12:12	1
1007	2021-06-14 23:10:12	-1