hive sql练习2：统计用户在每个时间段的在线时长

习题描述

表里有用户进入和离开的时间，统计用户在每个时间段的在线时长
题目场景不固定，根据大致题意，做出下面场景假设(下面假设场景中的表结构均为自己假设，如有问题请大佬指出)。

假设1

场景: 同一会话下记录用户的进入和离开时间，那就是每一次进入就是新的会话，然后统计按小时，或者按每10分钟段的在线时长。
表结构：

表结构
user_id      --用户id
session_id   --会话id
in_time      --进入时间
out_time     --离开时间

假设2

场景:用户在同一会话下记录进入和离开 两个事件，也就是记录两条数据，多的就是事件(进入，离开标志)，然后计算时间段时长。

表结构
user_id        --用户id
session_id     --会话id
event_time     --事件时间
event_flag     --事件标志

实现

假设1

--取某一天的所有小时间隔时间
with tmp as(
select 
from_unixtime(unix_timestamp('2023-02-17 00:00:00') + pos * 60 *60 , 'yyyy-MM-dd HH:mm:ss') as time
from (
select posexplode(split(space(23), ' ')) as (pos, val)
) t
)
--临时造的场景 1 所需测试数据
,tmp1 as(
select '1' as user_id, '1' as session_id, '2023-02-17 01:10:00' as in_time, '2023-02-17 04:10:00' as out_time union all 
select '1' as user_id, '2' as session_id, '2023-02-17 04:11:00' as in_time, '2023-02-17 04:16:10' as out_time union all 
select '2' as user_id, '1' as session_id, '2023-02-17 04:00:00' as in_time, '2023-02-17 04:16:00' as out_time union all 
select '2' as user_id, '2' as session_id, '2023-02-17 05:00:00' as in_time, '2023-02-17 07:00:00' as out_time 
)
--将每条数据(每个用户下的每个会话id)与当天所有时间段时间取笛卡尔积，
--行转列，将每次会话的进入和离开时间的两列，合并到一列里面，
--并对所在会话时间段的时间打标记
,tmp2 as(
select user_id, session_id, time as times, if(in_time < time and time < out_time, 1, null) as flag from tmp, tmp1 union all 
select user_id, session_id, in_time as times, 1 as flag from tmp1 union all 
select user_id, session_id, out_time as times, null as flag from tmp1
)
--对所有数据按用户的每次会话分组，按时间升序排序，窗口内取每条数据下条数据的时间，即取下条相邻时间
,tmp3 as(
select 
user_id, session_id, times, flag,
lead(times, 1) over(partition by user_id, session_id order by times) as times2
from tmp2
group by user_id, session_id, times, flag
)
--求每个用户每次会话所在每个时间段的时间
--取所有标记内的数据，并取相邻两个时间段的差值(单位分钟)
,tmp4 as(
select 
user_id, session_id, times, times2, flag,
(unix_timestamp(times2) - unix_timestamp(times)) / 60 as m_time
from tmp3
where flag = 1
)
--求每个用户所在每个时间段的时间总和
select 
user_id, date_format(times,'yyyy-MM-dd HH:00:00'),
sum(m_time) as m_time
from tmp4
group by user_id, date_format(times,'yyyy-MM-dd HH:00:00')
;

假设2

感觉跟场景 1 类似，只不过场景 1 是一条数据，场景 2 是两条数据，但是我们场景 1 的做法是将一条数据拆为了两条数据，这么看就与场景 2 一致，所以场景 2 应该比场景 1 少一步拆解数据的动作，其余感觉均与场景 1 写法一致。

以上均为自己根据题意假设所做，如有错误或者更好的方法，请大佬指正。