hiveSql 各时段观看直播人数

题目

有直播间观看记录数据如下：

live_id	user_id	start_time	end_time
A	u1	2022-11-11 12:12:12	2022-11-11 13:13:13
A	u2	2022-11-11 12:20:20	2022-11-11 13:40:40
…	…	…	…

表名：ods_live_watch_record_log
live_id:直播间ID；user_id:用户ID；start_time:该用户进入该直播间时间；end_time:该用户退出该直播间时间。

需求

想要计算出每个时间段，每个直播间有多少观看用户
例如上述数据，想要结果如下

live_id	start_time	end_time	user_cnt
A	2022-11-11 12:12:12	2022-11-11 12:20:20	1
A	2022-11-11 12:20:20	2022-11-11 13:13:13	2
A	2022-11-11 13:13:13	2022-11-11 13:40:40	1

2022-11-11 12:12:12 到 2022-11-11 12:20:20期间只有u1用户一人
2022-11-11 12:20:20 到 2022-11-11 13:13:13期间有 u1、u2用户两人
2022-11-11 13:13:13 到 2022-11-11 13:40:40期间只有u2用户一人

---

到这里可以思考下用HiveSQL如何实现上述需求

分析

part1

可以看到最终的数据需求是期望要所有直播间所有用户的进入、退出时间作为时间段，计算这些时间段的观看用户数。
先想象简单的数据，只有一个直播间，多个用户观看。对于这个直播间的统计时间段来说，就是这个直播间所有观看用户的进入时间和退出时间升序排序后的每两个时间点。

很容易想到，就是每个直播间所有用户的进入时间，退出时间去重。

select 
	live_id,start_time as t_time
from ods_live_watch_record_log 
union 
select 
	live_id,end_time as t_time
from ods_live_watch_record_log 

例如上述示例数据：

live_id	user_id	t_time
A	u1	2022-11-11 12:12:12
A	u2	2022-11-11 12:20:20
A	u1	2022-11-11 13:13:13
A	u2	2022-11-11 13:40:40

去重后，需要相邻两个时间作为开始结束时间，可以想到是自己关联自己，但是是错一位关联。
hive有开窗函数 lead() over() 或 lag() over() 可以实现上述要求。更多开窗函数可参看 常用开窗函数简介

with tmp as (
	select 
		live_id,start_time as t_time
	from ods_live_watch_record_log 
	union 
	select 
		live_id,end_time as t_time
	from ods_live_watch_record_log 
)
select
	 live_id,t_time as start_time,
	 lead(t_time, 1, t_time) over(partition by live_id order by t_time) as end_time
from tmp

live_id	开始时间	结束时间
A	2022-11-11 12:12:12	2022-11-11 12:20:20
A	2022-11-11 12:20:20	2022-11-11 13:13:13
A	2022-11-11 13:13:13	2022-11-11 13:40:40
A	2022-11-11 13:40:40	2022-11-11 13:40:40

part2

获得了每个直播间需要统计的时间段之后，只需要将其与每个直播间观看明细做关联，然后比较每个用户的进入退出时间段A 与 直播间需统计的时间段B 是否有交集即可，如果有交集，则当前用户在该时间段内是观看用户。

with tmp as (
	select 
		live_id,start_time as t_time
	from ods_live_watch_record_log 
	union 
	select 
		live_id,end_time as t_time
	from ods_live_watch_record_log 
)
select 
	a.live_id,a.start_time,a.end_time,
	count(distinct case when a.start_time <= b.end_time and a.end_time > b.start_time then b.user_id else null end) as user_cnt
from
	(select
		 live_id,t_time as start_time,
		 lead(t_time, 1, t_time) over(partition by live_id order by t_time) as end_time
	from tmp 
	) a 
left join 
	ods_live_watch_record_log b
	on a.live_id = b.live_id
group by a.live_id,a.start_time,a.end_time

即完成需求：

live_id	start_time	end_time	user_cnt
A	2022-11-11 12:12:12	2022-11-11 12:20:20	1
A	2022-11-11 12:20:20	2022-11-11 13:13:13	2
A	2022-11-11 13:13:13	2022-11-11 13:40:40	1