在 5 分钟不活动后创建用户会话

Question

使用SQL，如果用户已处于非活动状态5分钟，如何创建新会话?

userid           timestamp          session
1                10:15            1_10:15
1                10:16            1_10:15
1                10:17            1_10:15
1                10:23            1_10:23
2                10:18            2_10:18
2                10:18            2_10:18
2                10:19            2_10:18
2                10:30            2_10:30

我试过使用铅，并采取电流和铅之间的差异，但它没有给出正确的结果。

e.g. lead(timestamp,1) over(partition by userid order by timestamp asc) as leadTime,

有谁能帮帮我吗

Answer 1

您可以使用LAG函数定义一个flag=1，其中当前行时间大于前一行时间超过5分钟，然后使用累积和窗口函数(在该标志上)为时间差小于5分钟的连续行创建唯一组

with get_groups as
(
select userid, timestamp, SUM(flag) over (partition by userid order by timestamp) grp
from
(
select *,
case 
when unix_timestamp(timestamp) - unix_timestamp(lag(timestamp) over (partition by userid order by timestamp)) >300
then 1 else 0
end as flag
from table_name
) T
)
select userid, timestamp, 
CONCAT(userid, '_', MIN(timestamp) over (partition by userid, grp)) as session
from get_groups

查看MySQL的演示。我不确定这是否是一个有效的Hive语法，但你可以考虑这个解决方案的想法。