SQL 获取两个事件之间的会话持续时间

问题描述

我有一个数据集（具体是在PostgreSQL中），我需要计算“用户会话时间”。我非常确定需要使用分区来回答这个问题，但我不太确定如何去做。 events表包含id、user_id、事件类别和日期。当用户登录时，我们会在表中插入一个事件。每次他们访问一个页面，都会有一个类似的事件。表的结构类似于这样：

要从用户100计算会话时长，假设事件7是他们在该会话中系统中最后一页的浏览记录。我们需要获取登录事件（id为1），找到登录事件之前的最后一个事件（id为7），然后相减这两个时间。结果为2023-12-20 01:11:28 - 2023-12-20 01:07:03，即4分钟25秒。我希望对数据集中的每个用户/登录组合都进行这样的计算。完整性起见，用户可能没有任何页面浏览记录，这种情况下他们的会话时长为零。结果应该类似于这样：

我猜我想要以用户为基础进行分区，然后再按事件类别分区，但是我需要选择的行的前一行，并不是下一个窗口。在代码中，我通常会取出所有的事件，按用户和持续时间排序，然后通过迭代创建一个新的结果集，每次都在登录事件发生时创建一个“会话”。我只是好奇是否可以在数据库中通过SQL更有效地完成这个任务。

解决方案

如果你觉得这个窗口函数很别扭，你可以用笨拙的标量子查询来替代。毕竟，从每一行“登录”开始，你只需要寻找同一用户的下一个“登录”之前的最大日期： demo1

select user_id,
       date as session_start,
       (select max(date) from test t2 
        where t2.user_id=t1.user_id
        and t2.date>=t1.date
        and category<>'Sign-In'
        and date<=(
            select coalesce(min(t3.date),'infinity'::timestamp) 
            from test t3 where category='Sign-In'
            and t3.date>t1.date and t3.user_id=t1.user_id)
       )-date as duration
from test t1
where category='Sign-In'
order by 1,2;

或者你可以继续使用窗口函数： demo2

select user_id, date as session_start, duration
from ( select *,(lead(date)over w2)-date as duration
       from ( select *,('Sign-In'=category) as is_session_start
                      ,('Sign-In'<>category and
                        'Sign-In'=lead(category,1,'Sign-In')over w1
                       ) as is_session_end
              from test t1
              window w1 as (partition by user_id order by date) 
       ) AS subquery1 where is_session_start or is_session_end
       window w2 as (partition by user_id order by date) 
) AS subquery2 where is_session_start
order by 1,2;